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". The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “data table sensitivity analysis”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. 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. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.
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
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. 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. Here is the takeaway for “data table sensitivity analysis”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.
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.
Other ways people ask this
On the way here you may have searched this as “sensitivity analysis using excel” and “excel sensitivity analysis data table” — it is all the same task, and this page is the single, complete answer to it.
Why do people search for this in so many different ways?
Because the same task has many names. “sensitivity analysis using excel”, “excel sensitivity analysis data table” all point at the one operation explained on this page, which is why they all lead here.