Microsoft Office Tutorials and References

In Depth Information

**Playing What-If with Data Tables**

Figure 8-1:

Sales

projection

spreadsheet with

a column

of possible

growth

percentages to

plug into a

one-variable

data table.

As you can see in Figure 8-2, I entered a column of possible growth rates

ranging from 1% all the way to 5.5% down column B in the range B8:B17.

To create the one-variable data table shown in Figure 8-2 that plugs each of

these values into the sales growth formula, I follow these simple steps:

1. Copy the original formula entered in cell B5 into cell C7 by

typing
(equal to) and then clicking cell B5 to create the formula
=

=Projected_Sales_2014.

The copy of the original formula (to substitute the series of different

growth rates in B8:B17 into) is now the column heading for the one-vari-

able data table.

2. Select the cell range B7:C17.

The range of the data table includes the formula along with the various

growth rates.

3. Click Data
➪
What-If Analysis
➪
Data Table on the Ribbon.

Excel opens the Data Table dialog box.

4. Click the Column Input Cell text box in the Data Table dialog box and

then click cell B3, the Growth_2014 cell with the original percentage,

in the worksheet.

Excel inserts the absolute cell address, $B$3, into the Column Input Cell

text box.