Build an Excel Data Table With One or Two Variables
Create a one- or two-variable Excel Data Table, check the results, and turn the calculation grid into a clean dataset for public sharing.
Use Excel’s Data Table command when you want to calculate the same formula for several possible inputs. For example, you can compare projected revenue at different sales quantities, then publish the results as a table readers can explore.
First, choose the right kind of table:
- Excel Table: a structured list of records created with Home → Format as Table. Use this to organize an existing dataset. Microsoft’s table instructions explain how to select the range and identify its headers.
- What-If Analysis Data Table: a calculation grid that substitutes different values into one or two input cells. This is the tool used below. A one-variable table can evaluate multiple formulas; a two-variable table evaluates one formula across combinations of two inputs. Microsoft documents these distinctions.
Create a one-variable Data Table
This illustrative example uses desktop Excel for Windows. It calculates gross revenue as units sold × price per unit. It does not account for costs, taxes, discounts or returns.
1. Enter the model
On a blank worksheet, enter:
| Cell | Enter | Meaning |
|---|---|---|
| A2 | Units sold | Input label |
| B2 | 100 | Starting quantity |
| A3 | Price per unit (USD) | Input label |
| B3 | 12 | Fixed unit price |
| A4 | Gross revenue (USD) | Output label |
| B4 | =B2*B3 |
Revenue formula |
B4 should show 1200. Keep the model and the calculation grid on this worksheet for the example.
2. Lay out the trial quantities
Enter =$B$4 in E2. Leave D2 blank. Enter the trial quantities in D3:D5:
| Row | Column D | Column E |
|---|---|---|
| 2 | Leave blank | =$B$4 |
| 3 | 50 | Leave blank |
| 4 | 100 | Leave blank |
| 5 | 150 | Leave blank |
The output reference belongs one row above and one column to the right of the first trial value. This is the column-oriented layout in Microsoft’s one-variable workflow.
3. Run the calculation
- Select D2:E5, including the output reference, trial values and empty result cells.
- Choose Data → What-If Analysis → Data Table.
- Leave Row input cell empty.
- Set Column input cell to
$B$2. - Select OK.
Column input cell means the model cell Excel will replace with each value from the trial column. It is B2—not D3, where the first trial value sits.
E3:E5 should contain 600, 1200 and 1800. Check the endpoints directly: 50 × 12 = 600 and 150 × 12 = 1800.
Extend it to two variables
To compare both quantities and prices, keep the same model and build a second grid:
- G2:
=$B$4 - H2:J2: prices
10,12,15 - G3:G5: quantities
50,100,150 - H3:J5: leave empty for results
Select G2:J5, open Data → What-If Analysis → Data Table, and set:
| Dialog field | Model cell | Why |
|---|---|---|
| Row input cell | $B$3 |
Prices run across the top row |
| Column input cell | $B$2 |
Quantities run down the first column |
Select OK. This follows Microsoft’s two-variable layout. The expected result grid is:
| Units sold | Price: 10 USD | Price: 12 USD | Price: 15 USD |
|---|---|---|---|
| 50 | 500 | 600 | 750 |
| 100 | 1000 | 1200 | 1500 |
| 150 | 1500 | 1800 | 2250 |
If results repeat unexpectedly, check that the output formula depends on the input cell you selected. If results look transposed, check the row and column input assignments. If they appear stale, select the Data Table’s result formulas and press F9 to recalculate them; Microsoft documents this for tables excluded from automatic recalculation in its Data Table guidance.
Prepare the results for a public page
Keep the calculation workbook as your working file. Create a separate release workbook containing only the intended public data.
For the one-variable example, use three explicit columns:
| units_sold | price_usd_per_unit | gross_revenue_usd |
|---|---|---|
| 50 | 12 | 600 |
| 100 | 12 | 1200 |
| 150 | 12 | 1800 |
Enter these headers in A1:C1 of the release sheet. Copy the working sheet’s D3:D5 into A2:A4, and E3:E5 into C2:C4, using Home → Paste → Values for each paste. Enter 12 in B2:B4. Values pasting preserves formula results rather than the formulas themselves, as described in Microsoft’s paste options.
For the two-variable grid, use the same columns with one row per quantity–price combination: nine rows in total. This makes both inputs available as fields rather than burying prices in column headings. Transfer the outputs as values and verify every input–output pairing after rearranging the grid.
You can save the release sheet as CSV or use the clean XLSX workbook. CSV saves the active worksheet’s displayed text and values, not its workbook formatting or interactive calculation model. See CSV vs XLSX for the format trade-offs and Microsoft’s export reference for what CSV retains.
TablePage accepts CSV, TSV, XLSX and XLS files and turns an upload into a public dataset page with a filterable table. Upload only the sanitized release file—never sensitive information—and check the published row count, numeric values and filters before sharing its link.
Alongside the page, label this dataset illustrative scenario results, state the formula and currency, and explain the excluded costs. These are modeled outcomes, not observed sales. The published values are a snapshot, not an Excel what-if calculator.