What If Analysis in Excel: Full Guide

Excel’s What-If Analysis tools let you test different input values in a formula to see how they change the result, without rewriting the formula each time. You can use three built-in features—Scenario Manager, Goal Seek, and Data Tables—to explore possibilities, find a required input, or compare many combinations at once. This article explains what each tool does, when to use it, and how to get the most out of it.

Background: Why What-If Analysis Exists

Spreadsheets are built on formulas that turn inputs into outputs. In everyday work, you often need to ask “what if” questions: What if sales drop by 10%? What if interest rates rise? What if I need to save $50,000 in five years? Manually changing cells and re-checking results is slow and error-prone, especially with complex models. Excel’s What-If Analysis tools automate this process, letting you run multiple scenarios quickly and see the outcomes side by side.

The three tools serve different purposes:

• Scenario Manager – stores and switches between different sets of input values (scenarios) and shows the resulting outputs.
• Goal Seek – works backward from a desired result to find the input value that produces it.
• Data Tables – calculate multiple results at once by substituting a range of values into one or two input cells.

These tools have been part of Excel for decades and remain in current versions, including Microsoft 365 and Excel 2021. They are found on the Data tab, in the Forecast group (or What-If Analysis dropdown, depending on version).

How Each Tool Works

Scenario Manager

Scenario Manager lets you define named sets of input values. For example, a small business might create a “Best Case” scenario (sales 120,000, costs 80,000) and a “Worst Case” scenario (sales 90,000, costs 95,000). You can switch between them to see the effect on profit.

1. Go to Data > What-If Analysis > Scenario Manager.
2. Click Add, give the scenario a name, and select the cells that will change (the input cells).
3. Enter values for each changing cell and click OK.
4. Repeat to add more scenarios.
5. Select a scenario and click Show to apply it to the worksheet.

You can also generate a summary report that lists all scenarios and their resulting values. This is useful for presentations or for comparing outcomes without manually toggling.

Goal Seek

Goal Seek answers the reverse question: “What input do I need to get a specific output?” For instance, if you want a loan payment of $500 per month and the rate is fixed, you can ask Goal Seek to find the loan amount that yields exactly $500.

1. Set up a formula that calculates the output (e.g., monthly payment).
2. Go to Data > What-If Analysis > Goal Seek.
3. In Set cell, enter the cell with the formula.
4. In To value, type the target result (e.g., 500).
5. In By changing cell, enter the input cell you want Excel to adjust.
6. Click OK. Excel iterates until it finds a solution or gets close.

Goal Seek works only with one input cell and one formula. It can handle formulas that are not linear, but it may not find a solution if none exists or if the starting value is far off.

Data Tables

Data Tables are the most powerful for exploring many possibilities at once. They let you substitute a list of values into one or two input cells and see the corresponding outputs in a table.

• One-variable table: Vary one input (e.g., interest rate) and see how it affects one or more outputs (e.g., monthly payment, total interest).
• Two-variable table: Vary two inputs (e.g., rate and loan term) and see how they affect one output.

To create a one-variable table:

1. In a column, list the values you want to test (e.g., 3%, 4%, 5%).
2. In the row above the list, enter the formula that references the input cell (e.g., =PMT(B1/12, B2*12, -B3)).
3. Select the range that includes the formula and the list.
4. Go to Data > What-If Analysis > Data Table.
5. In Column input cell, enter the cell that the formula uses for the varying input (e.g., B1). Leave Row input cell blank.
6. Click OK. Excel fills the table with results.

For a two-variable table, place one set of values in a row and the other in a column, and put the formula in the top-left cell. Then specify both row and column input cells.

Choosing the Right Tool

Picking the right tool depends on your question:

• If you want to compare a few distinct sets of inputs (e.g., best/worst/expected), use Scenario Manager.
• If you need to find a single input value that produces a target output, use Goal Seek.
• If you want to see a range of outcomes across many input values, use Data Tables.

