What-If Analysis in Excel: Definition and Examples

Formulas & functions · Updated November 21, 2026

In Excel, what-if analysis means changing input values in a spreadsheet model to see how those changes affect its results. The built-in tools are Goal Seek, Scenario Manager, and Data Tables, found in Excel for Microsoft 365 on Windows under Data > Forecast > What-If Analysis.

What What-If Analysis Means

A spreadsheet model connects input values to calculated outputs. What-if analysis lets you explore how an output changes when you alter one or more inputs, without rebuilding the model for every possibility. For example, a shop owner might change the assumed selling price, number of units sold, or operating costs to see how projected profit responds.

The method is useful because a forecast depends on assumptions, and those assumptions may be uncertain. A sales estimate, interest rate, project timeline, or staffing level is not necessarily a known fact. Testing alternatives can show which assumptions have the greatest effect, where a target becomes attainable, and how much a result might vary across plausible cases.

What-if analysis does not tell you which assumption will happen in real life. It calculates consequences based on the values and formulas you provide. The model’s structure and input quality matter: an omitted cost or incorrect formula can make a precise-looking result misleading.

How Excel’s What-If Tools Work

The three tools answer different kinds of questions. You can use them with ordinary formulas in a workbook; they are not separate forecasting methods that automatically know what your business or project will do.

  • Goal Seek works backward from a desired result. You specify a formula cell, the value you want it to reach, and one input cell Excel may change.
  • Data Tables calculate a formula repeatedly for a list of values in one input cell or combinations of values in two input cells. They are useful for compact sensitivity analysis.
  • Scenario Manager saves named sets of input values, such as optimistic, expected, and cautious assumptions, so you can switch between them or create a comparison summary.

All three rely on the workbook’s existing formulas. If an output cell does not depend on the input cell being changed, the output will not respond as expected. Excel recalculates the model with the test values, then displays the resulting output or outputs.

Build a Small Model Before Testing It

A clear model makes what-if analysis easier to understand. Consider a simple sales example with units sold, selling price, variable cost per unit, and fixed costs. Enter the labels in column A and the values in column B: B2 contains 500 units, B3 contains a price of 20, B4 contains variable cost of 12 per unit, and B5 contains fixed costs of 3,000.

With units in B2 and price in B3, enter this in B6 to calculate revenue: =B2*B3. The result is 10,000 because 500 multiplied by 20 equals 10,000.

With units in B2 and variable cost per unit in B4, enter this in B7 to calculate total variable costs: =B2*B4. The result is 6,000 because 500 multiplied by 12 equals 6,000.

With revenue in B6, variable costs in B7, and fixed costs in B5, enter this in B8 to calculate profit: =B6-B7-B5. The result is 1,000: 10,000 minus 6,000 minus 3,000. This profit cell can now serve as the output for the What-If Analysis tools.

Keep inputs and formulas distinct where practical. Label the cells, use consistent units, and avoid typing assumed values directly into a formula when they can instead be kept in input cells. This makes it clearer which values a tool is changing and easier to review the model later.

Use Goal Seek to Find an Input for a Target

Goal Seek is appropriate when you know the result you want and need to find the input value that would produce it. In the example model, suppose the target is 5,000 in profit and the selling price is the value that may change.

  1. Choose Data > Forecast > What-If Analysis > Goal Seek.
  2. Set Set cell to B8, the cell containing the profit formula.
  3. Set To value to 5000.
  4. Set By changing cell to B3, the selling-price input, then choose OK.

With the stated model, Goal Seek should find a price of 28. At 500 units, revenue is then 14,000; variable costs remain 6,000 and fixed costs remain 3,000, leaving profit of 5,000. Goal Seek changes the input cell’s value, so note or restore the original assumption if you want to keep it. It does not save a collection of alternatives.

Goal Seek changes one input cell to reach one target in one formula cell. It is not a general optimizer: it does not search for the best result across many inputs or enforce a set of business constraints. If the target cannot be reached, or the formula behaves irregularly, the result may not be useful; inspect the model and test reasonable starting values.

Use a Data Table to Compare Many Values

A Data Table is useful when you want to see how an output responds across a range of assumptions. A one-variable table tests a list of possible prices or sales volumes. A two-variable table tests combinations, such as several prices at several sales volumes. The values are trial inputs, not a prediction of which case will occur.

For a two-variable table using the example model, place price alternatives across a row and unit alternatives down a column. In the table’s top-left corner, enter a reference to the output. With profit in B8, enter =B8 in that corner. Put candidate prices across the top row and candidate unit counts down the first column. Select the entire rectangular range, including the formula reference and both sets of values, then choose Data > Forecast > What-If Analysis > Data Table.

