How to Create What-If Scenario in Excel - Practical Guide

Learn to create what-if scenarios in Excel using Scenario Manager to test multiple business outcomes simultaneously. This advanced technique lets you compare profit projections, budget allocations, or sales forecasts by switching between predefined variable sets, enabling data-driven decision-making without altering your original data.

Why This Matters

What-if scenarios are essential for financial planning, risk analysis, and strategic forecasting in business. They enable professionals to model multiple outcomes before committing to decisions, reducing uncertainty and improving stakeholder confidence.

Prerequisites

•Proficiency with formulas (SUM, IF, basic spreadsheet functions)
•Understanding of cell references and data table structure
•Familiarity with Excel's Data menu and analysis tools

Step-by-Step Instructions

Set up your base model with formulas

Create your spreadsheet with input cells (assumptions) and output cells (results calculated via formulas). Clearly label each variable and ensure all outputs depend on input cells using cell references.

Access Scenario Manager

Navigate to Data > What-If Analysis > Scenario Manager (Excel ribbon). This tool appears in the Analysis section of the Data tab.

Add first scenario

Click Add in Scenario Manager, name your scenario (e.g., 'Best Case'), select changing cells (input ranges), and enter values for each variable. Click OK to save.

Create additional scenarios

Repeat the Add process for alternative scenarios (e.g., 'Worst Case', 'Most Likely'). Each scenario captures a different set of assumptions in the same changing cell ranges.

View and compare scenarios

Select a scenario in Scenario Manager and click Show to apply those values to your spreadsheet. Use Summary to generate a comparison table showing all scenarios' results side-by-side.

Alternative Methods

Data Tables for Two-Variable Analysis

Use Data > What-If Analysis > Data Table for simpler comparisons of one or two variables without saving multiple scenarios. Ideal for sensitivity analysis with limited variable changes.

Goal Seek for Single Outcome Targets

Use Data > What-If Analysis > Goal Seek to work backward from a desired result. Find what input value produces a specific output rather than comparing multiple pre-set scenarios.

Manual Scenario Comparison with Formulas

Create separate columns using INDEX or CHOOSE functions to display different scenarios without Scenario Manager. Offers more flexibility but requires more manual setup.

Tips & Tricks

✓Name your changing cells clearly (e.g., 'Units_Sold', 'Price_Per_Unit') so scenarios remain understandable weeks later.
✓Always create a 'Base Case' scenario first to preserve your original assumptions before experimenting with alternatives.
✓Use the Summary feature to auto-generate a professional comparison table that updates when you modify scenarios.

Pro Tips

★Link Scenario Manager with PivotTables: Create a PivotTable after generating a summary to further analyze scenario outcomes by category or dimension.
★Use descriptive scenario names with assumptions in parentheses (e.g., 'Conservative (+5% growth, -10% margin)') for quick reference without re-opening the manager.
★Combine with data validation: Restrict changing cells to specific ranges using Data > Validity to prevent invalid scenario entries.

Troubleshooting

Scenario Manager button is grayed out

Ensure you've selected the worksheet containing your data model first. The tool requires an active worksheet with formulas. If using merged cells, unmerge them as they can prevent manager activation.

Summary report shows incorrect values

Verify that all result cells (those you want shown in summary) contain formulas, not static values. Re-create the summary and specify the correct result cell range in the Summary dialog.

Changing cells won't accept new values in a scenario

Check that cells aren't locked or protected at the sheet level (Tools > Protect Sheet). Unprotect the sheet temporarily to add scenarios, then reprotect if needed.

Scenario values revert after switching scenarios

This is normal; Excel returns to the original state when you click Show for a different scenario. To preserve a scenario's values, manually copy them or create a new scenario capturing those numbers.

Related Excel Formulas

IF SUM INDEX CHOOSE SUMIF

Frequently Asked Questions

Can I create more than 3 scenarios?

Yes, you can create unlimited scenarios in Scenario Manager. Each scenario saves a complete set of values for your changing cells, allowing you to model dozens of different outcomes.

Does Scenario Manager work with protected sheets?

You cannot add or edit scenarios on a protected sheet. Unprotect the sheet via Tools > Protect Sheet, add your scenarios, then reprotect if needed. Your scenarios will remain intact.

How do I share scenarios with colleagues?

Save the workbook with scenarios as a shared file in OneDrive or SharePoint. Colleagues can then open it and view/show scenarios. Scenario data is saved within the workbook file itself, not separately.

Can scenarios include formulas as changing values?

No; changing cells must contain numbers or text values, not formulas. However, your output cells should use formulas that reference these changing cells to calculate results dynamically.

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

Scenario Manager saves named scenario sets for multiple variables and shows results in a summary. Data Table is simpler, creating a grid showing how one or two variables affect a single output, ideal for sensitivity analysis.