Scenario Manager and Data Tables in Excel

How-to basics · Updated November 21, 2026

Scenario Manager and Data Tables are Excel’s What-If Analysis tools for seeing how different inputs affect a formula. Use Scenario Manager to save and compare named sets of assumptions; use a data table to calculate an output across a grid of one or two changing inputs.

What What-If Analysis does

A spreadsheet model usually turns inputs—such as price, volume, hours, or costs—into a result. What-If Analysis helps you explore how that result changes when you vary the inputs, without rebuilding the model for every possibility. It is useful for planning, forecasting, and checking sensitivity, but it does not decide which assumptions are realistic or predict what will happen.

In Microsoft Excel for Microsoft 365 on Windows, both tools are on the Data tab under Data > What-If Analysis. Scenario Manager opens a dialog for saving and displaying scenarios. Data Table creates a calculation grid inside the worksheet. These are different from ordinary Excel tables, which organize data in rows and columns and can have filter buttons.

The tools work with formulas already in the workbook. A useful model has clearly identified input cells and result cells, with formulas connecting them. If a formula does not depend on the input cells you plan to change, neither tool can show a meaningful effect.

How Scenario Manager works

Scenario Manager stores sets of values for selected changing cells. For example, a small business model could use cells for unit price, units sold, and fixed costs. You might save a “Lower demand” scenario with fewer units sold, a “Higher costs” scenario with larger costs, and a “Target” scenario with the assumptions needed to reach a goal. The model’s formulas calculate the results using whichever scenario is currently displayed.

To set one up on Windows:

  1. Build the worksheet model and identify the input cells to vary and the result cells to review.
  2. Choose Data > What-If Analysis > Scenario Manager, then select Add.
  3. Give the scenario a clear name, select its changing cells, and enter the values for that scenario.
  4. Add other scenarios using the same changing cells, then select a scenario and choose Show to put its values into the model.
  5. Choose Summary to create a report that compares selected result cells across scenarios.

Scenario Manager supports up to 32 changing cells in a scenario. Those cells can represent several assumptions at once, so each scenario can be a coherent package rather than a single-input change. The summary report is useful when you want to compare a handful of named cases in one view. If you change the model or its scenarios later, create a fresh summary when you need a report reflecting the updated results.

How a data table works

A data table calculates a formula repeatedly while substituting different values into one or two input cells. A one-variable table varies one input and lists the resulting output for each value. A two-variable table varies two inputs, showing the results at their intersections in a grid. The table itself is a What-If Analysis feature, not a set of ordinary formulas copied down or across.

For a one-variable table, put the trial values in a row or column and place a reference to the output formula beside them: above the values for a vertical list, or to the left of values arranged horizontally. Select the entire range, including the formula reference and the trial values, then choose Data > What-If Analysis > Data Table. Enter the model input cell in the dialog’s Column input cell box for a vertical list, or Row input cell box for a horizontal row.

For a two-variable table, put the output formula in the top-left corner of the grid, trial values for one input across the top row, and trial values for the other input down the first column. Select the complete rectangle and open Data Table. Set Row input cell to the model input represented by the top-row values, and Column input cell to the input represented by the first-column values. Excel evaluates the formula for each pair. Both input cells must be referenced by the model’s calculation for the results to be useful.

Worked example: compare price assumptions

Suppose a simple model estimates profit before tax from price, units sold, and fixed costs. With price in B2, units in B3, and fixed costs in B4, enter the following in B5:

With price in B2, units in B3, fixed costs in B4, and profit in B5, the formula is =B2*B3-B4. If B2 is 25, B3 is 100, and B4 is 1500, the result is 1000 because 25 × 100 − 1500 = 1000.

To test several prices while holding volume and costs constant, put =B5 in B8, then enter 20, 22, 24, 26, and 28 in A9:A13. Select A8:B13 and open the Data Table dialog. Set the Column input cell to B2, because the trial values are in a column and represent the price input. Excel fills the result cells in column B: 500, 700, 900, 1100, and 1300. These results use 100 units and 1500 in fixed costs for every trial.

For a two-variable version, put several candidate prices across the top and candidate sales volumes down the side, with =B5 in the corner above and to the left of the grid. Set the row input cell to B2 and the column input cell to B3. The resulting grid shows how profit changes for each price-and-volume combination. This can reveal interactions—for example, a lower price may be offset by a higher volume—rather than showing the effect of price in isolation.

