Excel What-If Analysis: Master Goal Seek, Scenario Manager & Data Tables in 2026

Category: Excel | Published: May 24, 2026

Most Excel users know how to build models that calculate outputs from inputs. But the real analytical power comes from asking questions in reverse: What input do I need to reach a specific target? What happens to my bottom line if three variables change at once? Excel's What-If Analysis tools — Goal Seek, Scenario Manager, and Data Tables — answer these questions instantly, and in 2026 they integrate with Copilot to make the insights even more accessible.

This guide covers all three tools with practical examples you can apply to real business problems today.

What Is What-If Analysis?

What-If Analysis is a group of three tools found in Excel under Data > What-If Analysis:

Goal Seek: Works backward from a desired result to find the required input value

Scenario Manager: Saves and compares multiple sets of input assumptions side by side

Data Tables: Calculates a range of outcomes by systematically varying one or two input values

Together, these tools turn a static spreadsheet model into an interactive analytical engine.

Goal Seek: Find the Input You Need

Goal Seek solves one of the most common analytical questions: "I know what answer I want — what input gets me there?" It works with any single input cell and any formula-driven output cell.

Example: Break-Even Pricing

Suppose cell B5 contains your profit formula, and your goal is to reach exactly $0 profit (break even). You want to know what unit price in cell B2 achieves that:

Click Data > What-If Analysis > Goal Seek

Set cell: B5 (your profit formula)

To value: 0

By changing cell: B2 (unit price)

Click OK and Excel iterates until it finds the price that produces zero profit

Goal Seek works for any target: revenue goals, loan payment targets, required sales volumes, investment returns, and more.

Scenario Manager: Compare Best, Base, and Worst Case

Scenario Manager lets you save multiple sets of input assumptions and switch between them instantly — no copy-pasting, no multiple worksheets. It is perfect for presenting best-case, base-case, and worst-case projections to stakeholders.

Setting Up Three Scenarios

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

Name the scenario "Base Case", select your assumption cells (e.g., B2:B4 for growth rate, costs, and price), and enter the base values

Click Add again, name it "Best Case", and enter optimistic assumptions

Add a third "Worst Case" scenario with pessimistic values

Click Show to switch your model to any scenario in real time

Pro tip: Click Summary in Scenario Manager to generate a formatted comparison table showing all scenario inputs and outputs side by side — ideal for sharing with leadership.

Data Tables: Systematic Sensitivity Analysis

Data Tables automate the tedious process of manually testing dozens of input combinations. They come in two forms:

One-variable Data Table: Varies one input across a range of values and shows the resulting output for each

Two-variable Data Table: Varies two inputs simultaneously, producing a full matrix of outcomes

One-Variable Data Table: Interest Rate Sensitivity

Suppose you want to see how monthly mortgage payments change across ten different interest rates. Set up a column of rates (say 3% to 8% in 0.5% increments) and place your PMT formula one row above and one column to the right. Then:

Select the range including your rates and the formula cell

Click Data > What-If Analysis > Data Table

In the Column input cell box, enter the cell that holds your interest rate assumption

Click OK — Excel populates every row with the calculated payment at each rate

Two-Variable Data Table: Price and Volume Matrix

For a two-variable table, place price options across the top row and volume options down the left column. Put your revenue formula in the corner cell. Then select the full range, open Data Table, specify the Row input cell (price) and Column input cell (volume), and click OK. Excel fills the entire matrix showing revenue at every price-volume combination.

Copilot + What-If Analysis in 2026

Copilot in Excel integrates with What-If Analysis in powerful ways:

Ask Copilot to set up a Data Table: "Create a two-variable sensitivity table showing profit across prices from $10 to $50 and volumes from 100 to 1000" and Copilot builds the structure for you

Scenario narrative: After running Scenario Manager, ask Copilot to "Write a one-paragraph summary of the three scenarios and their key differences" for your presentation

Goal Seek suggestion: Ask "What price do I need to achieve a 20% margin?" and Copilot runs Goal Seek and explains the result in plain language

When to Use Each Tool

Use Goal Seek when you know your target and want to find the single input required

Use Scenario Manager when you need to present multiple business cases to stakeholders and switch between them quickly

Use Data Tables when you need to see a full range of outcomes across many values — perfect for sensitivity and stress-testing analyses

Use Solver (not covered here) when you have multiple variable constraints and need to find an optimized solution — think of it as Goal Seek's advanced sibling

Conclusion

What-If Analysis tools transform Excel from a calculation engine into a decision-support platform. Goal Seek answers backward questions, Scenario Manager structures your assumptions into presentable cases, and Data Tables give you the full landscape of possible outcomes. In 2026, with Copilot able to interpret and narrate your results, these tools are more accessible than ever — even for users who are not Excel power users.

Start small: pick any model you already have open and run a Goal Seek on one of your targets. Once you see how fast it works, you will find yourself reaching for these tools in every analytical session.

officelearner.net — MS Office Tips, Tricks & Tutorials