Playing What-If with Excel 2007’s Data Tables

With Excel 2007’s data tables, you enter a series of possible values that Excel plugs into a single formula so you can perform what-if analysis on the data. What-if analysis enables you to explore the possibilities in a worksheet by inputting a variety of promising or probable values into the same equation and letting you see the possible outcomes in the worksheet. In addition to data tables, other what-if analysis features in Excel 2007 include goal seeking and scenarios.

Excel supports two types of data tables, a one-variable 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 a series of possible values for two input values in a single formula.

Both types of data tables use the same Data Table dialog box that you open by choosing What-If Analysis→Data Table in the Data Tools group on the Data tab of the Ribbon. 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 that serves as the Row Input Cell (that substitutes the series of possible values you’ve entered across columns of a single row) and another that serves as the Column Input Cell (that substitutes the series of possible values you’ve entered down the rows of a single column).

An example of a two-variable data table (B3 is the row input cell; B4 is the column input cell).

The Excel data table feature works by creating a special kind of formula called an array formula in the blank cells of the table. An array formula (indicated by being enclosed in a pair of curly brackets) is unique in that Excel creates copies of the formula in each blank cell of the selection at the time you enter the original formula (you don’t make the formula copies yourself). As a result, editing changes such as moving or deleting are restricted to the entire cell range containing the array formula.