Excel What-If Analysis lets you change assumptions in a worksheet and see how those changes affect a result. In Microsoft 365 for Windows, find its main tools under Data > What-If Analysis: Goal Seek, Scenario Manager, and Data Tables.
What Excel What-If Analysis means
What-If Analysis is a way to explore a spreadsheet model without rebuilding it for every possible assumption. You start with input cells, use formulas to calculate an outcome, and then test different values for those inputs. The result might be profit, a project deadline, a staffing requirement, a grade, or another quantity your workbook calculates.
For example, a simple business model might calculate profit from units sold, sale price, variable cost per unit, and fixed costs. Changing the unit count gives a different profit. What-If Analysis tools help automate that exploration: one tool can find an input needed to reach a specified result, another can compare named sets of assumptions, and a third can calculate results for a range of inputs.
This feature is not the same thing as Excel’s IF function. IF makes a formula return one value when a condition is true and another when it is false; What-If Analysis changes or evaluates model inputs. With a pass mark of 70 in cell B2 and a score of 82, the formula =IF(B2>=70,"Pass","Review") returns Pass. The cell layout for this example is a score in B2 and the formula in another cell. You can use IF inside a model, but it is not one of the three What-If Analysis tools.
Where to find the tools
In Excel for Microsoft 365 on Windows, open the Data tab and look in the Forecast group for What-If Analysis. The menu contains Goal Seek, Scenario Manager, and Data Table. If the ribbon is simplified or the Excel window is narrow, the command may be grouped or shown as an icon; expanding the ribbon or widening the window can make it easier to locate.
On a Mac, Excel also provides these tools from the Data tab, though their placement can vary with the version and ribbon layout. Excel for the web does not offer the complete desktop What-If Analysis experience for creating and managing these tools. If a workbook already contains a data table or scenario setup, web behavior and editing options may be limited; use the desktop app for the full set of commands.
Build a model before testing it
These tools depend on a working model. Keep assumptions in identifiable input cells and make the result cell depend on those inputs through formulas. For example, place units in B2, sale price in B3, variable cost per unit in B4, fixed costs in B5, and profit in B6. With 100 units, a price of 20, a variable cost of 12, and fixed costs of 500, enter =B2*(B3-B4)-B5 in B6. The formula returns 300: 100 multiplied by the 8 difference between price and variable cost, minus 500.
Use clear labels beside the inputs, and avoid mixing assumptions with calculated outputs. Before relying on results, check that the model responds sensibly when you temporarily change an input. A wrong reference, a text value where a number is expected, or a formula that does not depend on the intended input can make What-If results misleading.
Goal Seek: find one input for a target
Goal Seek works backward from a desired result. You specify a formula cell, a target value, and one input cell Excel may change. To use it, choose Data > What-If Analysis > Goal Seek. In the dialog, set Set cell to the formula result, enter the desired number in To value, and select the input in By changing cell. The result cell must contain a formula, and the changing cell must be an input that affects it.
Using the model above, suppose B6 contains the profit formula and you want profit of 1,000 by changing units in B2. Goal Seek changes B2 to 187.5. That result checks out: 187.5 units multiplied by 8 contribution per unit, less 500 fixed costs, equals 1,000. In a real model, fractional units may not be practical; Goal Seek does not impose a whole-number restriction or other business rules for you. You would need to interpret the result and decide how to handle those constraints.
Goal Seek is useful for questions such as how many units must be sold to reach a profit target, what score would produce a target average, or how many hours are needed to complete a task by a deadline. It changes one input cell at a time. It is not a general-purpose optimizer: it does not let you set multiple changing cells or specify constraints such as minimums and maximums. If you need those capabilities, Excel’s Solver add-in may be more suitable.
Scenario Manager: compare named sets of inputs
Scenario Manager stores different sets of values for selected changing cells. You might create scenarios called Conservative, Expected, and High demand, each with different values for units sold, price, and costs. Open Data > What-If Analysis > Scenario Manager, add a scenario, identify the changing cells, and enter the values for that case. Repeat for other cases, then select a scenario to display its assumptions in the worksheet.
Scenario Manager is helpful when you want to preserve and revisit a small number of meaningful combinations. Unlike changing one value repeatedly by hand, named scenarios make it clearer which assumptions belong together. You can also create a scenario summary report to compare the resulting outputs. A scenario can include up to 32 changing cells, so it is intended for a manageable set of inputs rather than a very large simulation.
Be careful about what a scenario represents. A “high” case is only meaningful if its assumptions are internally consistent. For instance, higher sales volume might require higher staffing or shipping costs. Include the relevant changing cells in the scenario, or the output may look more favorable than the assumptions justify. Scenario Manager also does not assign probabilities to outcomes; it organizes cases but does not tell you how likely each case is.
Data Tables: calculate a range of possibilities
A What-If Data Table calculates a formula repeatedly using one or two input variables. It is different from an ordinary Excel table: it is a What-If Analysis feature that substitutes trial values into a model input and records the resulting output. Choose Data > What-If Analysis > Data Table after arranging the trial values and formula in the required grid.
For a one-variable table using the profit model, put trial unit counts 80, 100, and 150 in A10:A12, and put =B6 in B9. With that formula in B9, the current model returns 300 because B2 is 100; the cell layout assumes the model inputs and profit formula remain in B2:B6 as described above. Select A9:B12, open Data Table, and set the Column input cell to B2, the units input. Excel calculates profits of 140, 300, and 700 beside the three trial values, respectively.
For a two-variable table, put one set of trial values across the top row and another down the first column, with the output formula at their intersection. In the Data Table dialog, identify the model input corresponding to the row values and the one corresponding to the column values. For example, you could test several unit counts against several sale prices while keeping costs fixed. Excel fills the grid with the result for each pair. A two-variable table can show a useful range, but it cannot vary three or more inputs at once.
Data Tables are a good choice when you want to compare many values systematically, such as the effect of different prices and sales volumes. Large tables can make recalculation slower, particularly in complex workbooks. Excel’s What-If Data Tables also have special calculation behavior, so if results do not update as expected, check the workbook’s calculation settings and confirm that the table is arranged correctly.
Choosing the right approach
Pick the tool based on the question you are asking, not just on the number of cells in the worksheet:
- Use Goal Seek when you know the target result and want Excel to find one input value that reaches it.
- Use Scenario Manager when you have a few named cases, each combining several input assumptions.
- Use a Data Table when you want a grid of calculated results for a range of one or two inputs.
- Use formulas or ordinary worksheet tables when you need a custom calculation that does not fit those patterns, or want results displayed in a format you control.
- Consider Solver when the problem needs multiple changing cells or constraints, such as maximizing an outcome while keeping resource use below a limit.
These methods answer different questions. A Goal Seek result is one solution for one target; a Scenario Manager comparison is a set of discrete cases; a Data Table is a grid of trial results. None automatically predicts what will happen in the real world. The quality of the answer depends on the quality of the model and the assumptions you supply.
Common problems and useful checks
If Goal Seek reports that it cannot find a solution, check that the selected result cell contains a formula and that changing the selected input actually affects that formula. The target may be unreachable, the model may contain a discontinuity, or the starting value may make it difficult for Excel to find a useful result. Try a sensible starting value and verify the model manually. Goal Seek is a numerical search, not proof that a unique or practical solution exists.
If a Data Table shows repeated or unexpected results, verify the position of the formula and input values, and confirm that the row or column input cell is the actual model input. Also check for hard-coded values in the formula chain: if the output formula does not depend on the chosen input, changing that input cannot alter the result. For scenarios, review the changing-cell list and ensure each saved case contains the intended assumptions.
Keep a copy of important original assumptions before running analyses. Goal Seek changes a worksheet input, and selecting a different Scenario Manager case changes displayed inputs. Record what each result means, including units and assumptions. A number such as 187.5 is not self-explanatory unless the sheet makes clear that it represents units sold and the other model inputs remain fixed.
Frequently Asked Questions
Is Excel What-If Analysis the same as the IF function?
No. The IF function returns values according to a logical test in a formula. What-If Analysis refers to tools that test input values against a model, including Goal Seek, Scenario Manager, and Data Tables.
Where is What-If Analysis in Excel?
In Excel for Microsoft 365 on Windows, open the Data tab and look in the Forecast group for What-If Analysis. Its menu includes Goal Seek, Scenario Manager, and Data Table.
Can Goal Seek change more than one cell?
No. Goal Seek changes one input cell to reach a target in a formula cell. For a model with multiple changing cells or constraints, investigate Solver instead.
Can a Data Table test more than two variables?
No. A What-If Data Table evaluates one or two input variables. To explore more variables, use separate scenarios, formulas, or a suitable modeling approach.
Does What-If Analysis predict the most likely outcome?
No. It calculates results from the inputs you provide. It does not judge whether assumptions are realistic or assign probabilities to scenarios; that interpretation is up to the person using the model.
Related
- Excel Analysis ToolPak: What It Does and How to Use It
- Analysis ToolPak in Excel: How to Enable and Use It
- Analysis ToolPak in Excel: What It Does and How to Enable It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Color Filter: How to Filter Rows by Cell ColorTo filter rows by color in Excel for Microsoft 365 on Windows, select a cell in your data,…
- How to Lock a Filter in Excel Without Losing ControlTo keep an Excel filter in place while protecting the worksheet, set up the filter first, then choose…
- Show Formulas in Excel: Steps for Windows, Mac, and WebTo show formulas instead of calculated results in Excel, open the Formulas tab and select Show Formulas in…
- SUMPRODUCT in Excel: Multiple Criteria ExplainedYes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example,…
- Excel Flash Fill: Fill a Column Without a FormulaExcel Flash Fill is not a formula: it recognizes a pattern in examples you type and fills the…
- Excel Formula to Check Yes or No in Another ListYes. To show whether a value in one cell appears anywhere in another list, use COUNTIF inside IF.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.