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. For “use data table function in 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 formula 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. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
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. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. If you take one thing from this page on “use data table function in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
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 “how to use the data table function in excel” and “excel data table function” — 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. “how to use the data table function in excel”, “excel data table function” all point at the one operation explained on this page, which is why they all lead here.