180
22
33
44
55
11
2
Select the range that includes
the input values and the
formula.
3
Click the Data tab.
4
Click What-If Analysis ( ).
5
Click Data Table.
1
Type the input values:

To enter the values in a
column, start the column one
cell down and one cell to the
left of the cell containing the
formula, as shown here.

To enter the values in a row,
start the row one cell up and
one cell to the right of the cell
containing the formula.
Create a Data Table
If you are interested in studying the effect a

address in the Column input
cell text box.

If you entered the input values
in a row, enter the input cell’s
address in the Row input cell
text box.
7
Click OK.
•
Excel displays the results.
What is what-if analysis?
The technique called what-if analysis is perhaps
the most basic method for analyzing worksheet
data. With what-if analysis, you first calculate a
formula D, based on the input from variables A,
B, and C. You then say, “What happens to the
result if I change the value of variable A?”, “What
happens if I change B or C?”, and so on.
When I try to delete part of the data
table, I get an error. Why?
The data table results are created as an
array formula, which is a special formula
that Excel treats as a unit. This means that
you cannot move or delete part of the
results. If you need to work with the data
table results, you must first select the
entire results range.
12_577639-ch10.indd 18112_577639-ch10.indd 181 3/15/10 2:46 PM3/15/10 2:46 PM
182

44
66
55

The Subtotal dialog box
appears.
4
Click the At each change in
and then click the column
you want to use to group the
subtotals.
5
In the Add subtotal to list,
click the check box for
the column you want to
summarize (
changes to ).
6
Click OK.
•
Excel calculates the subtotals
and adds them into the range.
•
Excel adds outline symbols to
the range.
Do I need to prepare my worksheet to use
subtotals?
Excel sets up automatic subtotals based on data
groupings in a selected field. For example, if you
ask for subtotals based on the Customer field,

the way you want, Excel offers
a wide variety of chart types,
including pie charts, column
charts, and stock charts. Excel
also offers a large number of
chart options that enable you to
add chart titles and data labels,
control the chart legend and
gridlines, format the chart layout
and style, change the chart type,
and more.
13_577639-ch11.indd 18413_577639-ch11.indd 184 3/15/10 2:47 PM3/15/10 2:47 PM