Scenario Manager vs Data Table in Excel: Which to Use

Scenario Manager vs data table comes down to one question: are you changing a few inputs together or one or two inputs across many values? Scenario Manager saves named sets of inputs, such as a best, base and worst case, and summarises them in a report. A data table runs one or two inputs through a list of values and fills a grid with the results. This guide compares the two on the same profit model so you can pick the right one for a budget, forecast or job test.

Scenario Manager vs data table at a glance#

Both tools live under Data > What-If Analysis in Excel for Windows and Mac, next to Goal Seek. They answer different what-if questions:

There is a full walkthrough of each tool in Scenario Manager in Excel and what-if analysis with a data table. This article focuses on choosing between them.

The example model#

Both tools need a model: input cells, and a formula that depends on them. This one works out profit for a product:

Profit is units × (price − variable cost) − fixed costs, so B7 shows 5,000. The same structure is the heart of a break-even analysis in Excel. Keep inputs and formulas separate like this: both what-if tools work by changing input cells and reading the formula.

The same model with Scenario Manager#

Scenario Manager stores sets of values for the changing cells B2:B4. You add each case under Data > What-If Analysis > Scenario Manager > Add:

Click Show and Excel types that scenario's values into B2:B4, so B7 recalculates. Click Summary, choose B7 as the result cell, and Excel builds a new sheet listing each scenario's inputs and profit side by side. That report is easy to paste into a slide or email.

Two cautions. The summary does not update later, so if fixed costs change you must create it again. And Show overwrites the changing cells with plain values, so never use cells that contain formulas as changing cells.

The same model with a data table#

A data table answers a different question: how does profit move as price changes, with everything else held at the base case? List test prices down a column, put a link to the result one row above and one column to the right, then select the block and choose Data > What-If Analysis > Data Table. Leave the row input empty and set the column input cell to B2:

Each 2 added to the price adds 2,000 to profit, because 1,000 units each earn 2 more. A two-variable data table puts prices down the side and units across the top, with the row input cell set to B3:

The grid stays live: change fixed costs in B5 and every cell updates. A data table's input cells must be on the same sheet as the table.

You can build the same grid with ordinary formulas and mixed references instead. With prices in A11:A13 and units in B10:D10, the formula =B$10*($A11-$B$4)-$B$5 fills across and down. The dollar sign guide to absolute references explains why B$10 locks the row and $A11 locks the column. A formula grid is easier to audit and edit, and it is what the exercise on this page practises.

When to use Scenario Manager#

Choose Scenario Manager when the inputs move together and the story matters more than the range:

• Best, base and worst cases for a budget or forecast, where price, volume and cost all shift at once.
• More than two inputs per case. A data table can't change three inputs; a scenario can change up to 32.
• Reports for other people. The summary sheet is tidy and needs no explanation.
• Comparing proposals from different people, each saved as a named scenario.

If you want a live switch rather than a static report, a scenario selector built with CHOOSE or INDEX does the same job with formulas. A cash flow forecast in Excel often uses one to flip between cases.

When to use a data table#

Choose a data table when you want to see how sensitive one result is to one or two inputs:

• Loan payments for every combination of rate and term.
• Profit across a price range, to see where it turns negative.
• Many values at once: twenty prices take the same effort as five.
• Results that must stay current as the model changes.

Keep data tables small in large workbooks. They recalculate whenever the sheet does, so many big tables can make Excel sluggish. Switching to Automatic except for data tables (Formulas > Calculation Options) helps; press F9 to update them when you need to.

Common mistakes with both tools#

These slips cause most wrong what-if results:

1. Using formula cells as scenario changing cells. Show replaces the formula with a value, and the model breaks quietly.
2. Forgetting the summary is static. An old Scenario Summary in a report may no longer match the model.
3. Swapping the row and column input cells. In a two-variable table, the values across the top go with the row input cell.
4. Putting the data table on another sheet. The input cells must be on the same sheet as the table.
5. Trying to delete one result cell. Excel says it can't change part of a data table. Select the whole result area first.
6. Hard-typed numbers in the model formula. If B7 says =B3*(22-B4)-B5, changing price in B2 does nothing, so neither tool works.

For the reverse question, "what price gives a profit of exactly 8,000?", use Goal Seek instead: it works backwards from the result you want to one input.

Our recommendation#

Use Scenario Manager to present a few named cases where several inputs change together, and a data table to explore one or two inputs across a full range of values. For most analysis work, start with a data table or a formula grid, because the results stay live. Reach for Scenario Manager when you need a clean best, base and worst case report, or when three or more inputs move at once.

In short: scenarios to tell a story, data tables to measure sensitivity.

Practise it#

Both tools depend on a model built with locked references. Try the exercise on this page, which works out one row of a price table by hand. Then practise absolute and relative references in a live spreadsheet and build the department budget practice sheet, where every total depends on clean input cells.