What-If Analysis in Excel: Build a Data Table

A what-if analysis data table shows how one formula's answer changes when you change one or two of its inputs. Instead of typing a new interest rate, writing down the payment and trying the next rate, you list all the rates once and Excel fills in every result in one grid. This guide is for anyone who builds budgets, loan models or price plans and wants to compare many "what if" cases side by side.

What is a data table in Excel?#

A data table is one of three What-If Analysis tools in Excel, next to Goal Seek and Scenario Manager. It takes a formula that already works, swaps in each value from a list, and writes each result into a grid.

• One-variable data table: one input changes, such as the interest rate. You get one column (or row) of results.
• Two-variable data table: two inputs change, such as the rate and the loan term. You get a full grid of results.

Goal Seek works the other way: you give it the result you want and it finds one input. Scenario Manager saves named sets of several inputs. A data table is the best of the three when you want to see a whole range of answers at once.

All the examples use this loan model in A1:B4:

B4 returns 469.70. The minus sign in front of B1 makes the payment positive. The guide to the PMT function explains each argument.

How to build a one-variable data table#

A one-variable data table lists input values down a column, with the formula one row above and one column to the right of the first value. Here is how to test five interest rates:

1. In A8:A12, type the rates 4%, 5%, 6%, 7% and 8%.
2. In B7, one row above the first rate and one column to the right, type =B4. This points at the payment formula.
3. Select A7:B12, the formula and the rates together.
4. Go to Data > What-If Analysis > Data Table (in the Forecast group on Windows; on a Mac it is also under Data > What-If Analysis).
5. Leave Row input cell empty. In Column input cell, click B2, the rate cell.
6. Click OK.

Excel fills B8:B12 with the payment for each rate:

The column input cell is "the cell my column of values replaces". Excel puts 4% into B2 behind the scenes, reads B4, writes the answer, and moves on to 5%. Your real B2 never changes.

How to build a two-variable data table#

A two-variable data table puts the formula in the top-left corner, one input across the top row and the other down the left column. Here is rate against loan term:

1. In A15, type =B4.
2. In B15:D15, type the terms 36, 48 and 60.
3. In A16:A18, type the rates 5%, 6% and 7%.
4. Select A15:D18 and open Data > What-If Analysis > Data Table.
5. Row input cell: B3 (the months, because the terms run across the row).
6. Column input cell: B2 (the rate, because the rates run down the column).

The most common mistake is swapping the two input cells. If the numbers look strange, check that the row input cell matches the values in the top row.

Why can't I edit one cell of a data table?#

Excel writes the results as one array formula, {=TABLE(B3,B2)}, across the whole result area. You can't type over, move or delete a single cell of it. Excel shows "Cannot change part of a data table", the same kind of message explained in why Excel says you can't change part of an array.

To change the table:

• Change the inputs in the top row or left column. The results update.
• Change the model in A1:B4. The whole table recalculates.
• Delete the table: select every result cell (B16:D18 in the example) and press Delete.

How to keep a big data table from slowing Excel down#

A data table recalculates every time anything in the workbook changes, so a large one can make Excel slow. The fix is a calculation setting:

1. Go to Formulas > Calculation Options.
2. Choose Automatic Except for Data Tables.
3. Press F9 when you want the tables to update (on a Mac, you may need Fn+F9).

Remember that the table now shows old numbers until you press F9. If every cell shows the same value, this setting is usually the reason.

Data table rules and limits#

A data table is quick to build, but it has firm rules:

• Same sheet only. The row and column input cells must be on the sheet that holds the table.
• One or two inputs. For three or more changing inputs, use Scenario Manager or build the grid with formulas.
• The formula must depend on the input cell. If B4 doesn't use B2, changing the rate does nothing and every result looks the same.
• Values, not formulas, in the input lists work best. Typed rates and terms are easiest to check.

Data table or a grid of formulas?#

You can build the same grid with ordinary formulas and $ signs, and many people prefer it because you can see and audit every cell. For the one-variable table, type this in B8 and fill down:

=PMT(A8/12,$B$3,-$B$1)

The $ signs lock the months and the loan, while A8 moves down to each new rate. For a two-variable grid you need mixed references like $A16 and B$15 so one part locks the column and the other locks the row. The guide to absolute references and the dollar sign covers the basics.

Use a data table when the model is long (a full budget or a loan amortization schedule) and you only want to vary its inputs. Use a grid of formulas when the calculation fits in one cell. The same idea works for profit: vary the price or the units and watch the result, as in this break-even analysis in Excel. You can also vary a percentage, such as a cost increase of 2%, 4% or 6%, and read the new totals.

Practise it#

Data tables only work if the model under them is right, and that means solid $ references. Practise absolute references in a live spreadsheet: you fill formulas across and down and see straight away when a reference drifts. Then practise a budget model with simple maths and practise percent change, the two calculations most what-if tables test. Or start with the exercise on this page.