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.
On this page7
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 “create data table in excel”. 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: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.
In Google Sheets
This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. 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 and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. If you take one thing from this page on “create data table in excel”, make it the order of checks rather than the individual clicks: confirm what is selected, apply the step, and look at the result before moving on. That small routine is what keeps Excel work predictable when the same task comes back in a slightly different workbook.
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
This is also commonly searched as “how to create a data table in excel”, “how to make a data table in excel”, “excel how to make data table” and “how do i create a data table in excel”. They describe the identical operation, so you are in the right place no matter how you phrased it.
Why do people search for this in so many different ways?
Because the same task has many names. “how to create a data table in excel”, “how to make a data table in excel”, “excel how to make data table” all point at the one operation explained on this page, which is why they all lead here.