What-If Analysis in Excel: Scenarios, Goal Seek & More

In Excel for Microsoft 365 on Windows, what-if analysis helps you see how changing input values affects a formula’s result. Open Data > Forecast > What-If Analysis to use Scenario Manager, Goal Seek, or Data Table; choose a tool based on whether you want to compare sets of assumptions, find one input for a target result, or test many possible inputs.

What what-if analysis means

A spreadsheet model connects inputs, formulas, and outputs. What-if analysis changes one or more inputs so you can observe how the outputs respond, without having to rebuild the model for each possibility. For example, a shop might examine how profit changes with different sales volumes, prices, costs, or rent assumptions.

The analysis is only as useful as the model underneath it. Excel does not know whether an assumption is realistic, whether a formula reflects a real-world relationship, or which outcome is most likely. It calculates the results implied by the values and formulas in the workbook. Treat the output as a way to explore assumptions, not as a prediction or a guarantee.

Excel’s built-in What-If Analysis menu contains three distinct tools:

Scenario Manager saves and compares named sets of input values.
Goal Seek works backward from a target result to find a value for one input.
Data Table calculates results for a range of values for one or two inputs.

Build a model before testing assumptions

Put assumptions in separate cells and make output cells depend on them through formulas. This separation makes it easier to see what you are changing and helps avoid accidentally replacing a formula with a typed value. Add clear labels and, where useful, units such as dollars, units sold, or percentage points.

For a simple business example, assume units sold are in B2, selling price per unit is in B3, variable cost per unit is in B4, fixed costs are in B5, and profit is calculated in B6. With those inputs in place, enter this formula in B6:

With units in B2, price in B3, variable cost in B4, and fixed costs in B5, the formula in B6 is =B2*(B3-B4)-B5. If B2 is 1,000, B3 is 25, B4 is 15, and B5 is 8,000, the result is 2,000: 1,000 units produce 10 of contribution per unit, or 10,000, less 8,000 in fixed costs.

This small model is enough to demonstrate the tools, but a real model may have multiple linked outputs. Check that formulas refer to the intended input cells and that units are consistent before drawing conclusions from a what-if result.

Compare several cases with Scenario Manager

Use Scenario Manager when you want to save and revisit named combinations of inputs, such as a lower-sales case, a central estimate, and a higher-sales case. It can change multiple cells together, so it is suited to assumptions that naturally move as a package. Excel allows up to 32 changing cells in a scenario.

On the Data tab, choose Forecast > What-If Analysis > Scenario Manager.
Select Add, enter a scenario name, and identify the changing cells in the model.
Enter the values for those cells, then add other scenarios using the same changing cells.
Select a scenario and choose Show to apply its values to the worksheet.
Choose Summary to create a report, then select the result cells you want to compare.

For the profit example, one scenario could use 800 units and another 1,300 units, while keeping the price, costs, and fixed-cost assumptions the same. Their profits would be 0 and 5,000, respectively, under the formula above. A more involved set of scenarios could also vary price and costs together, but each scenario should have a clear explanation of why those assumptions belong together.

A Scenario Summary is a report of the scenarios and result cells you selected; it is not a live dashboard that automatically updates every time you revise the model. If you change assumptions and need a refreshed comparison, show the appropriate scenarios and generate a new summary. Scenario Manager is useful for a manageable number of meaningful cases, not for sweeping every possible combination.

Find a required input with Goal Seek

Goal Seek is the right choice when you know the result you want but not the value of one input that would produce it. It changes a single cell to make a formula cell reach a specified value. The target cell must contain a formula that depends, directly or indirectly, on the changing cell.

Choose Data > Forecast > What-If Analysis > Goal Seek.
In Set cell, enter the address of the formula cell you want to target.
In To value, enter the desired result.
In By changing cell, select the input cell Excel may adjust, then choose OK.

In the example, set B6 to 5,000 by changing B2. Since each unit contributes 10 toward fixed costs, the required volume is (5,000 + 8,000) divided by 10, or 1,300 units. Goal Seek can find that result because B6 depends on B2. If the model had a more complicated formula, Excel would search for a suitable input value rather than rearranging the formula algebraically.

Goal Seek adjusts only one input at a time and does not provide a place to specify constraints such as a minimum price or whole-number units. Review the result for plausibility, and check whether a different starting value or model relationship could lead to another solution. For problems involving several changing cells or explicit constraints, Solver may be more appropriate.

Test many values with a Data Table

A Data Table is useful for a repeatable grid of results. A one-variable table tests a series of values for one input, such as several sales volumes. A two-variable table tests combinations of values for two inputs, such as sales volume and price. The table recalculates a selected formula for each tested value or combination; it does not create a separate model for every case.