Choosing the right tool

Choose based on the question you want to answer, not just the size of the worksheet:

  • Use Scenario Manager when you have a few named combinations of assumptions, such as conservative, expected, and optimistic cases, and want to switch between them or produce a comparison summary.
  • Use a one-variable data table when you want to see how one output responds to many possible values of one input.
  • Use a two-variable data table when you want to compare combinations of two inputs in a compact sensitivity grid.
  • Use ordinary formulas or a separate analysis sheet when the model needs more than two varying inputs, custom calculations for each case, or a large automated experiment.

These methods can complement each other. Scenario Manager can hold a few broader cases, while a data table explores a range within one case. For example, you could compare three cost plans with scenarios, then use a data table to examine how different prices and sales volumes affect profit under the selected plan.

Limits and practical considerations

A data table is best for a manageable sensitivity grid, not an unlimited list of cases. A two-variable table with many row and column values can require Excel to recalculate the model many times, which may slow a complex workbook. Start with a modest range, and avoid making a grid much larger than the decision requires. Data tables also have a specialized calculation behavior, so they are not as flexible as a normal range of formulas for sorting, editing individual calculations, or feeding into other analysis.

Scenario Manager is better for a small set of meaningful cases than for thousands of combinations. Its changing-cell limit is 32, and each named scenario needs to be entered and maintained. A Scenario Summary report is a comparison aid, not a forecast or a guarantee; label assumptions and units clearly so readers understand what each case represents.

In Excel for Microsoft 365 on Mac, the tools are available in the desktop application, though ribbon layout and dialog presentation can differ from Windows. Excel for the web does not provide the full desktop What-If Analysis workflow for creating or managing these tools, so use the desktop app when you need to build or change scenarios and data tables. If a workbook is shared, check that collaborators can open it in a compatible desktop version.

Common problems and fixes

  • The table shows repeated or unexpected results: Confirm the output formula refers to the intended result cell and that the chosen input cell is the same cell used by the model. For a vertical list, use the Column input cell box; for a horizontal list, use Row input cell.
  • A two-variable table is blank or misleading: Check that the output formula is in the top-left corner, the trial values are in the top row and first column, and the row and column input cells are assigned to the corresponding model inputs.
  • Scenario Manager changes the wrong assumptions: Reopen the scenario and verify its changing-cell references. Use labels beside input cells and give scenarios descriptive names so you can distinguish assumptions later.
  • Results do not update as expected: Check that the model formulas actually depend on the changed inputs. Also review Excel’s calculation settings if the workbook is set to manual calculation; recalculate the workbook when needed.
  • The report is hard to interpret: Select concise result cells with clear labels and use consistent units. A report with dozens of unexplained outputs can be less useful than a short comparison of the key measures.

Alternatives for larger analyses

If you need to compare many records or apply different assumptions row by row, a regular worksheet table with formulas may be easier to audit than a What-If Data Table. For repeatable data preparation, Power Query can help reshape imported information, while PivotTables can summarize records; neither is a direct replacement for substituting inputs into one model formula. For goal-seeking questions—such as finding the price needed to reach a target profit—Excel’s Goal Seek feature, under Data > What-If Analysis > Goal Seek, works in the opposite direction: it changes one input to reach a specified formula result. Choose the feature that matches the question instead of treating all What-If tools as interchangeable.

Frequently Asked Questions

Are Scenario Manager and Data Table the same thing?
No. Scenario Manager saves named sets of input values and lets you switch among them. A data table calculates an output for a grid of trial values in one or two input cells.

Can an Excel data table use more than two input variables?
No. Excel’s What-If Analysis Data Table uses one or two input cells. To study more variables, use separate scenarios, build a larger analysis with regular formulas, or use an appropriate data-analysis workflow.

Where is Scenario Manager in Excel?
In Excel for Microsoft 365 on Windows, choose Data > What-If Analysis > Scenario Manager. The desktop Mac version has the corresponding What-If Analysis tools, though their ribbon placement can vary.

Why is Data Table unavailable or missing in Excel for the web?
The web app does not offer the full desktop What-If Analysis tools for creating and managing data tables. Open the workbook in the Excel desktop application to set up or edit one.

Can I use both tools in one workbook?
Yes. Scenario Manager can compare named overall cases, while a data table can examine how one or two inputs affect a result within a case. Keep the assumptions and output labels clear to avoid confusing the two analyses.

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.