Excel what-if analysis helps you see how changing input values affects a formula’s result. For a quick target, use Data > What-If Analysis > Goal Seek; to compare saved sets of assumptions, use Scenario Manager; and to test many input combinations at once, use a Data Table.
What Excel What-If Analysis Does
What-if analysis is a way to explore possibilities in a spreadsheet without replacing your working assumptions with guesses. You change one or more inputs, then observe what happens to a result such as profit, a monthly payment, a project deadline, or inventory remaining. The value comes from a model whose formulas connect inputs to outputs: Excel can only show meaningful consequences if those relationships are represented correctly.
Excel’s built-in What-If Analysis menu groups three tools: Goal Seek, Scenario Manager, and Data Tables. They address different questions. Goal Seek asks what one input must be to produce a chosen result. Scenario Manager stores and compares discrete groups of input values. A Data Table calculates results across a row or column of trial values, or across combinations of two inputs.
These tools do not forecast the future or decide which assumptions are realistic. They calculate the results implied by the values you supply. Treat the output as a model result, then check whether the inputs and formula make sense for the decision you are considering.
Choose the Right Tool for the Question
Start by phrasing the question precisely. If you know the desired result and want to solve for one input, Goal Seek is usually the most direct choice. If you want to compare a few named cases—such as low, expected, and high demand—Scenario Manager keeps those cases together. If you want to see how many possible values affect an output, a Data Table is more convenient.
- Goal Seek: Find one input that makes a formula reach a specified value. It changes one cell at a time.
- Scenario Manager: Save several sets of input values and switch between them or summarize their results.
- Data Table: Calculate a formula repeatedly for a list of values in one input cell, or for combinations of two input cells.
For example, “How many units must we sell to earn $5,000?” suits Goal Seek. “How do low, expected, and high sales assumptions compare?” suits Scenario Manager. “What profit results from each combination of price and units?” suits a two-variable Data Table. If the problem requires optimizing an objective subject to several constraints, consider Solver instead; it is a separate Excel add-in, not one of the three What-If Analysis tools.
Use Goal Seek to Find a Target Input
Goal Seek works backward from a formula result. First make sure the output cell contains a formula that depends on the input cell you want Excel to change. For a simple profit model, assume B2 contains units sold, B3 contains price per unit, B4 contains variable cost per unit, and B5 contains fixed costs. Enter 100, 50, 30, and 2000 in those cells respectively. With profit calculated in B8, the formula for B8 is =B2*(B3-B4)-B5. At the starting values, the result is 100 × (50 − 30) − 2,000 = 0.
To find the units needed for a profit of 5,000, go to Data > What-If Analysis > Goal Seek. In Set cell, select B8; in To value, enter 5000; in By changing cell, select B2; then choose OK. With the other assumptions unchanged, Excel returns 350 units: 350 × (50 − 30) − 2,000 = 5,000. Accept the result to keep it in the worksheet, or cancel to restore the previous input.
Goal Seek is useful for questions such as a required sales volume, a target grade, or a break-even price. It adjusts one changing cell, so it cannot directly solve for a price and a sales volume at the same time. It also does not impose business rules such as “units must be whole numbers” or “price cannot exceed a limit.” Check the answer against those rules and round only if doing so still produces an acceptable result.
Compare Named Cases with Scenario Manager
Scenario Manager is intended for a small set of distinct assumptions rather than a long sweep of values. In the Windows desktop version of Excel for Microsoft 365, open Data > What-If Analysis > Scenario Manager. Choose Add, give the case a useful name, and identify the changing cells. Enter the assumptions for that case, then add other named cases using the same input cells. Select a case and choose Show to place its values in the worksheet.
In the profit example, the changing cells might be B2 and B3: units and selling price. You could save a cautious case with 250 units at $45, a planning case with 350 units at $50, and a stronger case with 450 units at $52. Fixed costs and variable cost remain unchanged unless you include them as changing cells. Comparing the resulting profit makes clear which assumptions drive the difference.
Scenario Manager can create a Scenario Summary report that compares saved cases and selected result cells. Give those result cells clear labels before generating the report, and remember that the displayed scenario values are alternatives—not simultaneous recommendations. Scenario Manager is best for a handful of meaningful cases; it allows up to 32 changing cells per scenario, so it is not designed to store a large model with hundreds of independent inputs.
Build One- or Two-Variable Data Tables
A Data Table is useful when you want a grid of results rather than a few saved cases. In a one-variable table, Excel substitutes a list of values into one model input and records the output for each. In a two-variable table, Excel tests combinations from a row and a column of values. These are What-If Analysis tables, not the formatted Excel Tables created with Insert > Table.
For a two-variable profit table, keep units in B2, price in B3, variable cost in B4, fixed costs in B5, and profit in B8 as above. Put the formula reference =B8 in D4, the upper-left corner of the analysis grid. Put trial prices such as 40, 45, 50, and 55 in E4:H4, and trial unit counts such as 100, 200, 300, 350, and 400 in D5:D9. Select D4:H9, then choose Data > What-If Analysis > Data Table. Set Row input cell to B3, because the trial values across the top represent price; set Column input cell to B2, because the values down the first column represent units. Choose OK.
Each interior cell now shows the profit for its row’s unit count and column’s price, while the other model inputs stay fixed. At 350 units and a price of $50, the result is 5,000, matching the Goal Seek example. The orientation matters: values across the top correspond to the Row input cell, while values down the first column correspond to the Column input cell. If you swap those references, the table may calculate but answer a different question.
For a one-variable table, place the output formula above a vertical list or to the left of a horizontal list, select the whole range, and provide only the matching row or column input cell in the Data Table dialog. A Data Table can use only one or two input cells. Keep the grid focused on the key decision variables; a very large grid can make a workbook slower to recalculate.
Prepare the Model Before Testing Assumptions
A useful scenario starts with a clear, auditable model. Separate inputs from formulas, label units, and make the output easy to identify. For instance, distinguish dollars from percentages and monthly values from annual ones. Avoid typing an assumption directly into a formula when it should be a changeable input: a value stored in a labeled cell is easier to inspect and test.
Before running a tool, make a copy of the workbook or record the original inputs. Then check that the output formula references the intended input cells and that all relevant costs or constraints are represented. Test the model with an easy case whose answer you can calculate independently. If the formula produces an implausible result under familiar values, fix the model before relying on scenario comparisons.
Use realistic ranges. A sensitivity table with prices from $40 to $55 is more useful than a grid extending to values no one would consider. Include a baseline case so readers can compare changes with the current plan, and label the assumptions clearly in any report or chart. For decisions with uncertainty, test more than one important driver when practical; a result that changes sharply with a small input adjustment deserves extra scrutiny.
Common Problems and How to Fix Them
- Goal Seek will not run: Confirm that Set cell contains a formula and that the formula depends, directly or indirectly, on the changing cell. Goal Seek cannot change a fixed value and make an unrelated formula respond.
- The output does not reach the target: Check whether the target is possible under the model. Goal Seek may not find a sensible solution if the formula has limits, a discontinuity, or no value that reaches the requested result.
- A Data Table repeats one value: Verify that the top-left cell references the output formula and that row and column input cells match the orientation of the trial values. Also confirm that the formula actually depends on those inputs.
- Scenario results appear to replace current assumptions: Choosing Show loads that scenario’s inputs into the worksheet. Restore the original inputs or show the baseline scenario when you are finished.
- Results seem inconsistent: Check number formats, units, formulas, and calculation settings. A percentage stored as 10% is numerically different from the number 10, and a formatted cell can hide the underlying value.
On Windows, these tools are on the Data tab under What-If Analysis. Excel for Microsoft 365 on Mac can have a different ribbon layout, so use the Data tab or Excel’s command search to locate the relevant tool. Excel for the web may not expose the same desktop controls; if a command is missing, open the workbook in the desktop app rather than assuming the model itself is broken.
When Solver or Another Method Is a Better Fit
Use Solver when you need to maximize or minimize an objective while changing multiple decision cells and observing constraints—for example, choosing product quantities subject to labor and material limits. Solver is an add-in that may need to be enabled, and its setup differs from Goal Seek. Goal Seek is simpler when there is one target formula and one adjustable input; it does not provide Solver’s general constraint-based optimization.
For a short list of cases, ordinary worksheet formulas can also calculate outputs in parallel without changing the model’s inputs. That can be easier to review when each case has a distinct set of assumptions. For a large simulation or probabilistic risk model, Excel’s three What-If Analysis tools may not be enough on their own; the right approach depends on how the assumptions vary and how much analysis is required.
Frequently Asked Questions
Where is What-If Analysis in Excel?
In Excel for Microsoft 365 on Windows, look on the Data tab and choose What-If Analysis. The menu contains Goal Seek, Scenario Manager, and Data Tables. Ribbon layout and feature availability can differ on Mac or the web version.
What is the difference between Goal Seek and a Data Table?
Goal Seek changes one input to find a specified output. A Data Table calculates an output for a list of one input’s values or combinations of two inputs; it does not search for one target answer.
Can Goal Seek change more than one cell?
No. Goal Seek adjusts one changing cell. If several cells must vary together, compare saved assumptions with Scenario Manager or investigate Solver when the task involves optimization and constraints.
Why is my Data Table blank or incorrect?
Check that the upper-left cell of the selected range contains a reference to the result formula, that the trial values are arranged in the intended row or column, and that you selected the correct input cell in the Data Table dialog.
Does What-If Analysis predict what will happen?
No. It calculates results from your formulas and the assumptions you enter. It does not validate assumptions or account for unknown events unless those factors are explicitly built into the model.
Related
- Why Is My Excel File Corrupted? Causes and Fixes
- Why Can’t I Hyperlink in Excel? Common Causes and Fixes
- Why Use Scenario Manager in Excel? Compare Outcomes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Drop-Down Shortcut: Windows and MacIn Excel for Microsoft 365 on Windows, select a cell in the filtered column’s header and press Alt+Down…
- How to Merge Two Columns in Excel With Text Using a FormulaTo combine text from two Excel columns, enter a formula such as =A2&" "&B2 in a new column,…
- Long Invoice Template Excel: Layout and FormulasA long invoice template in Excel is a worksheet designed to handle many billable lines while keeping the…
- Split Lung Function Test: How It Measures Each LungA split lung function test estimates how much each lung contributes to breathing, rather than measuring the lungs…
- SUMIF a Date Between Two Dates in ExcelIf your dates are in A2:A100, the values to add are in B2:B100, and the start and end…
- Sort an Excel Column Highest to Lowest: Quick StepsTo sort numbers in Excel from highest to lowest, select a cell in the column and choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.