A one-row data table in Excel is a What-If Analysis tool that tests several possible values for one input and displays the results across a row. To create one in Excel for Microsoft 365 on Windows, put a formula in the cell at the upper-left of the table, enter test values to its right, select the formula cell and the row beneath the values, then choose Data > What-If Analysis > Data Table and set the Row input cell.
What “one-row data table” means
The phrase usually refers to a one-variable What-If Analysis data table whose trial values are laid out horizontally. Excel temporarily substitutes each value into one selected input cell, recalculates a formula that depends on that cell, and places each result beneath its corresponding trial value. It is a quick way to compare outcomes without changing the input manually over and over.
For example, you might compare the area of a rectangle at several widths while keeping its length fixed. The trial widths form one row; the calculated areas appear in the row below. The method is useful whenever a result depends on one changing assumption, such as a production quantity, hourly rate, discount percentage, or planned number of attendees.
There is a second, less specific meaning: someone may call any worksheet range with one record row a one-row table. That is not necessarily Excel’s What-If Analysis feature. An Excel Table, created with Insert > Table, is a structured range for organizing and filtering records. It can have one data row, but it does not automatically test different input values. The distinction matters because the setup and purpose are different.
How Excel’s What-If data table works
A horizontal one-variable data table has three parts: a formula in its top-left corner, a series of possible input values across the same row, and result cells directly below those values. The formula must calculate an outcome that ultimately depends on the model’s input cell. In the Data Table dialog, you identify that model cell as the Row input cell. Excel substitutes each value from the top row into it, recalculates the formula, and fills the results row.
The original input cell is not permanently replaced by every trial value. Excel uses the alternatives to calculate the table and restores the model’s original input afterward. This makes it possible to compare scenarios while keeping the main calculation intact. The table itself is a special What-If Analysis range, not a collection of ordinary formulas that independently refer to each scenario value.
Because the alternatives run horizontally, use the Row input cell box. A column-oriented one-variable table instead lists trial values vertically and uses the Column input cell box. These labels describe where the trial values are arranged, not whether the model input cell itself happens to be in a worksheet row or column.
Build a one-row data table: a worked example
Set up a small rectangle-area model. Enter a fixed length of 10 in B2 and a current width of 2 in B3. In B4, calculate the area using this formula:
With the length in B2 and width in B3, enter =B2*B3 in B4. The result is 20 square units.
Now create the scenario range. In C6, enter a link to the model result:
With the calculated area in B4, enter =B4 in C6. This makes C6 the data table’s top-left formula cell.
Enter the test widths 1, 2, 3, and 4 in D6:G6. Select C6:G7, including the formula cell, all four trial values, and the empty result cells beneath them. On the ribbon, choose Data > What-If Analysis > Data Table. Leave Column input cell blank and set Row input cell to B3, then choose OK.
Excel fills D7:G7 with 10, 20, 30, and 40. For instance, it evaluates the model with B3 temporarily set to 3, so the length of 10 multiplied by the width of 3 gives an area of 30. The value 2 in the example happens to match the model’s starting width, but each trial is calculated from the same model formula.
Choose the input cell and arrange the range correctly
The Row input cell must be the single cell in your model that the trial values are meant to replace. It is not the formula cell, the first cell containing a test value, or the result cell. In the example, B3 is the width assumption, so B3 belongs in the Row input cell box. If the model’s output does not change when you alter that input manually, the data table will not produce meaningful different results either.
Before opening the dialog, check the layout carefully:
- Put the formula that returns the model output in the upper-left corner of the selected range.
- Put all trial inputs across the same row, immediately to the right of that formula.
- Leave the row directly beneath the trial inputs available for Excel to write the results.
- Select the whole rectangle, including the formula, trial values, and output cells.
- Enter the model’s input cell as Row input cell, not Column input cell.
The formula can refer directly to a calculation or link to a result cell elsewhere in the model. A link is often clearer when the model is larger: it keeps the scenario range focused on the output being compared and leaves the main calculation in its normal location.
Read and format the results
Each result aligns with the trial value immediately above it. In the rectangle example, the 30 beneath the trial width 3 is the resulting area for that width. That alignment is important when there are many scenarios; adding labels or formatting helps readers avoid confusing an input with its output.
You can format the input row and results row as you would other worksheet cells. For example, apply number formats, add borders, or label the range with a nearby heading such as Width and Area. A data table does not automatically add explanatory labels, units, or chart titles, so include those yourself when the worksheet will be shared. If the results represent percentages, dates, or currency, format the result cells appropriately rather than assuming Excel will infer the intended display.
To compare the results visually, you can select the scenario values and corresponding outputs and insert a chart suited to the data. For a simple relationship such as width and area, a scatter chart can show how the output changes as the input increases. A chart is optional; the data table’s main job is to calculate and lay out the alternatives.
How it differs from Excel Tables and other What-If tools
An Excel Table is intended for a list of records. It provides features such as filter buttons, structured references, and automatic expansion when data is added. Create one by selecting the data and choosing Insert > Table, or use Ctrl+T on Windows. A What-If Analysis data table, by contrast, is meant to calculate possible outcomes from a model. It does not turn the scenario range into a sortable, filterable record list in the same way.
Excel’s other What-If Analysis tools serve different purposes. Goal Seek works backward from a desired result to find a single input value. Scenario Manager stores sets of assumptions that can change multiple input cells. A one-variable data table is especially convenient when you want a row of alternatives for one input and a formula-based result for each. A two-variable data table can test combinations of two inputs, but its layout and dialog setup differ.
For repeated analysis with many inputs or more elaborate logic, ordinary formulas, charts, or a dedicated scenario sheet may be easier to maintain. Choose the data table when the question is essentially, “What would this formula return for each of these values in one input cell?” It is not a replacement for a well-organized source-data table or a general-purpose database.
Common problems and fixes
If Excel returns the same result for every trial value, first confirm that the model formula really depends on the designated input cell. Also verify that the correct cell was entered as Row input cell. A formula that uses a fixed number instead of the input cell, or that refers to a different assumption cell, cannot respond to the alternatives in the intended way.
If the dialog produces no useful output, check that the selected range includes the formula cell and the blank result row, as well as the test values. A common mistake is selecting only the input row, leaving out the formula at the upper-left, or entering test values below the formula when the intended layout is horizontal. If values are in a column, either rearrange the range or use a column-oriented table with Column input cell.
Other troubleshooting checks include:
- Confirm the formula cell calculates correctly before creating the table.
- Check that the input cell contains a value that Excel can substitute, such as a number or percentage, rather than a label.
- Make sure the trial values are actually different if you expect different results.
- Review workbook calculation settings if results appear stale; calculation mode can affect when formulas refresh.
- Remove or recreate the whole data table range if you need to change its structure; its result cells are generated as one data-table operation.
What-If Analysis data tables are a desktop Excel feature. Excel for the web can preserve workbook content, but does not provide the desktop Data Table command for creating or changing these tables. Open the file in desktop Excel to set one up. On Mac, look for Data Table under the Data tab’s What-If Analysis controls; ribbon arrangement can differ from Windows, but the same horizontal layout and Row input cell concept apply.
When a different method may be better
A one-row data table is efficient for a modest set of alternatives, but it may not be the clearest choice for every task. If you need to compare several inputs changing together, Scenario Manager may fit better. If you need to find an input that produces a target output, Goal Seek is the direct tool. If you simply want a set of independent calculations visible and editable, copying a regular formula across cells can be more transparent than using a What-If data table.
For example, if each scenario has a separate quantity and unit price, you could create a conventional table with one row per scenario and a formula in an output column. That structure is easier to sort, filter, append, or use in a PivotTable. By contrast, the horizontal What-If table is compact and convenient when there is one model input cell and a single output formula to evaluate repeatedly.
Frequently Asked Questions
How do I create a one-variable data table across a row in Excel?
Put the output formula in the cell at the upper-left of the range, place trial values to its right, and leave result cells below. Select the complete range, choose Data > What-If Analysis > Data Table, and set the model input cell in Row input cell.
What is the difference between Row input cell and Column input cell?
Use Row input cell when the trial values are arranged horizontally in one row. Use Column input cell when they are arranged vertically in one column. The selected input cell is the model assumption Excel substitutes, not a cell containing one of the trial values.
Can a one-row data table test two inputs?
No. A one-variable data table substitutes alternatives for one input cell. Excel also supports two-variable data tables, which compare combinations of two input cells, but they require a two-dimensional arrangement with trial values along a row and a column.
Why do all the results in my data table match?
Often the output formula does not depend on the input cell you selected, or the wrong cell was entered as Row input cell. Check the model by changing its input manually and confirming that the output changes before rebuilding the table.
Is a one-row data table the same as an Excel Table?
No. An Excel Table organizes records and offers features such as filtering and structured references. A What-If Analysis data table calculates outcomes for trial input values. The word table is used for both, but the features and setup are different.
Related
- Add Quadrants to an Excel Scatter Plot: Step-by-Step
- How Excel Files Become Corrupted: Causes and Risks
- How to Activate Flash Fill in Excel: Shortcuts and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Move Between Sheets in a WorkbookIn Excel for Microsoft 365 on Windows, press Ctrl+Page Down to move to the next worksheet and Ctrl+Page…
- Excel Split Cell Into Columns: Methods and TipsYes. In Excel for Microsoft 365 on Windows, select the cells, then choose Data > Text to Columns…
- Best Way to Compare Data in Two Excel Sheets: 5 MethodsFor most people, the best way to compare data in two Excel sheets is to match records using…
- Clear Cache on the Outlook App: Safe Steps by DeviceTo clear the Outlook app’s cache, use the method for your device: on Android, open Settings > Apps…
- How to Divide a Cell in Half in Excel: 3 OptionsTo divide a cell in half in Excel, first choose what you mean by “divide”: Excel cannot turn…
- Google Sheets Column Running Total: Formulas That WorkTo calculate a running total in a Google Sheets column, enter =SUM($A$2:A2) in B2 when your numbers are…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.