Excel 2010 Bible

Part V: Analyzing Data with Excel

748

fairly easily, but data tables have some limitations. In particular, a data table can deal with only one

or two input cells at a time. This limitation becomes clear as you view the examples.

Note
Scenario Manager, discussed later in this chapter (see “Using Scenario Manager”), can produce a report that
summarizes any number of input cells and result cells. n

Don’t confuse a data table with a standard table (created by choosing Insert ➪ Tables ➪ Table).

These two features are completely independent.

Creating a one-input data table

A one-input data table displays the results of one or more formulas for various values of a single

input cell. Figure 36.2 shows the general layout for a one-input data table. You need to set up the

table yourself, manually. This is not something that Excel will do for you.

FIGURE 36.2

How a one-input data table is set up.

You can place the data table anywhere in a worksheet. The left column contains various values for

the single input cell. The top row contains references to formulas located elsewhere in the work-

sheet. You can use a single formula reference or any number of formula references. The upper-left

cell of the table remains empty. Excel calculates the values that result from each value of the input

cell and places them under each formula reference.

This example uses the mortgage loan worksheet from earlier in the chapter (see “A What-If

Example”). The goal of this exercise is to create a data table that shows the values of the four for-

mula cells (loan amount, monthly payment, total payments, and total interest) for various interest

rates ranging from 6 to 8 percent, in 0.25-percent increments.