← Functions

Use Data Table for Scenario Analysis

How to use Excel's Data Table feature to run one- and two-variable scenario analyses and stress-test financial models without manually changing inputs.

Scenario Analysis is a useful financial decision tool and a vital part of strategic planning. It projects outcomes of different scenarios so users can hope for the best and prepare for the worst — for example, identifying at what revenue level a project starts incurring a loss.

The Data Table function in Excel is not intuitive to use, but once you know how, it becomes very powerful. You can build a Data Table with one variable (a list of results) or two variables (a matrix).

One-Variable Data Table

This example uses a tiered commission rate structure. To build the scenario analysis:

  1. Enter the list of Sales Amount scenarios you want to test (e.g., $500,000 to $2,750,000 in $250,000 increments).
  2. In the cell at the top of the results column, reference the output cell you want to calculate (e.g., =E11 for Total Compensation Amount).
  3. Highlight the full list including the header reference cell.
  4. Click the Data tab → What-if Analysis → Data Table…
  5. In the Column input cell field, enter the cell where Excel should substitute each scenario value (e.g., the Sales Amount input cell).
  6. Click OK.

Excel populates the results column with calculated outputs for each scenario value.

Two-Variable Data Table

You can also create a two-dimensional data table showing results for two variables simultaneously. For example, analyzing monthly mortgage payments across different down payment percentages and interest rates:

  1. Set up a table with one variable across the top row and the other down the left column.
  2. In the intersection cell (top-left of the results area), reference the output formula (e.g., =B7).
  3. Highlight the full table including headers and the reference cell.
  4. Click Data → What-if Analysis → Data Table…
  5. Set the Row input cell to the variable represented in the top row.
  6. Set the Column input cell to the variable represented in the left column.
  7. Click OK.

Excel fills the table with the result for every combination of the two variables.

Note: A Data Table cannot obtain data from another Data Table. You cannot use a value from one Data Table as an input to another.