In Excel: lay the input values along a row or column next to your formula, select the block including the formula cell, then Data ▸ What-If Analysis ▸ Data Table and give the row and/or column input cell.
Build the model first so one formula cell holds the number you care about.
For one variable: list the input values down a column, and put a reference to the formula (=B5) one row up and one column right of the list.
Select the rectangle covering both the input list and the formula reference.
Data ▸ What-If Analysis ▸ Data Table. Set Column input cell to the model cell the listed values should replace (use Row input cell if the values run across a row).
For two variables: put one set of values down the left column, the other across the top row, the formula reference in the corner, and fill in both input cells.
For the reverse question, use Data ▸ What-If Analysis ▸ Goal Seek: set the formula cell to a target value by changing one input cell.
What this does
What-If Analysis is three tools on one menu. A Data Table recalculates one formula across a grid of input values — one variable along a column, or two variables across a column and a row — and shows every result at once. Goal Seek runs the opposite direction: you state the answer you want and Excel solves for the single input that produces it. Scenario Manager stores named sets of input values so you can flip between Base, Best and Worst. Data Tables are the ones people mean by "sensitivity analysis". Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “2 variable data table excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
A loan payment sits in B5 as =PMT(B2/12, B3, -B1). To see the payment at several rates, put rates 3%, 3.5%, 4%, 4.5%, 5% down D3:D7 and the formula reference =B5 in E2. Select D2:E7, then Data ▸ What-If Analysis ▸ Data Table, leave Row input cell empty and set Column input cell to B2. Excel fills E3:E7 with the payment at each rate — €1,264, €1,347, €1,432, €1,520, €1,610 on a €300,000 25-year loan. It answers the question every model gets asked — "and what if the rate is a point higher?" — without cloning the sheet five times and losing track of which copy was which. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “2 variable data table excel”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Putting the formula in the wrong corner — it belongs above the column of inputs and left of the row of inputs, in the cell where the two axes meet.
- Selecting only the input values without the formula cell, which produces an empty table.
- Trying to edit or delete individual result cells; a Data Table is one array and only clears as a whole.
- Pointing the input cell at a cell the formula does not actually depend on, which produces the same number in every row.
- Leaving a large two-variable table on Automatic calculation in a heavy workbook — set Formulas ▸ Calculation Options ▸ Automatic Except for Data Tables.
Frequently asked questions
What is What-If Analysis in Excel?
A menu on the Data tab holding three tools: Data Table (recalculate a formula across a grid of inputs), Goal Seek (solve for the input that hits a target output) and Scenario Manager (save named sets of inputs).
How do I make a two-variable data table?
Put one variable's values down a column, the other's across a row, and a reference to the formula in the corner cell where they meet. Select the whole block and set both Row input cell and Column input cell.
Why can I not delete part of my data table?
The results are a single array formula. Select the entire result range and delete it in one go.
Data Table or Scenario Manager?
A Data Table when you want to see one output across many values of one or two inputs. Scenario Manager when you want to switch several inputs at once between named cases.