Playing WhatIf 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 whatif analysis on the data. Whatif 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 whatif analysis features in Excel 2007 include goal seeking and scenarios.
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 twovariable 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 WhatIf 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 onevariable 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 twovariable 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 twovariable 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.