What-If Analysis in Excel: A Practical Guide

What-If Analysis helps you ask a useful question of an Excel formula: what happens to the result when an assumption changes? You can compare a few named plans, test a long list of input values, or work backward from a target result.

The tool depends on the question. Scenario Manager stores sets of assumptions. A Data Table shows many one- or two-input results together. Goal Seek finds one input that gets a formula close to a target. This guide uses one small workshop model to show where each fits.

The menu paths below describe desktop Excel. If you are using Excel for the web and do not see a What-If Analysis command, open the workbook in a desktop version of Excel.

Choose the tool from the question

All three tools depend on a working worksheet model. They do not judge whether your assumptions are realistic. Microsoft’s What-If Analysis overview explains the distinction between scenarios, Data Tables, and Goal Seek.

Build a model with separate inputs and one result

Imagine a paid workshop. You control the ticket price, but attendance is uncertain. There is a per-attendee cost and a fixed venue cost. Enter these labels in column A and values or formulas in column B:

A simple Excel model with separate input cells and a profit formula creates the foundation for What-If Analysis tools like Scenario Manager, Goal Seek, and Data Table.

With 30 attendees, the model gives 1,350: each ticket contributes 95 after the per-attendee cost, and the venue costs 1,500. Keep the assumptions in input cells and the profit in a formula cell. If you type a fixed number into B6, Excel has no formula to recalculate when an input changes.

Check the model before comparing plans: changing B2 from 30 to 20 while leaving the other inputs alone should change profit from 1,350 to 400. Restore B2 to 30 before continuing.

Save three plans with Scenario Manager

Scenario Manager is useful when several inputs change together and you want to revisit each combination. Here, attendees and ticket price vary; the two cost assumptions stay fixed.

1. Choose Data > What-If Analysis > Scenario Manager.
Scenario Manager opens as an empty list at first. From here, you can add named scenarios and define which input cells should change together.

2. Select Add, name the first scenario Cautious, and set Changing cells to $B$2:$B$3.
In the Add Scenario dialog, name the scenario and specify the input cells that should change together. Here, the Cautious scenario uses cells B2 and B3.

3. Select OK. Enter 20 for B2 and 100 for B3, then select OK.
Enter 20 attendees and a ticket price of 100 to define the Cautious scenario in Excel Scenario Manager.

4. Add Base with values 30 and 120. Add Stretch with 40 and 140.
Scenario Manager stores three workshop plans, allowing you to switch between Cautious, Base, and Stretch assumptions.

5. Select a scenario and choose Show to put its saved inputs into B2 and B3. Check that B6 matches the corresponding profit above.

The changing cells must be inputs, not the profit formula in B6. Showing a scenario replaces the current values in B2 and B3 with that scenario’s values. It does not create a separate live copy of the model.

To compare the plans on one sheet, select Summary in Scenario Manager, choose Scenario summary, and set Result cells to $B$6. Excel creates a report on a new worksheet. If you later edit a scenario, create a new summary: existing scenario reports do not update automatically. Microsoft documents this behavior in its Scenario Manager guide.

The Scenario Summary report compares attendees, ticket prices, and calculated profits across three workshop scenarios.

Find an attendance target with Goal Seek

Suppose the workshop must earn at least 2,000 in profit, and ticket price remains 120. That is a reverse question: you know the desired result but not the attendance required. First show the Base scenario so B3 is 120 and the other inputs match the original model.

1. Choose Data > What-If Analysis > Goal Seek.
2. Set Set cell to $B$6, the profit formula.
3. Enter 2000 in To value.
4. Set By changing cell to $B$2, the attendee input.
5. Select OK, inspect the proposed solution, and keep it only if it suits the model.

The mathematical answer is about 36.84 attendees. Real attendees come in whole numbers, so round up to 37 and check the formula again: profit becomes 2,015. Rounding down to 36 would produce 1,920 and miss the target. Goal Seek solves the worksheet equation approximately; it does not enforce real-world requirements such as whole attendees or venue capacity.

Goal Seek changes only one input cell. If several inputs must be chosen together under constraints, the Solver add-in is the more suitable tool. Microsoft’s Goal Seek instructions explain the three dialog fields and the one-input limitation.

Use a Data Table for a range of inputs

Scenario Manager is less convenient if you want to scan many values of just one input. A one-variable Data Table can show the profit for a list of attendee counts while holding the other assumptions constant. With price 120, cost per attendee 25, and fixed cost 1,500, the results are:

A two-variable Data Table can cross attendee counts with ticket prices and display the result for every combination. It is a what-if calculation grid, not the Insert > Table feature used to organize a list of records. The exact row-input and column-input layout matters, so follow Microsoft’s Data Table instructions when building the grid. Use Scenario Manager instead when you need named plans with several inputs changing together.

Interpret the result before acting

A what-if output is only as credible as the assumptions feeding it. The workshop model assumes every attendee pays the stated ticket price, the per-attendee cost stays at 25, and the venue cost does not change with attendance. A discount, capacity limit, refund, or step-up in staffing cost would require a better formula or additional inputs.

• Check the units: ticket price and costs must use the same currency, while attendees are a count.
• Check the sign of each cost in the formula so expenses reduce, rather than increase, profit.
• Check any result against a hand calculation or a simple known case.
• After Goal Seek, inspect the input cell it changed and restore a named scenario if you need the original assumptions back.
• Regenerate a Scenario Summary after editing saved scenario values.

Start with a question the model can answer

Use Scenario Manager for a few named combinations, a Data Table for a range or grid of values, and Goal Seek for one unknown input behind a target. Then examine the result in the context of the actual decision. Excel can calculate what the model says; the useful judgment is whether the model describes the situation well enough.