If you keep changing spreadsheet inputs by hand to see what happens, Excel’s Data > What-If Analysis menu can do much of that work in a more organized way. It contains three different tools: Scenario Manager for saved sets of inputs, Goal Seek for working backward from a target, and Data Tables for comparing many possible outcomes. They are not interchangeable.
What Excel What-If Analysis does
Microsoft defines What-If Analysis as changing cell values to see how those changes affect worksheet formula results. Instead of repeatedly overwriting inputs, you can use the tools to examine outcomes while keeping the underlying formulas in place. The right choice depends on whether you want to compare saved cases, solve for one input, or view a range of outcomes.
As an Amazon Associate I earn from qualifying purchases.
Choose the tool that matches the question
| Tool | Use it when | Inputs and output |
|---|---|---|
| Scenario Manager | You want to save and switch among named cases, such as best-case and worst-case budgets. | Each scenario stores a set of changing values, with a maximum of 32 changing values per scenario. You can create a summary report. |
| Goal Seek | You know the result you want and need to find an input that produces it. | Changes one input cell referenced by the formula in the selected result cell; returns a target result. |
| Data Tables | You want to see how a formula result changes across many candidate values. | Examines one or two input variables and presents the outcomes together in a table. |
| Solver | You need to optimize an objective using multiple decision variables and constraints. | Uses multiple variables subject to limits. Solver is an add-in and add-ins are not supported in Excel for the web. |
The first three are the What-If Analysis tools. Solver is a separate add-in that becomes relevant when the problem is an optimization model rather than a simple sensitivity check.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use Scenario Manager to compare saved cases
Scenario Manager is useful when a case consists of several inputs that belong together. For example, a budget might have different revenue, staffing, and cost assumptions for each outlook. Save each set as a scenario, then switch between them to see how the worksheet’s formulas respond. A scenario can contain up to 32 changing values.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
You can also create a scenario summary report to compare results. The report is a snapshot: it does not automatically update if you later edit the scenario values. Recreate the summary after changing scenarios if you need an up-to-date comparison.
Use Goal Seek to find one input for a target result
Goal Seek works backward. You provide a formula cell, the result you want, and one input cell for Excel to adjust. That changing cell must be referenced by the formula in the result cell; Goal Seek cannot vary multiple inputs at once.
- Open Data > What-If Analysis > Goal Seek. Menu placement or labels can vary by Excel version and platform.
- In Set cell, enter or select the cell containing the formula whose result you want to target.
- In To value, enter the desired result.
- In By changing cell, select the input cell referenced by that formula.
- Run Goal Seek and review the proposed value and resulting worksheet. Keep the result only if it makes sense for your model.
Microsoft’s documented loan example uses =PMT(B3/12,B2,B1) as the payment formula and has Goal Seek adjust the interest-rate input to reach a desired monthly payment. This illustrates the feature’s documented workflow; it is not an independent test.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsUse a Data Table to compare many outcomes
A Data Table lays out formula results for candidate values of one or two inputs. It is better suited to a grid of possibilities than to a few named cases: enter the candidate values, include the formula result, select the table range, and assign the relevant worksheet input cell or cells.
- Arrange the candidate values in a row or column. For a two-variable table, place one set across a row and the other down a column.
- Place a reference to the formula result in the appropriate corner of the table layout.
- Select the full range, including the result formula and candidate values.
- Choose Data > What-If Analysis > Data Table, then specify the row input cell, column input cell, or both, as appropriate.
Microsoft says Data Tables can examine one or two variables and use many candidate values. Unlike Scenario Manager’s fixed limit of 32 changing values per scenario, a Data Table is designed to show a range of values for its one or two inputs. Exact menu labels and placement may differ by Excel edition or platform.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When Solver is the better next step
Use Solver when you need Excel to optimize a result while adjusting multiple decision variables under constraints—for example, a model where several quantities can change but must stay within specified limits. It is an Excel add-in, and Microsoft says add-ins are not supported in Excel for the web. If your task is simply to find one input for a target formula result, Goal Seek is the more direct tool.
Quick Recap
Rank #4
Keep the distinction straight
- Use Scenario Manager for named, saved sets of multiple inputs.
- Use Goal Seek when you know the desired formula result and want Excel to find one input.
- Use a Data Table to display outcomes across many values for one or two inputs.
- Use Solver when the task is constrained optimization with multiple decision variables.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
