One-Column Data Table in Excel: How to Create One

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, create a one-column data table by placing trial values in a vertical list, putting a formula that refers to your model’s result in the cell above and to the right of the list, selecting the whole range, then choosing Data > What-If Analysis > Data Table. In the dialog, set Column input cell to the model input you want to vary and leave Row input cell blank.

What a one-column data table does

A one-column data table is an Excel What-If Analysis tool. It takes a series of possible values for one input, substitutes each value into a selected input cell in turn, recalculates a formula, and displays each resulting answer beside the trial value. It is useful when you want to compare many scenarios without manually changing an assumption and recording the result each time.

For example, a worksheet might calculate revenue from a unit price and a quantity. A data table can show the resulting revenue at several possible prices while leaving the quantity and the rest of the model unchanged. The trial prices go down one column; the corresponding revenue results appear in the next column.

The phrase can also mean an ordinary list of data arranged in a single Excel column. That is not the same feature. If you simply need to store, sort, or filter a list, use a normal range or an Excel Table. A one-column data table specifically means a What-If Analysis calculation table.

How the layout works

Excel needs two things: a list of test values and a formula that returns the result you want to examine. The formula must link to the existing model calculation. Excel then temporarily puts each test value into the model’s input cell, recalculates, and records the output. It does not permanently replace the input value in your model.

For a column-oriented table, arrange the cells like this:

  • Put the formula that links to the output in the top-right cell of the table area.
  • Put the possible input values directly below the top-left cell, in one vertical column.
  • Leave the top-left cell blank, unless you want to add a descriptive label outside the selected table range.
  • Place the calculated results in the column to the right of the trial values.

When you select the range, include the top row containing the output formula and all the trial values below it. The blank top-left cell is part of that selection. In the Data Table dialog, Column input cell means the original model cell that receives each value from the vertical list. It does not mean the results column.

Create a one-column data table step by step

  1. Identify the model input to test, such as a unit price, production level, or percentage assumption. Use a cell that contains an input value rather than a cell containing a formula.
  2. Identify the model output you want to compare. Make sure a worksheet formula already calculates it from the input.
  3. In a clear area, enter your test values vertically. Put a formula linking to the model output in the cell one row above and one column to the right of the first test value.
  4. Select the full rectangular range, including the output formula, the blank corner cell, all test values, and the cells where Excel should return results.
  5. On Windows, choose Data > What-If Analysis > Data Table. In the dialog, leave Row input cell empty and choose the model input cell for Column input cell.
  6. Select OK. Excel fills the results cells. Format the trial values and results appropriately, and add headings outside the calculation range if needed.

For the input-cell field, you can click the field and then click the relevant cell in the worksheet. Check that you selected the original model input, not one of the trial values in the table. If the input is on another worksheet, Excel may require the input cell to be on the same sheet as the data table; placing the scenario table beside the model or using a suitable input cell on that sheet avoids layout complications.

Worked example: compare revenue at different prices

Suppose B2 contains a unit price of 15, B3 contains a quantity of 120, and B4 calculates total revenue. With the price in B2 and quantity in B3, enter this formula in B4:

=B2*B3

At the starting assumptions, the result is 1,800, because 15 multiplied by 120 equals 1,800. The formula is a regular worksheet calculation; it is not the data-table command itself.

To test prices, put a link to the revenue result in B7 and enter trial prices in A8:A12. With the output formula in B7, trial values in A8:A12, and the model output in B4, enter this formula in B7:

=B4

Suppose A8:A12 contains 10, 12, 15, 18, and 20. Select A7:B12, then open Data > What-If Analysis > Data Table. Set Column input cell to B2 and leave Row input cell blank. Excel substitutes each price into B2, recalculates revenue using the unchanged quantity of 120, and returns 1,200, 1,440, 1,800, 2,160, and 2,400 in B8:B12. Your original value in B2 remains 15 after the calculation.

This example uses a simple multiplication so the results are easy to verify. In a larger workbook, the output formula can link to a more involved model, provided the model ultimately responds to the input cell being varied.

Read and format the results correctly