In the Data Table dialog, set Row input cell to B3, because the values across the top represent selling prices. Set Column input cell to B2, because the values down the first column represent unit counts. Excel substitutes each combination into those input cells and calculates the linked output. The table’s interior cells show the resulting profits for the tested combinations.

For a one-variable table, put the output reference beside or above the list of trial values and identify the single model input that list represents. The exact arrangement depends on whether the trial values are in a row or a column. Select the complete table range before opening the Data Table dialog, then use the corresponding row or column input cell. Data Tables are different from formatted Excel tables: they are a What-If Analysis feature and generally evaluate a single or small set of linked formula outputs for each test.

Use Scenario Manager for Named Cases

Scenario Manager helps compare named groups of assumptions. It is useful when a case involves several changing cells, such as price, unit sales, and costs, and you want to preserve each combination under a name. Select Data > Forecast > What-If Analysis > Scenario Manager, choose Add, name the scenario, and specify its changing cells. Excel then prompts for the values to store for that case.

For the sales example, you might create an expected case with the original inputs and a cautious case with lower sales and a higher cost. You can use Scenario Manager to show one saved case in the model or create a summary report based on selected result cells. The summary is useful for comparison, but it is a report of the saved assumptions and calculated results, not an assessment of which case is most likely.

Each scenario can change up to 32 cells. If your model requires more changing inputs, consider simplifying the set of assumptions, using a Data Table where appropriate, or organizing the analysis with a separate worksheet. Clearly label the changing cells so scenario values are interpretable.

Choose the Right Tool

Start with the question you need to answer, rather than choosing a feature by name:

  • Choose Goal Seek for a single target question: what price, volume, or cost produces this particular output?
  • Choose a one-variable Data Table to see a range of results as one input changes.
  • Choose a two-variable Data Table to compare the combined effect of two inputs, such as price and volume.
  • Choose Scenario Manager when you want to save and switch among named groups of assumptions affecting several cells.

These tools can complement one another. For example, use Goal Seek to calculate the price needed to meet a profit target, then use a Data Table to see how profit varies around that price at different sales volumes. Keep the analysis focused: testing a few meaningful ranges is usually easier to interpret than filling a worksheet with arbitrary combinations.

Common Problems and Limits

A frequent source of confusion is changing a cell that the result does not actually use. Trace the formula chain from the selected output back to the intended input. Also check that the input cell contains a number rather than a number stored as text, and that formulas use the right units. Mixing monthly costs with annual revenue, for example, makes a comparison invalid even if Excel calculates without an error.

Data Tables can recalculate many times and may slow a large workbook, particularly when the model contains complex formulas or many table cells. Reduce the tested range or number of combinations if calculation becomes sluggish. If Excel’s calculation mode is set to Manual, results may not update until recalculation; check Formulas > Calculation Options when outputs appear stale.

What-if results are conditional, not probabilities. A table showing profit at several prices does not say how likely each price is, and Scenario Manager does not rank cases by likelihood. Likewise, Goal Seek may return a mathematically valid value that is impractical in the real world. Apply sensible limits to the inputs and review the assumptions behind the model.

These commands are available in desktop Excel, though ribbon layout can vary with window size or customization. Excel for Mac has comparable What-If Analysis features, but the ribbon arrangement can differ. Excel for the web may display workbook results without offering the same desktop tools for creating or managing every analysis type; use desktop Excel if a command is missing.

Frequently Asked Questions

What is what-if analysis in Excel used for?
It is used to test how changes to spreadsheet inputs affect calculated results. Common examples include exploring a budget, estimating break-even conditions, comparing sales assumptions, and checking how a project result responds to cost or timing changes.

Where is What-If Analysis in Excel?
In Excel for Microsoft 365 on Windows, look on the Data tab under Forecast, then choose What-If Analysis. The menu contains Goal Seek, Data Table, and Scenario Manager. Ribbon placement can vary in other desktop versions or customized layouts.

What is the difference between Goal Seek and a Data Table?
Goal Seek works backward from one target result to find one input value. A Data Table works forward, calculating outputs for a list of trial values in one or two inputs. Use Goal Seek to solve a target question and a Data Table to compare a range of alternatives.

Does Excel what-if analysis predict the future?
No. It calculates results from the assumptions and formulas in your workbook. It can help examine uncertainty, but it does not determine which assumptions will happen or assign a likelihood to each outcome.

Can Excel change more than one input with Goal Seek?
No. Goal Seek changes one input cell at a time. For named cases involving multiple changing cells, use Scenario Manager; for systematic combinations of two inputs, use a two-variable Data Table.

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.