What-If Analysis in Excel: How It Works and When to Use It

Formulas & functions · Updated November 21, 2026

What-If Analysis in Excel is a set of tools for seeing how changing input values affects a formula’s result. In Microsoft Excel for Microsoft 365 on Windows, its three main tools are Goal Seek, Scenario Manager, and Data Tables; use them to find a needed input, compare named sets of assumptions, or calculate results across many possible inputs.

What What-If Analysis means

A spreadsheet model connects inputs, formulas, and outputs. What-If Analysis helps you explore that connection by changing assumptions and observing what happens to calculated results. For example, a sales forecast might depend on units sold, price, and costs. Testing different values can show whether a target is achievable or how sensitive profit is to a price change.

The term describes a way of asking questions, not a single calculation method. You might ask, “What if demand falls by 10 percent?” or “How many units must we sell to reach a target?” The tools make those questions easier to investigate while keeping the underlying model in the worksheet.

These tools do not tell you which assumptions are realistic, predict the future, or guarantee a decision is sound. They calculate consequences based on the values and formulas you provide. The quality of the result therefore depends on the model, its inputs, and your interpretation.

The three built-in tools

In desktop Excel for Windows, find the tools at Data > Forecast > What-If Analysis. The menu contains three options, each suited to a different kind of question:

  • Goal Seek works backward from a target result. You specify a formula cell, the value you want it to reach, and one input cell Excel may change. It is useful for questions such as how many sales are needed to break even.
  • Scenario Manager stores and compares sets of input values. A scenario might represent a low-demand case, a base case, or a high-demand case. You can show a saved scenario in the worksheet or create a summary of scenario results.
  • Data Table calculates a formula repeatedly for a list or grid of input values. A one-variable table varies one input; a two-variable table varies two inputs and displays their combined effects.

These options are related, but they are not interchangeable. Goal Seek searches for an input that produces one specified result. Scenario Manager saves a small number of named cases. A Data Table lays out many calculated outcomes for systematic comparison.

A concrete profit example

Imagine a simple product model. Put units sold in A2, selling price per unit in B2, variable cost per unit in C2, and fixed costs in D2. Enter 100 units, a price of 25, a variable cost of 15, and fixed costs of 800. With those values in place, the profit calculation is:

With units in A2, price in B2, variable cost in C2, and fixed costs in D2, put this formula in E2: =A2*(B2-C2)-D2. The result is 200, because 100 units produce 1,000 of contribution after variable costs, and subtracting 800 in fixed costs leaves 200.

Suppose the question is how many units are needed to reach 1,000 in profit, while price and costs stay unchanged. Choose Data > Forecast > What-If Analysis > Goal Seek. Set the cell to E2, set the target value to 1000, and set the changing cell to A2. Excel finds 180 units: each unit contributes 10, and 180 times 10, minus 800, equals 1,000. This is a mathematical result under the model’s assumptions, not a sales forecast.

For a different question, such as how profit changes at prices from 20 to 30, a Data Table is more appropriate. Keep the profit formula as the table’s output formula and list the alternative prices as test inputs. Excel substitutes each price into the model’s designated input cell and records the resulting profit. If both price and unit volume vary, a two-variable table can show outcomes for combinations of those values.

How to choose the right tool

Start by identifying what is unknown and what you want to learn. If you have a target output and want one input that reaches it, choose Goal Seek. If you want to preserve and switch among a few coherent sets of assumptions, choose Scenario Manager. If you want a table of results over a range of one or two inputs, choose a Data Table.

For example, a small business might use Goal Seek to find the monthly sales volume required to cover expenses. It could use Scenario Manager to keep an optimistic, expected, and cautious set of assumptions for price, costs, and demand. It could use a Data Table to compare monthly profit at several prices and sales volumes. The methods can complement one another: a table can reveal a useful range, and Goal Seek can then find a particular target within that range.

Excel’s built-in Data Table is limited to one or two varying inputs at a time. For larger models with many variables, constraints, or optimization goals, consider Solver, a separate Excel add-in. Solver can search for a best result subject to constraints, whereas Goal Seek changes one cell to reach a specified result and does not offer the same constraint-based optimization.

Setting up a Data Table

A Data Table works best when the worksheet already has a clear formula linked to the input cell or cells you want to test. For a one-variable table, arrange the test values in a row or column and place a reference to the output formula at the start of the table. Select the full table range, including the formula reference and test values, then choose Data > Forecast > What-If Analysis > Data Table.

In the dialog, use the row input cell when test values are arranged across a row, or the column input cell when they are arranged down a column. Choose the model’s original input cell that those test values should temporarily replace. A two-variable table uses both a row input cell and a column input cell, with the two sets of test values arranged along the top and side of the grid.

