Study guide

12.2 What-If Analysis: Goal Seek & Scenario Manager

Microsoft Excel Expert Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Exam Overview & MO-211 Fundamentals

1.1 MO-211 Exam Structure, Scoring, & Performance-Based Strategy 1.2 Excel Environment & Microsoft 365 Architecture

2 Chapter 2: Managing Workbooks & File Options

2.1 Workbook Versioning, AutoRecover, & File Properties 2.2 External Data Referencing & Cross-Workbook Formulas 2.3 Macro Security, Portability, & Workbook Distribution

3 Chapter 3: Workbook Protection & Collaboration Settings

3.1 Cell Locking, Range Permissions, & Worksheet Protection 3.2 Workbook Structure Protection & Editing Restrictions 3.3 Formula Calculation Options & Iterative Calculations

4 Chapter 4: Advanced Data Entry & Series

4.1 Flash Fill Patterns & Text Extraction Techniques 4.2 Advanced Fill Series (Linear, Growth, Date, & Step Configurations) 4.3 Dynamic Sequence & Random Data Generation (RANDARRAY, SEQUENCE)

5 Chapter 5: Custom Formatting & Data Validation

5.1 Custom Number Formatting Syntax & Masking Rules 5.2 Data Validation Criteria & Custom Formula Rules 5.3 Validation Input Messages, Error Alerts, & Data Circle Auditing

6 Chapter 6: Data Grouping, Summarization, & Cleansing

6.1 Data Grouping, Ungrouping, & Multi-Level Outlining 6.2 Subtotal Insertion & Summary Function Calculations 6.3 Duplicate Record Removal & Text-to-Columns Cleansing

7 Chapter 7: Advanced Conditional Formatting & Filtering

7.1 Formula-Based Conditional Formatting & Logical Tests 7.2 Data Bars, Color Scales, Icon Sets, & Rule Hierarchy 7.3 Advanced Filtering with Complex Criteria Ranges

8 Chapter 8: Logical Operations & Advanced Multi-Condition Functions

8.1 Nested IF, IFS, & SWITCH Logical Structures 8.2 Compound Boolean Logic: AND, OR, NOT, & XOR 8.3 Multi-Criteria Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, & MINIFS

9 Chapter 9: Advanced Lookup Functions & Variable Optimization

9.1 Modern Lookups with XLOOKUP & XMATCH 9.2 Classic Matrix & Two-Way Lookups: INDEX & MATCH 9.3 Formula Optimization & Variable Assignment with LET

10 Chapter 10: Date, Time, Text, & Auditing Functions

10.1 Current Date, Time, & Day-of-Week Functions: NOW, TODAY, & WEEKDAY 10.2 Business Date Calculations: WORKDAY, NETWORKDAYS, EDATE, & EOMONTH 10.3 Modern Text Extraction & Array Splitting: TEXTSPLIT, TEXTJOIN, TEXTBEFORE, TEXTAFTER 10.4 Error Handling & Formula Auditing Tools

11 Chapter 11: Dynamic Array Functions & Spill Operations

11.1 Array Filtering & Extraction: FILTER Function 11.2 Multi-Level Sorting: SORT & SORTBY Functions 11.3 Deduplication & Sequence Arrays: UNIQUE & Array Reshaping

12 Chapter 12: Data Analysis, What-If Tools, & Financial Functions

12.1 Summarizing Multiple Ranges with the Consolidate Feature 12.2 What-If Analysis: Goal Seek & Scenario Manager 12.3 Financial Modeling Functions: PMT, NPER, & Forecast Logic

13 Chapter 13: Macro Automation & VBA Fundamentals

13.1 Recording Macros with Absolute vs. Relative References 13.2 Macro Storage, Shortcut Assignment, & Form Control Buttons 13.3 Managing & Modifying VBA Code in the Visual Basic Editor

14 Chapter 14: Advanced & Specialized Visualizations

14.1 Statistical Visualizations: Histograms & Pareto Charts 14.2 Hierarchy & Financial Visualizations: Waterfall, Funnel, Treemap, & Sunburst 14.3 Distribution & Multi-Axis Charts: Box & Whisker & Dual-Axis Combo Charts

15 Chapter 15: PivotTable Construction & Field Configuration

15.1 PivotTable Data Architecture, Layouts, & Style Configuration 15.2 Field Summarization, Number Formatting, & Custom Calculations 15.3 Value Display Modes: % of Total, Running Total, & Differences

16 Chapter 16: PivotTable Grouping, Slicers, & PivotCharts

16.1 Date & Numeric Field Grouping Strategies 16.2 Calculated Fields & Calculated Items in PivotTables 16.3 Slicers, Timelines, Report Connections, & PivotCharts

12.2 What-If Analysis: Goal Seek & Scenario Manager

Key Takeaways Explain This Topic AI

Goal Seek and Scenario Manager both sit under Data > Forecast > What-If Analysis, alongside Data Table.

Goal Seek requires a formula in Set cell, a literal typed number in To value, and a constant in By changing cell that is a direct or indirect precedent of the Set cell formula.