For a one-variable table testing units sold, list the trial volumes down a column and place a reference to the result formula at the top of the results area. Select the entire table range, including that formula, the trial values, and the empty result cells. Choose Data > Forecast > What-If Analysis > Data Table. In the dialog, specify the model’s units-sold input cell as the Column input cell. Excel substitutes each listed value into that input and returns the corresponding result.

For a two-variable table, put one set of trial values across the top row and the other down the first column, with a reference to the output formula at their intersection. Select the complete grid and open Data Table. Set Row input cell to the model input represented by the values across the top, and Column input cell to the input represented by the values down the side. The intersecting cells then show the output for each pair. Carefully match the row and column input cells to the correct assumptions; reversing them produces misleading labels and results.

Data Tables are especially handy for sensitivity analysis: a reader can see how much an output moves across a range, rather than focusing on one selected scenario. Large tables or complex workbooks can take longer to recalculate, so start with a practical range and a useful number of increments.

Choose the right tool

Start with the question you are asking, not with the menu. The tools overlap in purpose, but they answer different questions:

Choose Scenario Manager to save a few named cases where several assumptions change together.
Choose Goal Seek to ask, “What single input gets this formula to my target?”
Choose a one-variable Data Table to compare one output across many values for one input.
Choose a two-variable Data Table to compare an output across a grid of two input values.
Consider Solver when you need to optimize a result by changing multiple cells or applying constraints. Solver is a separate Excel add-in, not one of the three commands in the What-If Analysis menu.

For example, “What profit do I get in three operating plans?” points to Scenario Manager. “How many units must I sell to earn 5,000?” points to Goal Seek. “How does profit vary for 500 to 1,500 units?” points to a Data Table. “What mix of products maximizes profit while staying under a labor limit?” may call for Solver, after the add-in is enabled and the model is set up.

Common problems and practical checks

If Goal Seek does not find a useful result, first confirm that the Set cell is a formula and that it depends on the changing cell. Check that the target is possible under the model. For instance, if an input does not affect the output, changing it cannot move the formula toward the target. A result that technically meets the target may still be unrealistic, so consider the business limits that are not represented in the worksheet.

If a Data Table shows unexpected values, verify its layout and its row and column input cells. The input cells must be the assumptions used by the model—not the result formula or cells holding labels. Also check that the output formula reference is in the correct corner of the selected table and that the trial values are actual numbers in the intended order.

For any of the tools, keep an untouched copy of the original assumptions or record them before experimenting. Scenario Manager can apply values to the changing cells, and Goal Seek changes its selected input cell. Restore the intended values when you are done, or show the original scenario if you saved one. Label assumptions clearly, document units, and avoid interpreting a result more precisely than the input estimates justify.

Availability and alternatives

In Excel for Microsoft 365 on Windows, the built-in commands are on the Data tab under Forecast > What-If Analysis. In Excel for Mac, look on the Data tab for What-If Analysis; ribbon organization and labels can vary by release. Excel for the web does not provide the same full desktop set of What-If Analysis tools, so open the workbook in desktop Excel when a command is missing or unavailable.

Solver is a related alternative for optimization. In Windows desktop Excel, enable it through File > Options > Add-ins, select Excel Add-ins in the Manage box, choose Go, and check Solver Add-in. After enabling it, look for Solver on the Data tab. Solver setup involves choosing an objective cell, a goal such as maximizing or minimizing it, changing cells, and any necessary constraints. It answers a different question from Goal Seek, which targets a result by changing only one input without a general constraint setup.

For a small number of simple cases, you can also copy a worksheet and enter different assumptions manually, or create a chart from a table of model outputs. Those methods can be easy to understand, but duplicated sheets are more prone to drift if formulas are updated in one copy and not another. A structured scenario or data table keeps the comparison connected to a shared model.

Frequently Asked Questions

Where is What-If Analysis in Excel?
In Excel for Microsoft 365 on Windows, select Data > Forecast > What-If Analysis. The menu contains Scenario Manager, Goal Seek, and Data Table. On Mac, look under What-If Analysis on the Data tab.

What is the difference between Goal Seek and Scenario Manager?
Goal Seek finds one input value that produces a target formula result. Scenario Manager saves and displays named sets of input values, often with several cells changing together. Use the one that matches the question you need to answer.

Can Goal Seek change more than one cell?
No. Goal Seek changes one input cell to reach a target in a formula cell. For multiple changing cells or constraints, consider the separate Solver add-in.

Why does Goal Seek give a result that does not make sense?
Goal Seek follows the worksheet’s formulas; it does not know real-world limits or whether an assumption is reasonable. Check the formula, target, and changing cell, then compare the result with valid ranges and constraints for the situation.

Does Excel What-If Analysis predict the future?
No. It calculates outcomes from the assumptions and formulas in a workbook. The result is conditional—if the inputs and model are as specified, this is the calculated output—not a forecast that the assumptions will occur.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.