See how one result reacts as a single input sweeps across a range — a loan payment at many rates, profit at many volumes. A Data Table recalculates the model for each input in one structured block.
The example
Monthly payment on a $25,000 loan at several rates.
| A | B | |
|---|---|---|
| 1 | Rate | Payment |
| 2 | 5.0% | $471.78 |
| 3 | 6.0% | $483.32 |
| 4 | 7.0% | $495.03 |
The formula
The model formula sits at the head of the input column:
How it works
A Data Table sweeps one input automatically:
- List the input values you want to test down a column.
- Put your model formula one row above the first input and one column to the right.
- Select the block (inputs + formula), then Data → What-If Analysis → Data Table.
- Set Column input cell to the model’s input cell. Excel computes the result for every listed value.
No Data Table? Use a plain formula. Often you can just write the model formula referencing the input cell in each row — =PMT(A2/12,60,-25000) filled down a rate column does the same thing and is easier to audit. Data Tables shine when the model is complex and you don’t want to rewrite it per row.
Try it: interactive demo
Loan payment across rates (25k, 60 mo).
Variations
Plain formula column
Often simpler:
Row orientation
Inputs across a row → Row input cell.
Multiple outputs
Add more formula columns to the table head.
Pitfalls & errors
Formula placement is precise. For a column table, the formula goes one row up and one column right of the inputs — misplacing it breaks the table.
Column vs Row input cell. Inputs down a column use Column input cell; across a row use Row input cell. Mixing them gives wrong results.
Data Tables recalc a lot. Big tables slow the workbook; consider manual recalc or a plain formula column.
Practice workbook
Frequently asked questions
How do I make a one-variable data table in Excel?
Where exactly does the formula go?
Is there a simpler alternative?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class