Each result corresponds to the trial value on the same row. A result is a calculated outcome under the model’s current assumptions, not a prediction that the outcome will actually occur. If the model also depends on a quantity, cost, date, or other assumption, those inputs stay as they are unless the calculation explicitly changes them too.

Use clear headings near the table, such as Price tested and Revenue. You can format values as currency, percentages, dates, or whole numbers as appropriate. Avoid putting a heading inside the selected calculation range unless it is deliberately part of the data-table structure; text in the list of trial values may be treated as an input and produce confusing results.

Changing the model’s formulas or assumptions can change every result in the table. If you need a fixed record of a particular analysis, copy the results and use Paste Special > Values in a separate area or workbook. That creates a snapshot rather than a live What-If Analysis table.

Common problems and how to fix them

  • The results are blank or unexpected: Confirm that the formula in the table’s top-right cell refers to the intended model output, and that the selected range includes the formula and all trial values.
  • The table repeats one result: Check that Column input cell points to the model input that actually drives the formula. If the output formula does not depend on that cell, substituting values will not change the answer.
  • Excel asks for an input cell or returns an error: Reopen Data Table and set Column input cell to a single valid input cell. For a one-column table, leave Row input cell empty.
  • Trial values appear to be ignored: Make sure they are numeric or otherwise the correct type for the model, and that they are in the first column of the selected range beneath the blank corner cell.
  • The table seems slow to update: Large data tables can require many recalculations, especially when the workbook contains complex formulas. On Windows, Formulas > Calculation Options > Automatic Except for Data Tables can prevent data tables from recalculating with every ordinary change; recalculate when you need updated results.

Data tables are calculated as a special What-If Analysis feature, so their result cells are not ordinary formulas that you can edit one by one. To change the scenarios, edit the trial-value list; to change the model connection, adjust the linked output formula or the input-cell selection.

One-column data table versus other Excel tools

A one-variable data table is best when you want to see how one model output changes across many possible values for one input. A two-variable data table can test combinations of two inputs, using trial values across a row and down a column. It is still a What-If Analysis tool, but the dialog uses both Row input cell and Column input cell.

Goal Seek solves a different problem: it finds an input value that makes a formula reach a target result. For example, it can find the price needed to reach a specified revenue. A data table instead evaluates the output for values you provide. Scenarios can store groups of assumptions, while a data table is often more convenient for a straightforward list of alternatives.

An ordinary Excel Table, created with Insert > Table or Ctrl+T on Windows, is a structured range for managing records. It supports features such as filtering and structured references, but it does not automatically substitute trial values into a model. These names are similar, but the features solve different tasks.

Version and platform notes

The steps above are for Excel for Microsoft 365 on Windows. Excel for Microsoft 365 for Mac also has What-If Analysis and Data Table tools, though the ribbon layout can differ slightly; look on the Data tab for the What-If Analysis command. Keyboard shortcuts for opening the dialog are not necessary, and menu labels are the more reliable route.

Excel for the web may let you view a workbook containing a data table, but the desktop Excel app is the safer choice for creating or editing What-If Analysis data tables. If the command is unavailable in a browser, open the workbook in desktop Excel rather than substituting a regular formula range and assuming it behaves the same way.

Frequently Asked Questions

How do I make a one-variable data table in Excel?

Enter trial values vertically, put a formula linked to the output above and to their right, select the full range, then choose Data > What-If Analysis > Data Table. Set Column input cell to the model input and leave Row input cell blank.

What goes in the Column input cell box?

Select the original worksheet cell whose value you want Excel to replace temporarily with each item in the vertical trial list. Do not select the list itself or the output formula cell.

Why is my Excel data table showing the same answer?

The model output may not depend on the selected input cell, or the dialog may point to the wrong cell. Confirm that changing the model input manually changes the output, then select that input cell in the Data Table dialog.

Can I use a one-column data table to test two inputs?

No. A one-column data table varies one input. To compare combinations of two inputs, arrange the possible values across a row and down a column, then use both Row input cell and Column input cell in the Data Table dialog.

Is an Excel data table the same as an Excel Table?

No. A What-If Analysis data table recalculates a model for trial inputs. An Excel Table is a structured range for organizing and analyzing records, with tools such as filters and column headings.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.