Microsoft Office Tutorials and References

In Depth Information

**Playing What-If with Data Tables**

Playing What-If with Data Tables

Data tables enable you to enter a series of possible values that Excel then

plugs into a single formula. Excel supports two types of data tables: a

onevariable data table that substitutes a series of possible values for a single

input value in a formula and a two-variable data table that substitutes series

of possible values for two input values in a single formula.

Both types of data tables use the very same Data Table dialog box that you

open by clicking Data
➪
What-If Analysis
➪
Data Table on the Ribbon or

pressing Alt+AWT. The Data Table dialog box contains two text boxes: Row Input

Cell and Column Input Cell.

When creating a one-variable data table, you designate one cell in the

worksheet that serves either as the Row Input Cell (if you’ve entered the series

of possible values across columns of a single row)
or as the Column Input

Cell (if you’ve entered the series of possible values down the rows of a single

column).

When creating a two-variable data table, you designate two cells in the

worksheet and, therefore, use both text boxes. One cell serves as the Row Input

Cell that substitutes the series of possible values you’ve entered across

columns of a single row, and the other cell serves as the Column Input Cell that

substitutes the series of possible values you’ve entered down the rows of a

single column.

Creating a one-variable data table

Figure 8-1 shows a 2014 sales projections spreadsheet for which a

onevariable data table is to be created. In this worksheet, the projected sales

amount in cell B5 is calculated by adding last year’s sales total in cell B2 to

the amount that we expect it to grow in 2014 (calculated by multiplying last

year’s total in cell B2 by the growth percentage in cell B3), giving you the

formula

=B2+(B2*B3)

Because I clicked the Create From Selection command button on the Ribbon’s

Formulas tab after making A2:B5 the selection and accepted the Left Column

check box default, the formula uses the row headings in column A and reads:

=Sales_2013+(Sales_2013*Growth_2014)