What-If Analysis in Excel: A Step-by-Step Guide

Formulas & functions · Updated November 18, 2026

To add what-if analysis in Excel, open the Data tab and choose Forecast > What-If Analysis, then select Goal Seek, Scenario Manager, or Data Table. These tools show how changing one or more inputs affects a formula, so you can compare possible outcomes without manually rebuilding your calculations.

Before starting, make sure your worksheet has clearly labeled input cells and formulas that depend on those inputs. The example below uses a simple profit model; the same approach works for schedules, inventory estimates, project budgets, and other spreadsheet models.

What You’ll Need

  • Excel for Microsoft 365 on Windows. Some what-if tools or their ribbon locations may differ in Excel for Mac, and support in Excel for the web can vary; use the desktop app if a command is missing.
  • A workbook with at least one formula linked to one or more input cells. Keep input values separate from formula cells.
  • For the worked example, a blank worksheet and these labels and values: A2, Unit price; B2, 25; A3, Units sold; B3, 120; A4, Fixed costs; B4, 1800; A5, Profit; and B5, the profit formula.

With unit price in B2, units sold in B3, and fixed costs in B4, enter =B2*B3-B4 in B5. At the example values, the result is 1,200: 25 multiplied by 120 is 3,000, and subtracting 1,800 gives 1,200. This one formula will be the output for the examples that follow.

Step-by-Step

  1. Set up and check the model. Enter the sample labels and values, then add the formula to B5. Confirm that B5 displays 1,200. If it does not, check that the values are in the stated cells and that the formula references B2, B3, and B4. In a real workbook, use the same structure: designate input cells, calculate a result in a formula cell, and label both so you can identify them later.
  2. Open the What-If Analysis menu. Select the Data tab on the ribbon. In Excel for Microsoft 365 on Windows, find Forecast and open What-If Analysis. The menu includes Goal Seek, Scenario Manager, and Data Table. On Mac, look on the Data tab for What-If Analysis; the group or menu placement can vary with the Excel version. If you do not see a tool in Excel for the web, open the workbook in the desktop app.
  3. Use Goal Seek to find one input that reaches a target. Choose What-If Analysis > Goal Seek. In Set cell, enter or select B5, the cell containing the formula. In To value, type 3000. In By changing cell, enter or select B2, the unit price. Select OK, review the result, and choose OK again to keep it or Cancel to restore the original value. With 120 units and 1,800 in fixed costs, Goal Seek finds a price of 40 because 40 multiplied by 120, less 1,800, equals 3,000. Goal Seek changes one input cell; it does not create a permanent list of possible prices.
  4. Save a copy of the starting values before exploring. Goal Seek changes the chosen input in your worksheet when you accept its result. Record the original assumptions, duplicate the worksheet, or use Undo promptly if you want to return to them. For the example, restore B2 to 25 before trying the next method. Keeping a baseline prevents a trial value from being mistaken for your original plan.
  5. Use Scenario Manager to compare named combinations. Choose What-If Analysis > Scenario Manager, then select Add. Give the first scenario a name, such as Base case. In Changing cells, select the input cells B2:B4; when selecting separate cells, use Ctrl-click on Windows. Enter the values for that case when prompted. Add more scenarios, such as Higher price or Lower sales, using different values for the same changing cells. Select a scenario and choose Show to apply its assumptions and see the formula result update. Scenario Manager supports up to 32 changing cells in a scenario.
  6. Create a scenario summary when you want a side-by-side report. In Scenario Manager, choose Summary, select Scenario summary, and identify B5 as the result cell. Confirm the dialog to create a new worksheet showing the scenario assumptions and results. Review the new sheet before sharing it: the summary is a report, not a live dashboard that automatically updates as you edit the original assumptions. You can rerun the summary after changing the scenarios.
  7. Build a one-variable Data Table for a range of possible prices. Restore the original model inputs, including B2 at 25. In D3:D7, enter 20, 25, 30, 35, and 40. Leave D2 blank and enter =B5 in E2 to link the table to the result. Select the full range D2:E7, then choose Data > Forecast > What-If Analysis > Data Table. Leave Row input cell empty and set Column input cell to B2, because the trial prices are listed down a column and replace the model’s price input. Select OK. The results in E3:E7 should be 600, 1,200, 1,800, 2,400, and 3,000. For example, the first result uses a price of 20: 20 multiplied by 120, less 1,800, equals 600.
  8. Choose the right tool for the question. Use Goal Seek when you know the desired result and need one input that reaches it. Use Scenario Manager when you want to save and switch between named sets of assumptions. Use a Data Table when you want Excel to calculate results across a row or column of trial values; a two-variable table can test two inputs together. Data Tables are a special what-if feature, not the same thing as formatting a range as an Excel table.

