What-If Analysis in Excel: 3 Tools and When to Use Each

What-If Analysis tools help you explore different scenarios and outcomes. Excel offers three powerful tools: Scenario Manager, Goal Seek, and Data Tables for comprehensive scenario planning.

What is What-If Analysis in Excel?

What-If Analysis is the practice of changing input values in a model to see how the outputs respond — "what if revenue drops 10%?", "what price hits break-even?". Excel builds three dedicated tools for it into the Data tab, each answering a different shape of question: saved scenarios, reverse targets, and sensitivity grids.

Data tab → Forecast group → What-If Analysis

Three options:
- Scenario Manager
- Goal Seek
- Data Table

Keyboard shortcut: Alt + A + W

Which What-If tool should I use?

Use Scenario Manager when you have several named cases with multiple inputs each; Goal Seek when you know the result you want and need the single input that achieves it; and a Data Table when you want to see the output across an entire range of one or two inputs at once.

Tool	Question it answers	Inputs	Best for
Scenario Manager	"How do outcomes compare across named cases?"	Up to 32 changing cells per scenario	Best/likely/worst budgets, saved assumption sets
Goal Seek	"What input gives me this exact output?"	Exactly 1	Break-even, required price, target payment
Data Tables	"How does the output vary across a range?"	1 or 2	Sensitivity analysis, rate/term matrices

How does Scenario Manager work?

Scenario Manager stores named sets of values for the same input cells — for example Best, Likely, and Worst Case budgets — and swaps them into the sheet with one click. It also generates a summary report comparing every scenario side by side, which is ideal for presentations.

Worked example: budget with 3 scenarios

Setup:
Cell B1: Revenue
Cell B2: Costs
Cell B3: =B1-B2  // Profit

Create Scenarios:
1. Data → What-If Analysis → Scenario Manager
2. Add: "Best Case"
   Changing cells: B1, B2
   Values: Revenue=500000, Costs=300000
3. Add: "Likely Case"
   Values: Revenue=400000, Costs=320000
4. Add: "Worst Case"
   Values: Revenue=350000, Costs=340000

Click "Show" on any scenario to load its values.

Scenario Summary Report

Scenario Manager → Summary

Creates table comparing all scenarios:
                Best     Likely    Worst
Revenue         500000   400000   350000
Costs           300000   320000   340000
Profit          200000    80000    10000

Perfect for presentations!

Full walkthrough with naming conventions and merge tips: Excel Scenario Manager guide.

How does Goal Seek work?

Goal Seek runs your formula backward: you name the formula cell, the value you want it to reach, and the one input cell Excel may change. Excel then iterates until the formula hits the target. It handles exactly one input and one target — for multiple variables or constraints you need Solver.

Worked example: required sales for a profit target

Question: "What sales do I need for $100k profit?"

Setup:
Cell B1: Sales (unknown)
Cell B2: Cost Ratio = 60%
Cell B3: Fixed Costs = 20000
Cell B4: =B1*(1-B2)-B3  // Profit

Goal Seek:
Set cell: B4 (profit)
To value: 100000
By changing: B1 (sales)

Result: Need $300,000 in sales

More examples including loan payments and break-even pricing: Goal Seek step-by-step guide.

How do Data Tables work?

A Data Table recalculates one formula for every value in a row or column of test inputs, producing a sensitivity grid in a single operation. One-variable tables test a range of one input; two-variable tables build a full matrix, such as profit at every price/volume combination.

Worked example: one-variable table

Show profit at different price points:

Setup:
Base price in B1: $50
Profit formula in B5

Column A6:A10: Test prices (40, 45, 50, 55, 60)
Cell B5 referenced at the top of the output column

Data Table:
Select the block containing prices + output column
Data → What-If Analysis → Data Table
Column input cell: B1 (price cell)

Shows profit for each price automatically

Worked example: two-variable table

Test price AND volume combinations:

Rows: Different volumes (1000, 1500, 2000)
Columns: Different prices ($40, $50, $60)
Top-left cell: reference to the profit formula

Row input cell: price cell
Column input cell: volume cell

Result: profit matrix for all combinations

Setup details and common #VALUE! pitfalls: Excel Data Tables for what-if analysis.

Practical Example: Investment Analysis

Using All Three Tools

Investment Decision Model:

Inputs:
- Initial Investment: $100,000
- Annual Return: 8%
- Years: 20
- Annual Contribution: $5,000

Scenario Manager:
- Aggressive: 10% return, $10k/year
- Moderate: 8% return, $5k/year
- Conservative: 5% return, $2.5k/year

Goal Seek:
"What annual contribution needed for $1M target?"

Data Table:
Matrix of returns (5-12%) vs years (10-30)
See future value for all combinations

Real-World Applications

Sales Forecasting

Scenarios:
- Optimistic: +20% growth
- Expected: +10% growth
- Pessimistic: +5% growth

Data Table: Growth rate vs marketing spend
Goal Seek: Marketing needed for specific target

Loan Analysis

Scenario Manager: Different down payments
Data Table: Interest rates vs loan terms
Goal Seek: Down payment for target monthly payment

Break-Even Analysis

Scenarios: Different cost structures
Data Table: Price vs volume to profitability
Goal Seek: Units needed to break even

Best Practices

• Use Scenario Manager for pre-defined scenarios you'll revisit
• Use Goal Seek for quick "what input gives this output?" questions
• Use Data Tables for comprehensive sensitivity analysis
• Document assumptions clearly for each scenario
• Create summary reports for stakeholder presentations