Goal Seek is an iterative approximation governed by the Maximum Iterations (default 100) and Maximum Change (default 0.001) settings under File > Options > Formulas, and it adjusts exactly one cell — multi-variable problems need Solver.

Microsoft caps a single scenario at 32 changing cells while placing no limit on the number of scenarios; a Scenario PivotTable report requires result cells, whereas a Scenario summary report does not.

Scenario reports are snapshots that never refresh, and applying a scenario to a cell containing a formula overwrites that formula with a constant.

Last updated: September 2026

What-If Analysis: Goal Seek & Scenario Manager

Financial models are usually built to answer the forward question: given these inputs, what is the result? Objective 3.4 requires the reverse and the parallel. Goal Seek runs the model backwards — you name the answer you need and Excel finds the input that produces it. Scenario Manager runs the model sideways — you store several complete input sets and switch between them on demand. Both live in the same menu: Data tab ▸ Forecast group ▸ What-If Analysis ▾

Scenario Manager...

Goal Seek...

Data Table...

Goal Seek: Solving Backwards for One Input

Goal Seek answers questions shaped like "what unit price gets me to a $2,000,000 gross margin?" It repeatedly substitutes trial values into one input cell until the formula cell reaches the target. ┌─ Goal Seek ──────────────────────────┐

  • │ Set cell: [ $D$14 ] ◄── must contain a FORMULA
  • │ To value: [ 2000000 ] ◄── must be a typed NUMBER
  • │ By changing cell: [ $B$4 ] ◄── must contain a CONSTANT
  • └──────────────────────────────────────┘
  • The Three Rules That Govern Every Field
  • Field Hard requirement What happens if you break it
  • Set cell Must contain a formula Excel rejects the entry: a constant has nothing to solve
  • To value Must be a literal number typed into the box A cell reference such as F2 is not accepted

By changing cell Must contain a constant, and must be a precedent of the Set cell formula Excel reports that the changing cell must be dependent on the Set cell formula

The changing cell may be an indirect precedent — Goal Seek follows the whole dependency chain, so an input three formulas upstream is still valid.

How the Solution Is Found

Goal Seek is an iterative numerical approximation , not an algebraic solve. It converges under the same two controls that govern circular-reference iteration, set at File > Options > Formulas :

  • Maximum Iterations — default 100
  • Maximum Change — default 0.001

When the model has no solution, several solutions, or a discontinuity, the status dialog reports that Goal Seek "may not have found a solution." Tightening Maximum Change and raising Maximum Iterations improves precision on stubborn models at the cost of speed.

Committing or Discarding the Result

Goal Seek writes its answer straight into the changing cell — the original input is gone from the sheet the moment the search finishes.

  • Action in the Goal Seek Status dialog Result
  • OK The solved value is kept permanently in the changing cell
  • Cancel The original input value is restored

Copy the baseline input to a scratch cell before running Goal Seek on a live model; Ctrl+Z works, but only until the next recalculation-heavy edit.

Scope Limits

Goal Seek adjusts exactly one cell. Multi-variable optimisation, constrained targets, and integer requirements need the Solver add-in ( File > Options > Add-ins > Excel Add-ins > Solver Add-in ), not Goal Seek.

Scenario Manager: Storing Complete Input Sets

Where Goal Seek changes one cell, a scenario stores an entire named combination of inputs — Best Case, Base Case, Recession — and applies them all at once.

Building a Scenario

Data > What-If Analysis > Scenario Manager > Add...

Scenario name : a descriptive label such as Recession Case .

Changing cells : the input cells this scenario overrides. Hold Ctrl to pick non-contiguous cells. Microsoft documents the ceiling precisely: "A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want."

Comment : Excel pre-fills the author and date; overwrite it with the assumption rationale.

Protection : Prevent changes and Hide take effect only once the worksheet itself is protected via Review > Protect Sheet .

Enter the value for each changing cell and click OK , or Add to define the next scenario immediately.

There is no limit on the number of scenarios, only on the changing cells inside one.

Applying and Reporting

  • Button Effect
  • Show Pushes the selected scenario's values into the worksheet and recalculates the model live
  • Edit Reopens the definition to revise values or the changing-cell set
  • Merge Imports scenarios from another worksheet or another open workbook
  • Summary Generates a comparison report on a new worksheet

The Scenario Summary dialog offers two report types and a Result cells box for the output cells you want compared:

Scenario summary — a static, outlined table with one column per scenario plus a Current Values column reflecting whatever was on the sheet when the report was generated. Microsoft notes you do not need result cells for this report type.

Scenario PivotTable report — a PivotTable of the same comparison. Result cells are required here.

Note

Name your changing cells and result cells before generating the report. Without defined names the summary column headings read $B$4 and $D$14 ; with them, they read Unit_Price and Gross_Margin .

Traps Worth Memorising

A scenario report is a snapshot . Editing the model afterwards does not refresh it — regenerate the summary.

If a changing cell contains a formula, applying a scenario overwrites that formula with a constant . Restrict changing cells to hard-coded inputs.

Merge only sees workbooks that are currently open, and the merged scenarios must reference the same changing cells to be meaningful.