What Is What-If Analysis in Excel? Complete 2026 Guide

If you've ever wanted to ask Excel "what happens to my profit if I change this price?" or "how many units do I need to sell to hit my revenue target?" — you've already wanted what-if analysis. Excel has three built-in what-if tools that solve exactly these problems: Goal Seek, Scenario Manager, and Data Tables. Each one answers a different flavor of what-if question. This article covers what what-if analysis is, the three tools, when to use each, and the practical limitations.

Free SEO + AI Search Audit. What-if analysis helps you model business scenarios. The biggest "what if" for most businesses in 2026: what if your visibility in AI search platforms like ChatGPT, Claude, Perplexity, and Gemini is much lower than you think? Run your free audit → to find out in 60 seconds.

What Is What-If Analysis?

In simple terms, what-if analysis is the process of changing values in your spreadsheet to see how those changes affect formulas and outcomes. It's how you answer questions like:

• "What if I raise prices by 10%?" — affects revenue and margin
• "What sales volume do I need to break even?" — affects volume calculation
• "How does inflation at 3%, 5%, and 7% affect my 5-year forecast?" — multiple scenarios

Excel includes three what-if tools, each suited to a different question type:

• Goal Seek — find the input that produces a specific output
• Scenario Manager — save and compare multiple sets of input values
• Data Tables — see how one or two variables affect a formula across a range of values

All three live under the Data tab → What-If Analysis dropdown.

Tool 1: Goal Seek (Find the Input for a Target Output)

Goal Seek is the simplest what-if tool. It answers: "What input value would I need to get this specific output?"

Example: You have a profit formula that depends on units sold. What's the minimum units sold to hit $100,000 profit?

How to use it:

1. Build your formula. Cell B5 contains `=Units × Price - Costs`. Currently shows $80,000.
2. Click Data → What-If Analysis → Goal Seek.
3. Set cell: B5 (the profit cell you want to change).
4. To value: 100000 (the target profit).
5. By changing cell: B1 (the units sold cell — the input you want Goal Seek to solve for).
6. Click OK. Excel iterates and shows the units number that produces $100K profit.

Goal Seek works with single-variable problems where there's a deterministic relationship. It uses iterative numerical methods (no calculus needed from you) to find the answer.

When to Use Goal Seek

• Break-even analysis (what volume to hit zero or target profit)
• Pricing decisions (what price gives a target margin)
• Loan calculations (what monthly payment hits a target balance by a target date)
• Reverse-engineering targets

Tool 2: Scenario Manager (Save and Compare Multiple Scenarios)

Scenario Manager lets you save multiple sets of input values as named "scenarios" and switch between them or compare them side-by-side.

Example: You're modeling 2026 revenue under three scenarios — Best Case, Base Case, Worst Case — each with different assumptions for growth rate, churn, and ARPU.

How to use it:

1. Build your forecast model with the assumption inputs in specific cells (e.g., B2 = growth rate, B3 = churn rate, B4 = ARPU).
2. Click Data → What-If Analysis → Scenario Manager → Add.
3. Scenario name: "Base Case."
4. Changing cells: B2:B4 (the assumption cells).
5. Click OK and enter the Base Case values.
6. Click Add again to create "Best Case," then "Worst Case," each with their own input values.
7. To switch between them, open Scenario Manager, select a scenario, and click Show.
8. To compare side-by-side, click Summary in Scenario Manager. Excel generates a report comparing all scenarios.

Free SEO + AI Search Audit. Scenario planning helps you model business outcomes. The same logic applies to search visibility — what's the best case, base case, and worst case for your AI search performance? Run a free audit of how your site performs across Google AND every major AI search platform.

When to Use Scenario Manager

• Financial forecasting under different macro assumptions
• Strategic planning (what if we expand to market X vs Y)
• Risk analysis (best/expected/worst case modeling)
• Comparing multiple deal structures

Tool 3: Data Tables (One-Variable or Two-Variable Sensitivity Analysis)

Data Tables show how a formula's output changes when one or two input variables vary across a range. They're the right tool for sensitivity analysis.

One-Variable Data Table

Example: How does monthly profit change as price varies from $50 to $100 in $5 increments?

How to use it:

