Yes. In Excel, a one-variable Data Table can return multiple outputs by placing a different formula above each output column. A two-variable Data Table accepts only one formula in its top-left corner, so to compare multiple outputs across two changing inputs, create a separate table for each output or use another analysis method.
What “multiple outputs” means in an Excel Data Table
An Excel What-If Analysis Data Table substitutes trial values into one or two input cells in a model, then records the resulting calculation. It is useful when you want to compare many possible input values without changing the model manually. For example, a pricing model might calculate revenue and profit for several possible prices.
“Data Table” has a specific meaning here. It is not the same as an Excel table created with Insert > Table, which organizes a list of records and can have filter buttons. A What-If Analysis Data Table is a calculation tool that uses one or two model inputs. It does not store a general collection of scenarios or replace the formulas in your model.
The number of outputs you can display depends on the table type:
- One-variable table: can show multiple outputs side by side, with one output formula at the top of each result column.
- Two-variable table: shows one output for each combination of two input values. Make another table for each additional output.
How What-If Analysis Data Tables work
A Data Table temporarily supplies values to input cell or cells in an existing model. Excel recalculates the model for each value or combination, then places the chosen formula result in the table. The input cell must be part of the model: if changing it does not affect an output formula, the table will not show a meaningful change.
In a one-variable table, values go down the first column or across the first row. You identify the corresponding model input cell as the column input cell or row input cell. To show multiple outputs, put a formula linked to each desired result along the edge opposite the trial values. Excel evaluates each formula for every trial value.
A two-variable table uses values across the top and down the first column. Its top-left corner contains the one formula whose result you want to examine. Excel substitutes the top-row values into one model input cell and the first-column values into another. The body of the grid reports that formula’s result for each pairing. The corner is not a row of output formulas, which is why the same grid cannot directly display several independent outputs.
Example: multiple outputs with one changing input
Suppose a simple model has price in B2, units sold in B3, variable cost per unit in B4, and fixed costs in B5. Its outputs are revenue and profit. With those inputs in place, use these formulas in the model:
With price in B2 and units in B3, enter this in B7 for revenue: =B2*B3.
With price in B2, units in B3, variable cost in B4, and fixed costs in B5, enter this in B8 for profit: =(B2-B4)*B3-B5.
For a one-variable table that tests different prices, put trial prices in C3:C7. Put a reference to revenue in D2 and a reference to profit in E2. The layout is:
- C2: leave blank; this is the corner above the trial values.
- D2: with revenue calculated in B7, enter
=B7. - E2: with profit calculated in B8, enter
=B8. - C3:C7: enter the prices to test.
Select C2:E7, then choose Data > What-If Analysis > Data Table. Leave Row input cell blank and set Column input cell to B2, because the trial prices are arranged vertically and should replace the model’s price input. Select OK. Excel fills D3:E7 with revenue and profit for each price. For example, if B3 is 100 units, B4 is $6, B5 is $200, and a trial price is $10, that row returns $1,000 revenue and $200 profit: (10 − 6) × 100 − 200 = 200.
For a horizontal list of trial values, place the values in a row and use the Row input cell instead. The orientation of the inputs in the grid determines which input-cell box to fill; it does not depend on whether the model input itself is on a row or column.
Why two-variable tables need separate output grids
Suppose you want to vary both price and unit volume and inspect revenue and profit. Put trial prices across the top of a grid and trial unit counts down its first column. Put a reference to the revenue result in the upper-left corner, select the whole grid, and open Data > What-If Analysis > Data Table. Set Row input cell to the model’s price cell B2 and Column input cell to the units cell B3. Excel then calculates revenue for every price-and-volume pairing.
To show profit for the same pairings, duplicate the grid and replace its corner formula with a reference to B8, the profit result. Use the same row and column input cells. Each grid now reports one measure, and both grids can use identical scenario values. Label them clearly and keep their input-value rows and columns aligned so it is easy to compare corresponding scenarios.
Do not put several formulas beside one another in the corner of a two-variable table and expect Excel to create extra result layers. The two-variable Data Table is designed around one result formula. Separate grids are the straightforward built-in approach when both inputs and multiple outputs matter.
Build and check the table in Excel for Microsoft 365
On Excel for Microsoft 365 for Windows, set up the model first, then arrange the trial values and output formula references in the required layout. Select the complete table range, including the formula cell or cells and all trial values. Choose Data > What-If Analysis > Data Table, enter the appropriate input-cell references, and select OK. For a one-variable table, fill only the row or column input field that matches how the trial values are arranged. For a two-variable table, fill both fields.
Check the first result manually by typing one trial value into the model input and comparing the model output with the corresponding table result. Then restore the original input value if needed. Also check that the formula references at the edge of the table point to the intended model outputs; a reference to an intermediate calculation can be valid, but may not be the measure you meant to compare.
Excel for Mac has What-If Analysis Data Tables in the desktop application, though ribbon placement can differ with the version and layout. Excel for the web does not support creating or calculating What-If Analysis Data Tables in the same way as desktop Excel. Use the desktop app to build or recalculate them, especially before relying on updated results.
Common problems and practical limits
- No change in results: verify that the selected input cell is the cell used by the model formulas. A table cannot test an input that has no effect on the output.
- Wrong orientation: values across the top call for a Row input cell; values down the first column call for a Column input cell. In a two-variable grid, assign each model input to the corresponding orientation.
- Unexpected blanks or errors: inspect the model formulas and the top-row or corner references. Errors in the model can flow into the table, and a formula pointing at a blank or incorrect output cell will not produce the intended measure.
- Slow recalculation: a large two-variable grid can require many model recalculations. A grid with 40 values across and 30 down has 1,200 combinations per output; separate output tables add more calculations. Start with a smaller grid when developing the model.
- Results seem stale: confirm that Excel is recalculating and that you are using desktop Excel. Data Tables are a special calculation feature, and workbook calculation settings or opening the file in an app that does not support them can affect when results update.
Data Tables are best for a manageable set of scenarios in a model that already works. They are less convenient when you need a large, reusable scenario database, charts fed by structured records, or many different combinations of inputs. Keep the model inputs and output formulas easy to identify, and document the units and assumptions beside the grid. A result is only as meaningful as the assumptions built into the model.
Alternatives when you need more flexibility
For a small number of scenarios, you can enter each scenario’s inputs in separate rows and calculate the outputs with ordinary formulas. This makes each scenario an explicit record, which is often easier to filter, chart, or share. It does require formulas that refer to each row’s inputs rather than relying on the Data Table feature.
For a larger scenario list, a structured input table with formulas, Power Query, or a carefully designed model may be a better fit. The right choice depends on whether you need a quick sensitivity grid, a repeatable record of scenarios, or an automated process. Excel’s Scenario Manager is another What-If Analysis tool, but it is aimed at saving and switching between named sets of inputs; it does not turn one two-variable Data Table into a multi-output grid.
When comparing several outcomes, separate two-variable tables are usually easiest to audit. If you choose another method, preserve the same input assumptions across outputs and make the relationship between each scenario and its results visible. That reduces the risk of comparing profit under one set of assumptions with revenue under another.
Frequently Asked Questions
Can an Excel Data Table show multiple outputs?
Yes, if it is a one-variable Data Table: put a formula for each output in a separate column or row along the table’s edge. A two-variable Data Table has one output formula, so make a separate grid for each additional output.
How do I put two outputs in a one-variable Data Table?
Place references to the two model outputs in adjacent cells above the trial values, select the entire layout, and choose Data > What-If Analysis > Data Table. Set the row or column input cell according to the direction of the trial values.
Can one two-variable Data Table show revenue and profit?
Not as two independent outputs in the same standard two-variable table. Create two grids with the same input values and input-cell settings, using revenue as the corner formula in one and profit as the corner formula in the other.
What is the difference between a Data Table and an Excel table?
A What-If Analysis Data Table recalculates a model for trial input values. An Excel table, created with Insert > Table, organizes a dataset and provides features such as filters and structured references. They are different tools despite sharing a name.
Can I create a What-If Analysis Data Table in Excel for the web?
Excel for the web does not provide the same support for creating and calculating these Data Tables as the desktop application. Use Excel for Microsoft 365 on Windows or the supported desktop version on Mac to create and recalculate the analysis.
Related
- Entire Excel Sheet Not Responding? Fixes That Work
- How to Edit a PivotTable in Excel
- Edit a Slicer in Excel: Change Its Style, Size, and Settings
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Validation From a Table in Excel: Setup and FixesTo use an Excel table as the source for a drop-down list, create a workbook-level name that refers…
- Find an Excel Password in a Protected File? Safe OptionsYou usually cannot find or reveal a password from inside a protected Excel file: Excel does not display…
- Excel Flash Fill and Conditional Formatting ExplainedExcel Flash Fill and conditional formatting do different jobs: Flash Fill recognizes a pattern and writes values into…
- Create a Mailing List in Outlook: Steps & TipsTo create a mailing list in Outlook, make a contact list in the People section and add the…
- Remove Table Formatting in Word Without Losing DataTo remove a table’s style in Microsoft Word without deleting its contents, click inside the table, open Table…
- Protect a Sheet in Excel: Steps, Options, and LimitsIn Excel for Microsoft 365 on Windows, protect a sheet by opening the worksheet, choosing Review > Protect…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.