Microsoft Office Tutorials and References

In Depth Information

**Using a Data Table to Analyze Worksheet Data**

You already have seen that if you change a value in a cell, Excel immediately recalculates

and displays the new results of any formulas that reference the cell directly or indirectly.

But what if you want to compare the results of the formula for several different values?

Writing down or trying to remember all the answers to the what–if questions would be

unwieldy. If you use a data table, however, Excel will organize the answers in the worksheet

for you automatically.

A
data table
is a range of cells that shows the answers generated by formulas in

which different values have been substituted. Data tables are built in an unused area of

the worksheet (in this case, the range B7:E23). Figure 4–19 illustrates the makeup of a

one–input data table. With a
one–input data table
, you vary the value in one cell (in

this worksheet, cell E2, the interest rate). Excel then calculates the results of one or more

formulas and ﬁ lls the data table with the results.

An alternative to a one–input table is a two–input data table. A
two–input data

table
allows you to vary the values in two cells, but you can apply it to only one formula.

A two–input data table example is illustrated in the Extend Your Knowledge exercise on

page EX 325.

The interest rates that will be used to analyze the loan formulas in this project

range from 4.50% to 7.75%, increasing in increments of 0.25%. The one–input data table

shown in Figure 4–20 illustrates the impact of varying the interest rate on three formulas:

the monthly payment (cell E4), total interest paid (cell E5), and the total cost of the item

to be purchased (cell E6). The series of interest rates in column B are called
input values
.

Data Tables

Data tables have one

purpose: to organize

the answers to what-if

questions. You can create

two kinds of data tables.

The ﬁ rst type involves

changing one input

value to see the resulting

effect on one or more

formulas. The second

type involves changing

two input values to see

the resulting effect on

one formula.

Data Table command

uses formulas in these

cells to display resulting

Monthly Payment,

Total Interest, and Total

Cost values based on

different interest rates

data table

automatically ﬁ lled

with results based

on varying interest

rates in column B

interest rate values

used to calculate

results in table

same interest

rate generates

same results

Excel displays results

of calculations based

on varying interest

rates in column B

Figure 4–19

Figure 4–20