How to Use Data Tables for What-If Analysis in Excel

Introduction

You have a loan payment model that returns $1,342 per month at 5% interest. You want to see what the payment looks like at rates from 3.5% to 6.5%. You could copy the PMT formula seven times and hard-code each rate, but a data table generates all the results from one formula in a single step.

The Spreadsheet

Here's a mortgage payment model with three inputs and one formula:

	A	B
2	Loan Amount	250000
3	Annual Rate	5%
4	Term (Years)	30
5	Monthly Payment	=PMT(B3/12, B4*12, -B2)

The PMT formula in B5 divides the annual rate by 12 for the monthly rate, multiplies the term by 12 for the number of payments, and takes the loan amount as a negative (money flowing out). The result is $1,342 per month.

That's one scenario. A data table shows you all of them.

Building a One-Variable Data Table

A one-variable data table takes a list of input values and runs the same formula for each one. Here, the input is the interest rate and the output is the monthly payment.

Set up the table in columns D and E:

	D	E
2		=B5
3	3.5%
4	4.0%
5	4.5%
6	5.0%
7	5.5%
8	6.0%
9	6.5%

Cell E2 points to the payment formula (=B5). The interest rates go down column D. The formula cell must sit one row above and one column to the right of the input values.

To fill the table:

Select the range D2:E9 (the formula cell, the input values, and the empty results column).
Go to Data > What-If Analysis > Data Table.
Leave Row input cell blank.
Set Column input cell to B3 (the cell the interest rate lives in).
Click OK.

Excel plugs each rate from column D into B3, recalculates the PMT formula, and writes the result in column E:

Rate	Monthly Payment
3.5%	$1,123
4.0%	$1,194
4.5%	$1,267
5.0%	$1,342
5.5%	$1,419
6.0%	$1,499
6.5%	$1,581

The spread between the lowest and highest rate is $458 per month, or about $165,000 over 30 years. One table, seven answers.

Key takeaway: A one-variable data table needs three things: the input values in a column, the formula reference one row above and one column to the right, and the column input cell telling Excel which cell to substitute. The inputs run down a column, so you specify the column input cell and leave the row input cell blank.

Building a Two-Variable Data Table

A two-variable data table changes two inputs at once. One goes across the top row, the other down the left column, and the grid fills with the result for every combination.

To see how payments change across both interest rate and loan term, set up a second table:

	D	E	F	G	H
12	=B5	15	20	25	30
13	4.0%
14	4.5%
15	5.0%
16	5.5%
17	6.0%

Cell D12 holds the formula reference (=B5). Loan terms in years go across the top row (E12:H12). Interest rates go down the left column (D13:D17).

Select D12:H17.
Go to Data > What-If Analysis > Data Table.
Row input cell: B4 (Term, because the terms run across the row).
Column input cell: B3 (Rate, because the rates run down the column).
Click OK.

Excel fills a 5-by-4 grid:

	15 years	20 years	25 years	30 years
4.0%	$1,850	$1,515	$1,320	$1,194
4.5%	$1,912	$1,582	$1,390	$1,267
5.0%	$1,977	$1,650	$1,462	$1,342
5.5%	$2,043	$1,720	$1,535	$1,419
6.0%	$2,110	$1,791	$1,611	$1,499

The top-right corner ($1,194 at 4% for 30 years) is the lowest monthly payment. The bottom-left ($2,110 at 6% for 15 years) is the highest, but a 15-year loan at 6% costs far less in total interest than a 30-year loan at 4%. Every combination sits in the same grid, ready to compare.

Data Tables vs Goal Seek

Data Tables and Goal Seek both live under Data > What-If Analysis, but they solve opposite problems:

	Data Tables	Goal Seek
Direction	Forward: how does the output change across different inputs?	Backward: what input produces a specific output?
Variables	1 or 2	1
Output	A table of many results	A single answer
Best for	"Show me payments at every rate"	"What rate gives me exactly $1,500?"

Use a data table when you want to explore a range of possibilities. Use Goal Seek when you already know the target and need to find the input that hits it.

For problems with more than two variables or constraints (like "minimize total interest while keeping the monthly payment below $2,000"), you need Solver (Data > Solver).

Things to Know

Array formula. The results in a data table form a single array. You can't edit or delete individual cells within it. To clear the results, select the entire output range and delete it all at once.
Recalculation. Data tables recalculate every time the workbook recalculates. Large tables with complex formulas can slow things down. To skip automatic recalculation, go to Formulas > Calculation Options > Automatic Except for Data Tables. The table then updates only when you press F9.
One formula per table. Each data table evaluates a single formula. To test a second output against the same inputs, build a separate table pointing to the other formula.

Conclusion

Data tables show how a formula's output changes across a range of inputs, producing dozens of results from one formula in a single step. One-variable tables test a single input, while two-variable tables create a grid that covers every combination of two inputs at once.

For finding the exact input that produces a specific target, see our guide on Goal Seek. For the loan payment formula used throughout this post, check out our guide on PMT. For how sensitivity grids are used in valuation models, see our guide on sensitivity analysis.