How to How to Use What-If Analysis in Excel in Excel - Practical Guide

Master What-If Analysis to test multiple scenarios and forecast outcomes without altering your original data. Learn three powerful tools—Data Tables, Scenario Manager, and Goal Seek—to analyze business decisions, budgets, and financial projections. This advanced technique enables you to make data-driven decisions by visualizing how changes in variables impact your results.

Why This Matters

What-If Analysis is essential for financial modeling, risk assessment, and strategic planning in corporate environments. It empowers professionals to make confident, evidence-based decisions by simulating multiple scenarios efficiently.

Prerequisites

•Proficiency with formulas (SUM, IF, basic calculations)
•Understanding of cell references and absolute/relative references
•Familiarity with Excel's Data menu and basic spreadsheet structure

Step-by-Step Instructions

Set Up Your Base Model

Create a spreadsheet with input cells (variables) and output cells (formulas that depend on inputs). Ensure all calculations link to input cells so changes propagate automatically.

Access What-If Analysis Tools

Click Data tab > What-If Analysis dropdown menu (in the Forecast or Data Tools group) to reveal three options: Scenario Manager, Data Table, and Goal Seek.

Create Scenarios with Scenario Manager

Select Data > What-If Analysis > Scenario Manager > Add, name your scenario (e.g., 'Best Case'), specify changing cells, enter values, then repeat for additional scenarios (Worst Case, Base Case). Click Summary to compare all scenarios.

Build One-Variable Data Tables

Create a table with input values in one column/row and your formula reference in the corner cell. Select entire range > Data > What-If Analysis > Data Table, specify Row or Column input cell, and Excel auto-calculates results for each input.

Use Goal Seek for Target Results

Click Data > What-If Analysis > Goal Seek, set the formula cell (To value), enter desired result (By changing cell), specify input cell to adjust, then click OK. Excel finds the input value needed to reach your target.

Alternative Methods

Two-Variable Data Tables

For analyzing two variables simultaneously, arrange input values in rows and columns with your formula in the corner. Select the entire range and use Data > What-If Analysis > Data Table, specifying both row and column input cells.

Manual Scenario Tracking

Copy your base model to separate sheets and manually change inputs to create scenarios. While simpler for small analyses, Scenario Manager is more efficient and organized for complex comparisons.

Solver Tool (Advanced)

For complex optimizations with multiple constraints, use Solver (Data > Solver) instead of Goal Seek. Solver adjusts multiple variables to maximize or minimize a target based on specified constraints.

Tips & Tricks

✓Always keep your original data unmodified by using What-If Analysis tools rather than manually changing cell values.
✓Name your input and output cells using Data > Define Name for clearer, more maintainable models.
✓Use Scenario Manager's Summary report to instantly compare financial outcomes across multiple assumptions.

Pro Tips

★Combine Scenario Manager with Pivot Tables to create dynamic dashboards that update automatically when you switch scenarios.
★For Goal Seek, ensure your formula cell contains a single output value; circular references will cause errors.
★Use Data Tables for sensitivity analysis to visualize how one or two variables affect your bottom-line metric across a range of values.

Troubleshooting

Goal Seek returns 'Could not find a solution'

Verify that your formula cell actually depends on the changing cell you specified. Check for circular references and ensure the target value is logically achievable (e.g., don't seek negative profit if all costs are fixed).

Data Table shows #REF! or #VALUE! errors

Ensure your formula uses correct relative and absolute references. The formula cell should reference the input cell with a relative reference so it adjusts when copied to the table.

Scenario Manager not showing all scenarios in Summary

Verify scenarios are saved by checking Scenario Manager dialog. Only saved scenarios appear in the Summary report; click Edit to confirm each scenario's changing cells and values are correct.

Changes in one scenario affect another scenario's values

This occurs when scenarios reference the same input cells without proper isolation. Use Scenario Manager to create independent copies of each scenario with unique changing cell values.

Related Excel Formulas

IF SUM SUMIF INDEX VLOOKUP PMT

Frequently Asked Questions

Can I use What-If Analysis with charts?

Yes, charts automatically update when you change scenarios or switch between Data Table results. Build your chart based on your model, then apply What-If Analysis to the underlying data; the chart will reflect all changes in real-time.

How many scenarios can I create with Scenario Manager?

Excel supports up to 32 scenarios per worksheet in most versions. For complex analyses requiring more, consider using separate worksheets or the Solver tool for optimization-based approaches.

What's the difference between Data Table and Scenario Manager?

Data Tables are ideal for sensitivity analysis showing how one or two variables affect results across a range of values. Scenario Manager is better for comparing discrete, named scenarios with multiple variable changes and detailed assumptions.

Can Goal Seek work with multiple variables?

No, Goal Seek adjusts only one input cell to reach a target. For multiple variables, use Solver (available in Data > Solver) which handles constraints and optimizations across many changing cells.

Are scenarios saved when I close the file?

Yes, scenarios are saved with the workbook. When you reopen the file, all scenarios remain available in Scenario Manager, though you may need to refresh calculations if source data has changed.