Microsoft Office Tutorials and References
In Depth Information
More About Data Tables
In Figure 4–27, the data table shows the monthly payment, total interest, and total
cost for the interest rates in the range B10:B23. For example, if the interest rate is 5.75%
(cell E2), the monthly payment is $1,748.80 (cell E4). If the interest rate is 7.75% (cell
B23), however, the monthly payment is $2,020.77 rounded to the nearest cent (cell C23).
If the interest rate is 7.00% (cell B20), then the total cost of the house is $443,948.90
rounded to the nearest cent (cell E20), rather than $407,740.46 (cell E6). Thus, a 1.25%
increase from the interest rate of 5.75% to 7.00% results in a $36,208.44 increase in the
total cost of the house.
The following list details important points you should know about data tables:
1. The formula(s) you are analyzing must include a cell reference to the input cell.
2. You can have as many active data tables in a worksheet as you want.
3. While only one value can vary in a one–input data table, the data table can
analyze as many formulas as you want.
4. To include additional formulas in a one–input data table, enter them in adjacent
cells in the same row as the current formulas (row 9 in Figure 4–27 on the previ-
ous page) and then deﬁ ne the entire new range as a data table by using the Table
command on the Data menu.
5. You delete a data table as you would delete any other item on a worksheet. That
is, select the data table and then press the DELETE key.
To Format the Data Table
If you start to assign
formats to a range and
then realize you made a
mistake and want to start
over, select the range,
click the Cell Style button
on the Home tab on the
Ribbon, and then click
Normal in the Cell Style
The following steps format the data table to improve its readability.
1 Select the range B8:E23. Right–click the selected range and then click Format Cells on the
shortcut menu. When Excel displays the Format Cells dialog box, click the Border tab, and
then click the medium line style in the Style area (column 2, row 5). Click the Outline but-
ton in the Presets area. Click the light border in the Style area (column 1, row 7) and then
click the Vertical Line button in the Border area to preview the black vertical border in the
2 Click the Fill tab and then click the light red color box (column 6, row 2). Click the OK button.
3 Select the range B8:E8. Click the Home tab on the Ribbon and then click the Borders button
to assign a light bottom border.
4 Select the range C10:E23 and right–click. Click Format Cells on the shortcut menu. When
Excel displays the Format Cells dialog box, click the Number tab. Click Currency in the
Category list, click the Symbol box arrow, click None, and then click the second format,
1,234.10, in the Negative numbers list. Click the OK button to display the worksheet as
shown in Figure 4–28.
5 Click the Save button on the Quick Access Toolbar to save the workbook using the ﬁ le
name Braden Mortgage Loan Payment Calculator.