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. Treat “one variable data table excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. 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 turns a workflow that saves repeating the same clicks every week into a method you can reuse, explain, and defend when the workbook leaves your screen.
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. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. 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 “one 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.
Other ways people ask this
People reach this page typing “1 variable data table excel”, “does capital one allow data importation in excel”, “one variable table excel” and “one way data table excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “1 variable data table excel”, “does capital one allow data importation in excel”, “one variable table excel” all point at the one operation explained on this page, which is why they all lead here.