Common Mistakes

  • Selecting a value cell instead of a formula cell in Goal Seek. The Set cell must contain a formula that responds to the changing input. A typed number cannot recalculate when Excel adjusts another cell.
  • Using an unrelated changing cell. If the selected cell does not feed into the result formula, Goal Seek cannot move the result toward its target. Trace the formula references first.
  • Putting the Data Table formula in the wrong place. For a vertical one-variable table, the output formula belongs above the results column, with trial inputs below it in the adjacent column. Select the entire rectangle, including the formula and input cells.
  • Choosing the wrong input orientation. Values listed vertically require a Column input cell. Values listed horizontally require a Row input cell. The input cell is the original model cell Excel temporarily substitutes with each trial value.
  • Overwriting assumptions without recording them. Goal Seek and Scenario Manager can change displayed worksheet values. Keep a baseline copy and clearly label which assumptions are active.
  • Expecting Scenario Manager to optimize automatically. It stores and displays cases you define; it does not search for the best case. Use Goal Seek for a single target input, or a separately designed optimization approach for a more complex question.

Tips

  • Use meaningful names. Labels such as Price, Units, and Fixed costs are easier to verify than unexplained cell references. Keep input cells visually distinct from formula cells, for example with a consistent fill color and a small legend.
  • Test reasonable ranges. A Data Table is useful only when its trial values make sense for the model. Include a low, expected, and high value, and use increments that make the result easy to interpret.
  • Keep one clear output formula. Link the table to the exact result you want to compare. If you need to compare revenue and profit, set up separate output tables or use a suitable scenario summary with both result cells.
  • Check assumptions before sharing. Include units, time periods, and whether costs are fixed or variable. A result can be mathematically correct but misleading if the assumptions are unclear.
  • Use formatting to make comparisons readable. Add headings to the scenario report or table, apply number formats such as currency when appropriate, and use conditional formatting only as a visual aid. It does not change the calculation.
  • Save before a large analysis. A duplicate worksheet or workbook gives you a reliable reference if you accept the wrong Goal Seek result or edit scenario inputs unintentionally.

Troubleshooting

Goal Seek says it cannot find a solution. Confirm that Set cell is a formula, that the changing cell affects that formula, and that the target is plausible. Check for constraints in the model, such as a formula that always returns a fixed value or an input that is ignored. Try a target closer to the current result to see whether the relationship responds. Goal Seek handles one changing cell and may not find a useful answer for a discontinuous or complicated formula.

The Data Table returns the same result in every row. Check that the output formula points to the correct result cell, that the column input cell is the model input used by that result, and that you selected the full table range. Verify that the trial values are numbers rather than text. Reopen the Data Table dialog and confirm that the row and column input cell fields match the orientation of your inputs.

The Data Table does not calculate or appears blank. Make sure the formula is in the top row of the output column and the input values are immediately below the blank corner cell. Recreate the table selection if necessary. Also check calculation settings under Formulas > Calculation Options; if calculation is set to Manual, choose Automatic or press F9 to recalculate. On Mac, use the Formulas tab to find calculation options; keyboard shortcuts can differ.

A scenario changes the wrong cells or gives an unexpected result. Open Scenario Manager, select the scenario, and choose Edit to inspect its changing-cell references and stored values. Confirm that the result formula depends on those inputs. If the scenario summary is outdated, generate a new summary after editing the scenarios.

You cannot find What-If Analysis. Confirm that you are using the Excel desktop app and check the Data tab for the Forecast group. Ribbon layouts can vary by platform and window size. On Mac, check the Data tab for the command in its available ribbon groups. Excel for the web may not offer every desktop what-if command, so open the file in desktop Excel when needed.

Frequently Asked Questions

What does What-If Analysis do in Excel?
It evaluates how changing input values affects formulas. Goal Seek works backward from a target, Scenario Manager saves named sets of inputs, and Data Tables calculate results for multiple trial values.

Where is What-If Analysis in Excel 365?
In Excel for Microsoft 365 on Windows, open Data > Forecast > What-If Analysis. The exact ribbon grouping can differ on Mac or in other versions; look on the Data tab or use the desktop app if the command is missing in the web version.

Can I use Goal Seek with more than one changing cell?
No. Goal Seek changes one input cell to reach a target in a formula cell. To compare several combinations of multiple inputs, use Scenario Manager; to calculate a grid for two inputs, set up a two-variable Data Table.

What is the difference between a Scenario and a Data Table?
A Scenario is a named set of values that you save and show on the worksheet. A Data Table automatically calculates the same formula for a series of one or two input values and displays the results together.

Can a Data Table test two variables?
Yes. Put trial values for one input across the top row and values for the second input down the first column, with the result formula in the corner above and to the left of the grid. Select the full range and enter the model’s horizontal input in Row input cell and vertical input in Column input cell.

Will Goal Seek or a Data Table change my original model?
Goal Seek changes its selected input when you accept the result. A Data Table temporarily substitutes trial values to calculate its grid, while your model inputs remain in place. Save a copy or record the baseline assumptions before experimenting.

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.