1. In a worksheet, lay out the price range in column A (A2:A12 = $50 through $100 in $5 increments).
2. In cell B1 (one row above and to the right), enter `=[reference to your profit formula]` (e.g., `=B5` where B5 is the profit cell).
3. Select the range A1:B12.
4. Click Data → What-If Analysis → Data Table.
5. Row input cell: leave blank (since we're using a column variable).
6. Column input cell: the original price cell that the formula references (e.g., B3).
7. Click OK. Excel fills column B with profit values for each price.

Two-Variable Data Table

Example: How does monthly profit change as price varies AND as units sold vary?

How to use it:

1. Lay out the price range in column A and the units range in row 1 (e.g., $50/$60/$70 down column A; 100/200/300 across row 1).
2. In the top-left corner cell (A1), enter `=[reference to your profit formula]`.
3. Select the entire range including the corner cell.
4. Click Data → What-If Analysis → Data Table.
5. Row input cell: the units cell.
6. Column input cell: the price cell.
7. Click OK. Excel fills the grid with profit values for every price/units combination.

When to Use Data Tables

• Sensitivity analysis (how robust is my answer to input changes?)
• Pricing optimization (what price+volume combination maximizes profit?)
• Investment analysis (how do returns vary with input assumptions?)
• Stress testing financial models

How to Choose the Right What-If Tool

The three tools answer different questions:

Use Goal Seek When...

You know what output you want and need to find the input that gets there. Single variable, single target.

Use Scenario Manager When...

You want to save multiple sets of input assumptions (best/base/worst case) and switch between or compare them. Multiple variables, multiple scenarios.

Use Data Tables When...

You want to see how output varies across a range of inputs. One or two variables, many input values, sensitivity-oriented analysis.

Free SEO + AI Search Audit. What-if analysis works on inputs you can model. AI search visibility isn't an input you control — but it is something you can measure and optimize. Get a free audit to see exactly where your site stands across Google AND every major AI search platform.

When Should You Use What-If Analysis?

Excel's what-if tools are useful in these scenarios:

1. Financial Modeling and Forecasting

The classic use case. Build a 3-statement model (income, balance sheet, cash flow) and use Scenario Manager to compare growth scenarios.

2. Pricing Analysis

Use Data Tables to find the price/volume combination that maximizes profit. Use Goal Seek to find the price that hits a target margin.

3. Break-Even and Goal Setting

Goal Seek answers questions like "what volume do I need to break even" or "what conversion rate do I need to hit my revenue target."

4. Sensitivity Analysis

Before committing to a forecast or business decision, use Data Tables to see how robust the answer is to changes in key assumptions. If a small change in input produces a huge change in output, your model is fragile.

5. Investment Analysis

Compare different investment scenarios (different ROI, different hold periods) using Scenario Manager. Use Data Tables to see how NPV or IRR varies with discount rate.

Limitations of Excel's What-If Tools

Goal Seek only solves one variable at a time. For multi-variable optimization (e.g., maximize profit changing both price AND volume), you need Solver, a more powerful add-in.

Scenario Manager max 32 scenarios per sheet. For more, switch to a dedicated scenario-planning tool or a custom model.

Data Tables max 2 variables. For 3+ variable sensitivity analysis, build multiple Data Tables or use pivot tables.

Performance slows on large models. Data Tables recalculate the formula for every combination — a 100×100 Data Table runs 10,000 calculations. For complex formulas, this gets slow.

Goal Seek can fail on non-monotonic functions. If the relationship between input and output isn't smooth, Goal Seek may converge on a wrong answer or fail to converge.

Hidden in the ribbon. What-If Analysis is under Data → What-If Analysis dropdown — easy to miss. Many Excel users never discover it.

When to Graduate Beyond Excel What-If Tools

Use Solver instead of Goal Seek when: You need to optimize with constraints (maximize profit subject to budget cap, capacity limit, etc.). Solver is a free Excel add-in.

Use Power BI / Tableau / Looker Studio when: You want stakeholders to interactively explore scenarios in a dashboard rather than running them in Excel.

Use Monte Carlo simulation when: Your inputs are probabilistic (not single values). Add-ins like @RISK or custom VBA can do this in Excel; dedicated tools handle it natively.

Use a real planning tool when: Multiple stakeholders need to collaborate on a model. Anaplan, Adaptive Insights, or similar tools are built for collaborative planning.

Final Thoughts

Excel's what-if analysis tools are powerful and free, hidden in plain sight in the Data tab. Goal Seek for finding inputs, Scenario Manager for comparing sets of assumptions, and Data Tables for sensitivity analysis cover most everyday business modeling needs.

The bigger "what if" for most businesses in 2026, though, is what's happening to their visibility as buyers shift from Google to ChatGPT, Claude, Perplexity, and Gemini for product research. Most teams have no measurement of where they stand in AI search. Run a free audit to see exactly where your site performs across Google AND every major AI search platform — and which fixes will move your traffic the fastest this quarter.