Microsoft Excel · Analysis and charts · Intermediate
Compare one- and two-input models with What-If data tables in Excel
Build one- and two-variable What-If data tables, map row and column inputs correctly, and reconcile every output.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Put a reference to your model result at the corner of a What-If data table, arrange trial values beside it, then choose Data > What-If Analysis > Data Table. Row input and column input refer to the model cells being substituted, not to the displayed trial ranges.
Prepare a two-input model
Use English Excel for Microsoft 365 desktop on Windows and a blank worksheet named Model. This is a What-If analysis data table, not an Excel table made with Insert > Table. Keep the model and both trial grids on this worksheet.
Enter these cells:
| Cell | Entry |
|---|---|
| A2 | Units |
| B2 | 10 |
| A3 | Price |
| B3 | 3 |
| A4 | Revenue |
| B4 | =B2*B3 |
B4 must be 30. These are synthetic arithmetic units, not pricing or investment advice. Before automating scenarios, manually change B2 to 5: B4 must become 15. Restore B2 to 10. If that simple check fails, correct the formula before constructing data tables.
Vary one input down a column
- Leave D2 blank and put
=B4in E2. - Enter 5, 10, and 15 in D3:D5. These are trial Units values.
- Select D2:E5, including the formula row and all trial values.
- Choose Data > What-If Analysis > Data Table.
- Leave Row input cell empty. Set Column input cell to
$B$2, because the trial values run down a column and replace the Units input. - Confirm and check E3:E5: the results must be 15, 30, and 45.
The grid evaluates the same result formula for each trial value. It does not permanently replace the base input B2 with each displayed number. Keep B2=10 and B3=3 as the starting case; B4 remains 30.
Vary two inputs together
Put =B4 in G2, trial prices 2 and 3 in H2:I2, and trial unit counts 5 and 10 in G3:G4. Select G2:I4 and open Data Table again.
Set Row input cell to $B$3: the values across the top row are Prices. Set Column input cell to $B$2: the values down the first column are Units. Confirm.
| Units / Price | 2 | 3 |
|---|---|---|
| 5 | 10 | 15 |
| 10 | 20 | 30 |
Each intersection is independent multiplication. H3=5×2, I3=5×3, H4=10×2, and I4=10×3. A two-variable table evaluates one result formula with two substituted inputs; it does not support an unlimited list of varying model inputs.
Change an assumption and check recalculation
Change B3 from 3 to 4. The one-variable table now returns 20, 40, and 60 because it varies only Units and uses the current base Price. The two-variable table still returns 10,15,20,30 because its own trial prices remain 2 and 3. This contrast is expected, not a refresh failure.
Now change I2 from 3 to 4. I3 and I4 become 20 and 40. Save as XLSX and reopen; check the base formula and both grids rather than merely looking for populated cells.
If results seem stale, inspect calculation mode. See calculation mode inherited from the first workbook for the relevant session-wide trap. If results are misplaced, rebuild the grid after checking its row/column mapping. Do not type corrective constants over the calculated result area.