Chapter 31: Performing Spreadsheet What-If Analysis
3131
This example uses the mortgage loan worksheet from earlier in the chapter (see “Looking at
a What-If Example”). The goal of this exercise is to create a data table that shows the values
of the four formula cells (loan amount, monthly payment, total payments, and total inter-
est) for various interest rates ranging from 4.5% to 6.5%, in 0.25% increments.
This workbook is available on this book’s website at http://www.wiley.com/go/excel2019bible. The
file is named mortgage loan data table.xlsx.Figure 31.3 shows the setup for the data table area. Row 3 consists of references to the
formulas in the worksheet. For example, cell F3 contains the formula =C10, and cell G3
contains the formula =C11. Row 2 and column D contain optional descriptive labels that
are not actually part of the data table. Column E contains the values of the single input cell
(interest rate) that Excel will use in the table.
FIGURE 31.3
Preparing to create a one-input data table
To create the table, select the entire data table range (in this case, E3:I12) and then choose
Data ➪ Forecast ➪ What-If Analysis ➪ Data Table. The Data Table dialog box, shown in
Figure 31.4, appears.
You must specify the worksheet cell that contains the input value. Because variables
for the input cell appear in the left column in the data table, you place this cell reference
in the Column Input Cell field. Enter C7 or point to the cell in the worksheet. Leave the
Row Input Cell field blank. Click OK, and Excel fills in the table with the calculated results
(see Figure 31.5).