Two-Variable Data Table (Grid)

All versionsData Table

Vary two inputs at once and read the result off a grid — loan payment by rate and term, profit by price and volume. A two-variable Data Table fills the whole matrix in one step.

Quick formula: formula in the top-left corner, one input set down the side, the other across the top:

Data → What-If Analysis → Data Table → Row input cell + Column input cell

Each grid cell shows the result for that row-input / column-input combination.

Functions used (tap for the full reference guide):

PMT

The example

Payment by rate (rows) and term (columns) on $25k.

	A	B	C
1	Rate \ Term	48	60
2	5%	$575	$472
3	6%	$587	$483

The formula

The corner holds the formula; the two inputs flank the grid:

Corner cell = =PMT(rate/12, term, -25000) Row input cell = term; Column input cell = rate // fills the whole grid

How it works

Two inputs, one result, a full matrix:

Put the model formula in the top-left corner of the grid.
List one input set down the left column and the other across the top row.
Select the whole block, then Data → What-If Analysis → Data Table.
Set Row input cell (the across-the-top variable) and Column input cell (the down-the-side variable). Excel fills every combination.

Corner formula often looks odd. It shows the model’s current result, which can be confusing — many people format the corner cell’s font white to hide it, leaving a clean grid of results.

Try it: interactive demo

Live demo

Payment grid: rate × term ($25k).

Variations

Hide the corner

White font on the corner formula.

Profit grid

Corner = profit; price × volume.

Conditional format

Color-scale the grid to spot sweet spots.

Pitfalls & errors

Don’t swap the input cells. Row input = the variable across the top; Column input = the variable down the side. Swapping them transposes (and breaks) the grid.

Only one formula. A two-variable table allows a single output formula (in the corner) — you can’t add more outputs as with a one-variable table.

Recalc cost. Large 2-D tables are heavy; keep the grid reasonable.

Practice workbook

📊

Download the free Two-Variable Data Table (Grid) practice workbook

A rate×term payment grid (formula form) with color scale, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a two-variable data table in Excel?

Put the model formula in the top-left corner, one input set down the left and the other across the top, select the block, then Data → What-If Analysis → Data Table and set both Row and Column input cells.

Which is the Row input cell?

The Row input cell is the variable listed across the top row; the Column input cell is the variable down the left column. Don't swap them.

Why does the corner cell show a number?

It's the model's current result, required for the table. Format its font white to hide it for a clean grid.

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

Function references: PMT