Excel’s What-If Analysis Data Table cannot vary three input cells in a single table: it supports one or two changing inputs at a time. To analyze three variables, hold the third variable at a chosen value and make a separate two-variable table for each value, or list all three inputs in ordinary worksheet columns and calculate each scenario with a formula.
What Excel means by a Data Table
A Data Table is a What-If Analysis tool that recalculates a formula for a range of trial input values. It is useful when you already have a model, such as a profit calculation, and want to see how its result changes as one or two assumptions vary. It is not the same thing as an Excel Table created with Insert > Table; the latter organizes data and offers features such as filter buttons and structured references.
The Data Table feature works by temporarily substituting values into selected input cells in your model, recalculating the result, and displaying each result beside its trial values. The model’s original input values are restored after the calculation. This makes a Data Table convenient for sensitivity analysis without having to edit and undo assumptions one at a time.
- A one-variable Data Table substitutes a list of values into one model input cell.
- A two-variable Data Table substitutes values from a row and a column into two model input cells.
- A three-variable analysis needs another method because the Data Table interface has only a row input cell and a column input cell.
Why one Data Table cannot vary three inputs
In a two-variable table, one set of trial values runs across the top row and the other runs down the first column. Excel needs one model cell for the row values and one model cell for the column values. The formula at the corner of the table supplies the result to calculate for each pair. There is no third input-cell field in the Data Table dialog.
This limitation concerns changing inputs, not the number of calculations in your workbook. Your model can have many assumptions and formulas, but a single Data Table can substitute trial values into no more than two input cells. Other assumptions remain at their current values during that table’s calculation. For a three-variable analysis, the practical question is how to represent the third variable: as separate slices, or as another column in a list of scenarios.
For example, a profit model might depend on selling price, units sold, and unit cost. A two-variable table can show how profit changes with price and units sold, while unit cost stays fixed. To see different unit costs as well, make another price-and-volume table for each cost level, or calculate each complete price-volume-cost combination in a flat scenario list.
Build a two-variable table for one slice
Here is a concrete setup. Put selling price in B2, units sold in B3, unit cost in B4, and fixed costs in B5. Enter 25 in B2, 1,000 in B3, 15 in B4, and 5,000 in B5. With those assumptions, the profit model returns 5,000: (25 − 15) × 1,000 − 5,000.
With price in B2, units in B3, unit cost in B4, and fixed costs in B5, put the model formula in B6:
=(B2-B4)*B3-B5
For the table, enter =B6 in D9. Put possible prices, such as 23, 25, and 27, across E9:G9. Put possible unit counts, such as 800, 1,000, and 1,200, down D10:D12. The formula cell D9 is at the upper-left corner of the trial-value grid; the price headings go across its row, and the unit headings go down its column.
- Select D9:G12, including the formula, both sets of trial values, and the empty result cells.
- On the Windows desktop, choose Data > What-If Analysis > Data Table.
- Set Row input cell to B2, because the trial values across the top are prices.
- Set Column input cell to B3, because the trial values down the side are units sold.
- Choose OK. Excel fills the result area with profit for each price-and-volume pair.
For instance, at a price of 27 and 1,200 units, with unit cost still 15 and fixed costs still 5,000, profit is 9,400: (27 − 15) × 1,200 − 5,000. That is the result at the intersection of the 27 price heading and the 1,200-unit heading. If your values appear in the wrong orientation, check whether the row and column input cells were assigned to the correct model assumptions.
Include the third variable with separate slices
To compare several unit costs, repeat the two-variable analysis at each cost level. For example, one slice could hold unit cost at 15, another at 17, and a third at 20. Each slice then answers the same question—how price and volume affect profit—under a different cost assumption. Label each slice clearly so a reader can tell which unit cost applies.
There are two sensible ways to manage the slices:
- Use separate model copies. Put each model and its corresponding two-variable table in a separate worksheet area or sheet. Give each copy its own input cells, so the tables can represent different third-variable values at the same time. Keep the model formulas consistent between copies.
- Calculate one slice at a time. Change the third input cell, calculate the two-variable table, and preserve that slice’s results before changing the input again. If you need a permanent comparison, copy the finished results and paste values into a clearly labeled summary area.
Do not expect several tables that share the same model input cells to preserve different third-variable settings simultaneously. A Data Table temporarily uses the input cells for its own calculations, and the shared model has only one current value for the third assumption. Separate model copies or saved result values avoid that ambiguity.
Use a scenario list for all three inputs
If you want every scenario in one sortable, filterable list, use ordinary worksheet formulas instead of the Data Table feature. Give each scenario one row and one column for each input. This method is especially straightforward when the number of combinations is manageable or when the combinations are specific cases rather than every possible pairing.
For example, place price in A2, units in B2, unit cost in C2, and fixed costs in F1. Enter 27 in A2, 1,200 in B2, 15 in C2, and 5,000 in F1. With those input values in place, put this profit formula in D2:
=(A2-C2)*B2-$F$1
The result is 9,400. Copy the formula in D2 down for additional scenario rows; the input references adjust to each row, while the fixed-cost reference remains anchored at F1. Add labels or a separate scenario ID column if useful. You can then use normal worksheet sorting, filters, charts, or conditional formatting to compare results. Unlike a Data Table, each row explicitly records all three assumptions that produced its output.
For a full grid, decide which values to test for each variable, form the combinations as rows, and calculate each row. A grid with 4 price choices, 5 volume choices, and 3 cost choices contains 60 combinations (4 × 5 × 3). This can be easier to audit as a flat list than as 3D-style blocks spread across a sheet. Avoid creating combinations you do not need; a very large scenario list can make recalculation and review less convenient.
Choose the right what-if method
The best approach depends on whether you need a visual sensitivity table, a set of named cases, or a comprehensive list of combinations.
- Two-variable Data Table: Choose this for a compact grid showing how one output responds to two inputs while other assumptions remain fixed. It is a good fit for a price-versus-volume matrix.
- Separate two-variable tables: Choose these when readers need to compare a few levels of a third assumption, such as low, expected, and high costs. Label each slice and keep the underlying model inputs clear.
- Scenario list with formulas: Choose this when every row needs to retain all input values, or when you want to filter, sort, chart, or export the results. It is often the most transparent format for many combinations.
- Scenario Manager: Choose this for a small collection of named cases rather than a full grid. In desktop Excel, open Data > What-If Analysis > Scenario Manager. A scenario can store values for multiple changing cells, but Scenario Manager is designed to save and compare selected cases, not automatically calculate every combination of three variables.
For Scenario Manager, Excel allows up to 32 changing cells in a scenario. That does not mean it will generate all possible combinations of those inputs. If the goal is to inspect every price, volume, and cost combination, a scenario list or separate Data Table slices is usually more direct.
Common problems and useful checks
- The results do not change. Check that the row and column input cells are the actual cells used by the model formula. A heading containing a price will not affect the output if the formula refers to a different price cell.
- Results are swapped or unexpected. The row input cell must correspond to the trial values across the top, and the column input cell must correspond to values down the side. Also check that the table’s corner cell links to the intended output formula.
- The third assumption seems to disappear. It is not an input to that Data Table. Its current value is held constant during the calculation. Create a separate slice or record the assumption explicitly in a scenario list.
- The workbook recalculates slowly. Data Tables can require many repeated model calculations, especially when the model is large or contains many volatile formulas. Start with a smaller set of trial values, and avoid duplicating unnecessary slices.
- The command is missing or unavailable. Use the desktop version of Excel for Microsoft 365 on Windows and look under Data > What-If Analysis. Excel for the web does not provide the desktop Data Table command for creating this analysis. On Mac, use the desktop Excel ribbon’s Data tab and its What-If Analysis options; the exact dialog presentation can differ slightly by version.
It is also worth distinguishing a Data Table from a PivotTable. A PivotTable summarizes existing records; it does not substitute hypothetical assumptions into a model formula. A What-If Analysis Data Table does perform those substitutions, but only for one or two input cells per table. Choosing the right feature prevents a common misconception: arranging three headings on a worksheet does not make Excel’s Data Table tool vary three model inputs.
Frequently Asked Questions
Can I make a three-variable Data Table in Excel?
No. Excel’s What-If Analysis Data Table supports one or two changing input cells. For three variables, use multiple two-variable tables with the third variable fixed at a different value in each slice, or use a formula-driven list of scenarios.
How do I change the third variable in a two-variable table?
Change the third model input before calculating a new slice. For simultaneous comparisons, use separate copies of the model with separate input cells, or save each calculated result set as values and label it with the third variable’s setting.
Can I put three variables in the row and column headings?
Not in a single standard Data Table. It has one row of trial values and one column of trial values, which map to two input cells. You can arrange multiple tables or a scenario list to show the third variable, but that does not add a third input to the Data Table feature.
Is an Excel Data Table the same as an Excel Table?
No. A What-If Analysis Data Table recalculates a formula using trial input values. An Excel Table, created with Insert > Table, organizes a range of records and adds features such as filters. They serve different purposes.
Can Excel for the web create a What-If Analysis Data Table?
The desktop Data Table command is not available for creating this analysis in Excel for the web. Open the workbook in desktop Excel to build or manage the what-if table.
Bottom line
A single Excel What-If Analysis Data Table cannot vary three variables. Use it for two inputs at a time, then represent the third variable with separate, clearly labeled slices—or use a scenario list where every row contains all three inputs and a calculated result. The latter is often easier to audit when you need a complete comparison rather than a compact sensitivity grid.
Related
- Edit Data Validation Restrictions in Excel
- Excel Data Validation Restrictions Explained
- Excel Data Validation: Create Rules, Lists, and Input Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Number Data Validation ExplainedYes. Google Sheets data validation can require a cell to contain a number that meets a rule, such…
- How to Freeze One Row in Excel: Keep Headers VisibleTo freeze one row in Excel, select View > Freeze Panes > Freeze Top Row. Excel keeps row…
- 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…
- Excel Document Not Responding? Causes and Safe FixesIf an Excel document is not responding, wait briefly before closing it: Excel may be calculating, loading data,…
- Google Sheets COUNTIF: Exclude Subtotal Rows CorrectlyTo keep subtotal rows out of a Google Sheets count, use COUNTIFS with a condition that rejects the…
- Excel Table Not Adding New Rows? Causes and SolutionsExcel tables usually add rows when you type directly in the first row beneath them, or press Tab…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.