iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
If you keep changing spreadsheet inputs by hand to see what happens to a formula, Excel has tools to do that work more deliberately. What-If Analysis is a group of three features—Scenario Manager, Goal Seek, and Data Tables—not one all-purpose command. Each answers a different question: What happens under a saved set of assumptions? What input reaches a target result? How do results vary across many inputs? Microsoft defines the feature as changing cell values to see how those changes affect formula results (Microsoft Support).
Which What-If Analysis tool should you use?
Choose based on the question your worksheet needs to answer. Scenario Manager compares saved cases, Goal Seek works backward from a target, and a Data Table lays out formula results across candidate inputs. Solver is a separate next step for optimization problems with constraints.
| Tool | Use it to answer | Inputs | What you get |
|---|---|---|---|
| Scenario Manager | How do named cases, such as best-case and worst-case budgets, compare? | Saved sets of changing values; up to 32 changing values in each scenario | Switchable scenarios and an optional summary report |
| Goal Seek | What one input value will make a formula reach a target? | One changing cell referenced by the result formula | A target result and the input value Excel found |
| Data Tables | How does a formula’s result change across candidate values? | One or two input variables, with many candidate values | A table of formula outcomes |
| Solver | What combination of decision variables optimizes an objective while meeting limits? | Multiple decision variables and constraints | An optimization solution; Solver is an add-in |
Microsoft describes Scenario Manager, Goal Seek, and Data Tables as What-If Analysis tools (What-If Analysis overview). Solver is a different Excel add-in for optimization (Microsoft’s Solver guide).
Recommended Free Tools
When should you use Scenario Manager?
Use Scenario Manager when you want to compare several combinations of assumptions without replacing the worksheet’s input values each time. A budget might have separate cases for lower, expected, and higher revenue, with corresponding changes to expenses or staffing assumptions.
#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
Create each scenario by giving it a name, selecting the cells whose values will change, and entering that case’s values. Excel can switch the worksheet among the saved cases, and you can create a summary report to compare their results. Each scenario can contain up to 32 changing values, according to Microsoft’s Scenario Manager documentation (Microsoft Support).
A scenario summary report is a snapshot: Microsoft says it does not automatically update when scenario values change. Recreate the report after editing the scenarios if you need the summary to reflect those edits.
When should you use Goal Seek?
Use Goal Seek when you know the result you want and need to find the value of one input that would produce it. The formula must already exist, and the changing cell must be referenced by that formula. Goal Seek changes one input; it is not a way to solve for several independent inputs at once.
Run Goal Seek
- Identify the formula cell whose result you want to target, and the input cell used by that formula.
- Open Data > What-If Analysis > Goal Seek. Menu placement can vary by Excel version and platform.
- In Set cell, select the formula cell. In To value, enter the target result. In By changing cell, select the referenced input cell.
- Run Goal Seek and review the resulting value in the input cell and the formula result in the worksheet.
Microsoft documents a loan example with the payment formula =PMT(B3/12,B2,B1): Goal Seek adjusts the interest-rate input to reach a desired monthly payment. This illustrates the feature’s documented workflow; it is not an independent test (Microsoft’s Goal Seek guide).
When should you use a Data Table?
Use a Data Table when you want to see how a formula changes across many candidate values for one or two inputs. For example, you can lay out several interest rates and see the corresponding payment results, or examine outcomes across combinations of two assumptions. Unlike Goal Seek, which returns an input for one target, a Data Table displays many outcomes together.
Set up a one- or two-variable Data Table
- Place candidate input values in a row, a column, or both, and place a formula linked to the worksheet model at the start of the results area.
- Select the full range containing the formula, candidate values, and cells where results should appear.
- Choose Data > What-If Analysis > Data Table.
- In the dialog, specify the worksheet input cell corresponding to the candidate values in the row, column, or both. Excel substitutes the candidates and fills the table with formula results.
Microsoft says a Data Table can examine one or two variables and use many values for those variables (Microsoft Support). The exact menu labels and placement may differ among Excel versions and platforms.
Rank #4
When is Solver the better next step?
Use Solver when the problem has multiple decision variables and an objective you want to maximize or minimize subject to limits. For instance, a model might seek the highest output while respecting resource constraints. Goal Seek changes one input to hit a target; Solver is designed for this broader optimization setup. Microsoft says Solver is an Excel add-in and add-ins are not supported in Excel for the web (Microsoft Support).
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →How to choose without changing inputs by hand
- Compare named combinations of assumptions: Scenario Manager.
- Find one input that produces a desired formula result: Goal Seek.
- Inspect a grid of outcomes across one or two inputs: Data Table.
- Optimize several decision variables under constraints: Solver, where supported.
These tools do not replace a sound worksheet model: the formulas and input-cell relationships still determine whether the results answer the question you care about.
Quick Recap
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.