A common mistake is using Goal Seek when a Data Table would be better. Goal Seek gives one answer; a Data Table gives a whole picture. For example, if you want to know how different interest rates affect your payment, a Data Table shows the full range, while Goal Seek only finds the rate for a specific payment.

Common Problems and How to Avoid Them

What-If Analysis tools can be finicky. Here are the most frequent issues:

• Data Table not recalculating: If you change input values in the table, the results may not update automatically. Press F9 to recalculate, or set calculation to automatic (Formulas > Calculation Options).
• Goal Seek can’t find a solution: The formula may not be able to reach the target with any input, or the starting value is too far from the answer. Try a different starting value or check the formula for errors.
• Scenario Manager not showing changes: The scenario must reference the correct changing cells. If you insert or delete rows, the references may break.
• Data Table with multiple formulas: For a one-variable table, you can put several formulas in the row above the values, but they must all reference the same input cell. For a two-variable table, only one formula is allowed at the top-left.

Another common issue is forgetting that Data Tables are array formulas. You cannot edit individual cells in the results; you must select the whole table and delete it to make changes.

Practical Examples

Let’s walk through two realistic examples.

Example 1: Loan Payment Sensitivity
You have a loan of $20,000 at 5% annual interest over 5 years. The monthly payment formula is =PMT(5%/12, 5*12, -20000). To see how the payment changes with interest rates from 3% to 7%, create a one-variable Data Table with rates in a column, the formula above them, and the column input cell set to the rate cell (e.g., B1). The table will show payments for each rate.

Example 2: Break-Even Sales
Your profit formula is =Sales - Costs. You want to know how many units you must sell to break even (profit = 0). Use Goal Seek: set the profit cell to 0 by changing the units sold cell. Excel will find the exact number.

Limitations and Alternatives

What-If Analysis tools have limits. Goal Seek only handles one variable; for multiple variables, you need Solver (an add-in). Data Tables are limited to one or two inputs; for more, you’d need to build a custom model or use a macro. Scenario Manager is manual—it doesn’t automatically explore a range, just named sets.

If you need more advanced analysis, consider:

• Solver – an add-in that finds optimal values subject to constraints (e.g., maximize profit with limited resources).
• Power Query / Power Pivot – for large data sets and complex modeling.
• Manual formulas with dropdowns – using data validation to switch inputs, which is simpler for small models.

Also note that What-If Analysis works with any formula, but the results are only as good as your model. Always double-check that your formulas are correct and that you’re using the right input cells.

Frequently Asked Questions

Can I use What-If Analysis with multiple formulas?

Yes. Scenario Manager can show results for any number of formulas, and a one-variable Data Table can include multiple formulas in the top row. Goal Seek only works with one formula at a time, but you can run it repeatedly for different outputs.

Why is my Data Table showing the same value for all rows?

This usually means the column input cell is not correctly referenced in the formula, or the table range doesn’t include the formula cell. Make sure the formula in the top-left of the table references the input cell you specify in the Data Table dialog.

Does Goal Seek work with circular references?

No. Goal Seek cannot handle circular references (where a formula depends on its own result). It will return an error. You must break the circular reference first.

Can I undo What-If Analysis changes?

Yes. Scenario Manager changes are applied to the worksheet, but you can press Ctrl+Z to undo the last action. Goal Seek also can be undone. Data Tables, however, are array formulas; to remove them, select the entire table and press Delete.

Is What-If Analysis available in Excel Online or Google Sheets?

Excel Online has limited support: Goal Seek is available, but Scenario Manager and Data Tables are not. Google Sheets has a similar feature called “What-if” in the menu, but it works differently—it creates a side panel with scenarios. For full functionality, use the desktop version of Excel.

Related

• Smartphone Radiation: Real Risks & SAR Levels
• Valve Index Advantages Over Newer VR Headsets
• Show Developer Tab in Excel Ribbon (Windows & Mac)