A one-dimensional data table in Excel shows how changing one input affects one or more formula results. In Excel for Microsoft 365 on Windows, put trial values in a row or column, link a nearby cell to the result formula, then choose Data > What-If Analysis > Data Table and specify the model’s Row input cell or Column input cell.
What a One-Variable Data Table Does
A one-dimensional data table, also called a one-variable data table, is an Excel What-If Analysis tool. It recalculates a formula repeatedly, temporarily substituting a series of possible values into one selected input cell. The results appear beside or beneath those trial values. This makes it easy to compare outcomes without replacing the input in your working model over and over.
For example, a business might want to see how monthly revenue changes at different sales volumes while keeping its price fixed. A household budget might compare a range of utility costs against a remaining-balance calculation. The table does not forecast which input will happen; it shows the result of each value under the assumptions already built into the workbook.
The word data table has another meaning in Excel: a regular range of organized data, often formatted with Insert > Table. A What-If Analysis data table is different. It is a calculation tool, and its results are linked to a formula and one model input cell.
Build a Simple Example
Start with a small model in a blank worksheet. In this example, the unit price is in B2 and the number of units sold is in B3. Put the value 20 in B2 and 100 in B3. With price in B2 and units in B3, enter this formula in B4 to calculate revenue; the result is 2,000.
=B2*B3
Next, list possible unit counts down a column. Enter 50, 75, 100, 125, and 150 in A8:A12. In B7, enter a reference to the result cell. With revenue in B4, this formula in B7 returns 2,000 using the current model inputs.
=B4
The layout now has trial values in A8:A12, the result reference in B7, and an empty result area in B8:B12. Select A7:B12, including the blank corner cell and the result-reference cell. Go to Data > What-If Analysis > Data Table. Because the trial values are in a column, leave Row input cell blank and set Column input cell to B3, the model cell for units sold. Select OK.
Excel fills B8:B12 with the calculated revenues: 1,000, 1,500, 2,000, 2,500, and 3,000. For instance, the row with 125 temporarily substitutes 125 into B3, so the model calculates 20 times 125, or 2,500. The original value in B3 remains 100 after the calculation.
Set Up Values Across a Row Instead
You can place trial values horizontally rather than vertically. For a row-oriented table, put the formula reference in the cell immediately to the left of the first trial value, then select the formula cell and all the trial-value cells and the blank output cells beneath them. In the Data Table dialog, use Row input cell to identify the model input that Excel should temporarily replace.
For example, if B2 contains price, B3 contains units, and B4 calculates revenue, you could put the reference to B4 in A7 and trial prices in B7:F7. Select A7:F8, assuming row 8 is where results should appear, and set Row input cell to B2. Excel will calculate revenue for each price while keeping the units input unchanged. The location of the trial values determines which input box matters: a vertical list uses Column input cell; a horizontal list uses Row input cell.
Do not confuse the direction of the trial values with the location of the model input. The input cell can be elsewhere on the sheet, and it should be the single cell that drives the scenario. In the revenue example, a vertical list of unit counts uses B3 as the column input cell, even though B3 is not in the table’s A:B output range.
Why the Formula Reference Matters
The corner formula tells Excel what to calculate for every trial. It should refer to a result already calculated by the model, rather than repeat a separate calculation for each value. A table can also show more than one result: place multiple result references across the top row for a vertical trial list, or down the first column for a horizontal list, and select the full rectangular layout before opening Data Table.
For instance, a pricing model could return both revenue and gross profit for each possible price. Each result reference should point to a separate model output formula, while the table still substitutes values into just one input cell. This is useful when several outputs depend on the same assumption, but it does not turn the table into a two-variable analysis.
Data tables are a special What-If Analysis feature, not ordinary copied formulas. Excel commonly displays their calculation cells using the TABLE function internally. You generally do not type TABLE yourself; create or change the analysis through the Data Table command. The displayed results are tied to the table’s setup, so moving or deleting its cells can disrupt the analysis.
Check the Layout Before You Calculate
A correctly arranged table prevents many confusing results. Before selecting the range, check these points:
- There is a formula reference in the corner of the table: above the first trial value for a vertical list, or to the left of the first trial value for a horizontal list.
- The trial values form one uninterrupted row or column and are included in the selected range.
- The input cell is the model cell Excel should replace, not the formula-output cell or the cell holding the list of trial values.
- The result formula actually depends on the selected input cell. If it does not, the table may repeat the same output for every trial.
- The table range includes the empty cells where Excel should place results.
For a vertical layout, select the corner formula cell, the trial-value column, and the result column together. For a horizontal layout, include the corner formula cell, the trial-value row, and the output row. If you select only the values or omit the formula-reference corner, Excel cannot identify the intended analysis structure.
Common Problems and Fixes
Every result is identical. Check whether the selected input cell is actually used by the output formula. Also verify that you chose the correct Row input cell or Column input cell. A vertical list paired with the Row input cell setting, for example, does not describe the intended layout.
The results look unrelated to the trial values. Confirm that the input cell contains the assumption you mean to vary. If the model uses a different cell, changing the one you selected will not affect the output. Check formulas and references in the model as well as the table setup.
The formula reference shows an unexpected value. A standalone reference such as B7 may display the current model result before the table is calculated. That is normal. The table results are produced when Excel substitutes each trial value into the specified input cell and recalculates the model.
Excel is slow to recalculate. What-If Analysis data tables can require many recalculations, especially in a large workbook with complex formulas. Reduce the number of trial values, simplify dependent calculations where practical, or change calculation settings only if you understand the effect on the workbook. In Excel for Windows, calculation options are available at Formulas > Calculation Options. Manual calculation can leave workbook results out of date until recalculation is triggered, so restore the appropriate setting when finished.
You cannot edit an individual result cell normally. The results belong to the data table’s output range. Edit the trial values, the corner formula, or the Data Table setup instead of treating one output as an independent formula. If the layout is unclear, create a fresh table in an empty range.
One-Variable Tables Compared With Other Tools
A one-variable data table answers a focused question: what happens to one or more formula results when one input takes different values? A two-variable data table varies two inputs, typically using one set of trial values across a row and another down a column. It is appropriate when, for example, both price and sales volume might change. A one-variable table cannot test both independently in the same grid.
Other Excel What-If Analysis tools serve different purposes. Goal Seek works backward from a desired formula result to find one input value; it does not make a full list of scenarios. Scenario Manager stores named sets of input values for comparing cases, which can be useful when a scenario changes several assumptions together. A standard formula copied down a worksheet can also calculate scenarios, but it usually needs a formula design that passes each row’s value through the calculation. The data table is convenient when an existing model already has a single input cell to substitute.
Use a one-variable table when you want a compact sensitivity analysis and your model is already set up. Choose a two-variable table when two assumptions need to vary together, or another tool when you need named cases or a target-value search.
Excel Version and Platform Notes
The steps above are for Excel for Microsoft 365 on Windows. In desktop Excel for Mac, the Data Table feature is also available through the Data tab’s What-If Analysis controls, though ribbon organization and dialog appearance can differ slightly by release. The same row-versus-column input logic applies.
Excel for the web has limitations for What-If Analysis data tables. An existing workbook may show data-table results, but the web app does not provide the full desktop workflow for creating or editing them. If the Data Table command is unavailable, open the workbook in desktop Excel. Menu labels and availability can vary with updates and license editions, so check the installed desktop app if a command is missing.
Frequently Asked Questions
What is a one-dimensional data table in Excel?
A one-dimensional data table recalculates a formula for a series of possible values entered in one row or column. It temporarily substitutes each value into one selected model input cell and displays the resulting output.
What is the difference between Row input cell and Column input cell?
Use Row input cell when the trial values are arranged horizontally. Use Column input cell when they are arranged vertically. In either case, select the model cell whose value should be replaced during the calculation.
Can a one-variable data table show more than one result?
Yes. Place references to multiple model outputs in the table’s formula area and include those references in the selected layout. Excel can calculate each output for every trial value, while still varying only one input cell.
Why does Excel show the same answer in every row?
The output formula may not depend on the input cell you selected, or the wrong input-cell box may have been used. Check the model’s cell references and confirm that the trial values run in the same direction as the input-cell setting.
Can I create a What-If Analysis data table in Excel for the web?
Excel for the web does not offer the full desktop controls for creating or editing these tables. Use desktop Excel for the Data > What-If Analysis > Data Table workflow; existing results may still be visible in the web app.
Related
- How to Split a Cell in Excel: Text, Columns, and More
- How to Run Scenario Manager in Excel
- How to Run Goal Seek in Excel to Find a Target Value
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Clothing Inventory Spreadsheet: Build and Use OneA clothing inventory spreadsheet is a worksheet for recording each sellable garment variant, its quantity, location, and other…
- Wedding Budget Spreadsheet for Beginners: A Simple SetupA beginner wedding budget spreadsheet should show your total spending limit, planned costs, final or contracted costs, and…
- Excel Data Model in the Web: What Works and What Doesn’tExcel for the web can open a workbook that contains an Excel Data Model, but it does not…
- Export an Excel Table as an Image: Step-by-StepTo export an Excel table as an image in Microsoft Excel for Microsoft 365 on Windows, select the…
- Hanging Indent in Word: Format Reference Lists EasilyTo hang-indent references in Microsoft Word, select the reference paragraphs, open the Paragraph settings from the Home tab,…
- How to Find Rank in Excel with RANK.EQTo rank a value in Excel, use RANK.EQ. With scores in C2:C6 and the first score in C2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.