Microsoft Office Tutorials and References
In Depth Information
Using a Data Table to Analyze Worksheet Data
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 fi 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 fi 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 fi 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
Search JabSto ::

Custom Search