Take care to select the whole table range and identify the correct model input cells. If the input cell is unrelated to the output formula, the results may repeat or fail to answer the intended question. Data Tables are a special what-if feature, not ordinary formulas copied into each result cell; editing individual result cells is usually not the way to adjust their calculations.

How to build useful scenarios

Scenario Manager is most helpful when each case represents a consistent story. For instance, a low-demand case might combine fewer sales with a lower price, while a high-cost case might combine increased material and shipping expenses. Saving those values together is more informative than changing one input at a time when the inputs are related.

To create one in desktop Excel for Windows, open Data > Forecast > What-If Analysis > Scenario Manager, choose Add, give the scenario a meaningful name, and identify its changing cells. Enter the values for that case, then add other scenarios as needed. Use Show to display a saved set in the worksheet. A summary report can compare selected result cells across the saved cases.

Scenario Manager supports up to 32 changing cells in a scenario. Choose a manageable set of important inputs and label scenarios clearly; names such as “Lower demand” are more useful than “Scenario 2.” Scenario results are comparisons of supplied assumptions, not probability estimates. Unless you add probabilities and analyze them separately, Excel does not imply that one scenario is more likely than another.

What the results can and cannot tell you

What-If Analysis is only as reliable as the model it evaluates. A formula that omits an important cost, uses the wrong units, or assumes a relationship that does not hold can produce a precise-looking but misleading answer. Check the worksheet logic and verify a few results with manual calculations before relying on a large set of outputs.

Goal Seek is a numerical search, not a proof that a unique solution exists. If the formula is nonlinear, there may be several inputs that produce the same target, or Excel may not find a solution from its starting value. If the result seems unexpected, try a reasonable starting value, check the formula’s relationship to the changing cell, and consider whether the target can be reached at all. Goal Seek changes the worksheet’s input cell when it finds a result, so note or restore the original value if you want to keep it.

Data Tables can make a workbook recalculate more slowly, especially when the model contains many formulas or volatile calculations. Keep the tested range focused and avoid building a needlessly huge grid. Scenario summaries and tables also represent selected cases or ranges; they do not automatically account for every possible uncertainty in a real situation.

Common misconceptions and practical tips

What-If Analysis is not the same as Excel’s Forecast Sheet. A Forecast Sheet uses historical time-based data to project a series, while Goal Seek, Scenario Manager, and Data Tables evaluate a workbook model under changed inputs. Neither approach makes assumptions disappear: forecasts depend on historical patterns, and what-if results depend on the model’s formulas and chosen values.

It is also a misconception that Goal Seek automatically finds the best business decision. It finds an input that makes a formula reach a target; it does not judge whether the input is feasible, profitable in a broader sense, or consistent with other constraints. If the model needs to account for limits such as capacity, minimum inventory, or multiple changing values, Solver may be a better fit.

  • Keep assumptions in clearly labeled input cells and distinguish them visually from formulas.
  • Use realistic ranges and consistent units, such as monthly dollars with monthly sales volumes.
  • Record the meaning of each scenario and the date or source of important assumptions.
  • Save a separate copy before making significant changes to a model or running automation.

In Excel for Mac, the available commands and ribbon layout can differ slightly by release, so look on the Data tab for What-If Analysis or the corresponding tool. Excel for the web may not provide the full desktop experience for every what-if feature; use the desktop app if a command is missing or if the workbook depends on a feature that the web version does not support.

Frequently Asked Questions

Where is What-If Analysis in Excel?
In Excel for Microsoft 365 on Windows, open the Data tab and choose Forecast > What-If Analysis. The menu includes Goal Seek, Scenario Manager, and Data Table. The precise ribbon arrangement can vary in other desktop versions.

What is the difference between Goal Seek and Scenario Manager?
Goal Seek changes one input cell to reach a target formula result. Scenario Manager saves and displays named sets of values for multiple changing cells, making it useful for comparing defined cases rather than solving for one target.

Can Excel What-If Analysis change more than one variable?
Yes, but the method matters. Scenario Manager can store multiple changing-cell values, and a two-variable Data Table evaluates combinations of two inputs. Goal Seek changes only one input cell at a time.

Why is Goal Seek not finding the answer I expect?
Check that the set cell contains a formula that depends on the changing cell, that the target is reachable, and that the starting value is reasonable. A nonlinear formula can have multiple solutions or none, and Goal Seek does not apply business constraints automatically.

Is What-If Analysis the same as Solver?
No. Goal Seek is for reaching a target by changing one input, while Scenario Manager compares saved cases and Data Tables calculate ranges of outcomes. Solver is a separate add-in for optimizing an objective while applying constraints.

Related

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.