TL;DR: What-If Analysis in Excel lets you test how changing inputs affects a formula’s result without touching your real data. Use Goal Seek to work backwards from a target by changing one input, Scenario Manager to save and compare whole sets of assumptions, Data Tables to see how one or two inputs move a result across a range, and the Solver add-in for harder problems with several inputs and limits. All three built-in tools live under Data → Forecast → What-If Analysis.
What-If Analysis in Excel lets you test how changing one or more inputs affects a formula’s result, without disturbing your real data. It sits under Data → Forecast → What-If Analysis and gives you three tools — Goal Seek, Scenario Manager and Data Tables — plus the Solver add-in for tougher problems. In my 24 years of training in Singapore, I have watched managers make this same request again and again: “Tell me what happens to the numbers if this one figure changes.” This guide shows you how to answer that, step by step.
Want to practise this with a trainer in the room? Our Excel Training course in Singapore covers Goal Seek, Scenario Manager, Data Tables and Solver — WSQ-funded, SkillsFuture-eligible, taught by ACTA-certified trainers.
What is What-If Analysis in Excel?
What-If Analysis in Excel is a set of tools that answer one question: “what happens if this number changes?” You keep your real model untouched and let Excel do the recalculating. So you can compare outcomes before you commit to a decision.
Think of it like a flight simulator. A pilot tries a steep landing in the simulator, crashes, walks away, and tries again — all without bending a real aircraft. What-If Analysis is that simulator for your spreadsheet. You can fly the worst case, see the wreckage, and nobody loses real money.
Excel ships with three built-in What-If Analysis tools:
- Goal Seek — you know the answer you want; Excel finds the input that gets you there.
- Scenario Manager — you save several sets of inputs (best case, base case, worst case) and switch between them.
- Data Tables — you watch how one or two inputs change a result across a whole range of values at once.
There is also the Solver add-in. Think of it as Goal Seek with more muscle: it can change several inputs at the same time and respect limits you set. Together these cover most of the day-to-day data analysis a working professional needs in a spreadsheet.
Where to find What-If Analysis (and which tool to use when)
All three built-in tools live in the same place. Go to the Data tab, find the Forecast group, and click What-If Analysis. A menu drops down with Scenario Manager, Goal Seek and Data Table.
The harder question is which tool to reach for. Use this quick guide:
| Your question | Best tool |
|---|---|
| “I know the result I want — what input gets me there?” (one input) | Goal Seek |
| “I know the result I want, but several inputs can move and there are limits” | Solver |
| “How do three or four full sets of assumptions compare?” | Scenario Manager |
| “How does one input change the result across many values?” | One-variable Data Table |
| “How do two inputs interact across a grid of values?” | Two-variable Data Table |
Here is a simple rule of thumb. One or two variables: use a Data Table. More than two full sets of assumptions: use Scenario Manager. Working backwards from a target you already know: use Goal Seek or Solver. Keep this table handy while you read the rest of the guide.
How to use Goal Seek
Goal Seek works backwards. You tell Excel the result you want in a formula cell, point it at one input cell, and it finds the input value that produces that result. The classic case is a loan.
Suppose you are borrowing $300,000 over 240 months and cell B4 calculates the monthly repayment with the PMT function: =PMT(B3/12, B2, -B1), where B1 is the loan amount, B2 the number of months, and B3 the annual interest rate. You can afford $1,800 a month. What interest rate do you need?
1. Click the Data tab → What-If Analysis → Goal Seek.
2. In Set cell, choose B4 (the repayment formula).
3. In To value, type 1800 (the repayment you can afford).
4. In By changing cell, choose B3 (the interest rate).
5. Click OK. Excel adjusts B3 until B4 reads 1800 and shows you the rate.
That is it. No trial and error, no nudging the rate up and down by hand. If you want the official reference, Microsoft’s own Goal Seek guide walks through the same dialog box.
Goal Seek only ever changes one cell. If you need to move two or more inputs at once — say the loan amount and the term together — that is a job for Solver, which we cover below.
How to use Scenario Manager
Scenario Manager is for comparing whole sets of assumptions side by side. Instead of one input, you save several named “scenarios”, each with its own values, then switch between them or print a summary.
Say you run a small training budget and want to model three futures: a best case where enrolment is high, a base case, and a worst case where a venue cost rises.
1. Set up your model so the inputs (enrolment, venue cost, materials cost) sit in their own cells and your profit formula reads from them.
2. Go to Data → What-If Analysis → Scenario Manager.
3. Click Add. Name it “Best Case”, choose the changing cells, and click OK.
4. Type the values for that scenario, then OK.
5. Repeat Add for “Base Case” and “Worst Case”.
6. Select any scenario and click Show to push its values into the sheet.
To compare them all at once, click Summary in the Scenario Manager box, pick the result cells you care about (such as profit and total cost), and Excel builds a Scenario Summary Report on a new sheet. How good is that? One scenario can change up to 32 cells, and you can store as many scenarios as you like. This is a strong choice for budgeting, where your boss will almost certainly ask for the best- and worst-case numbers on the same page. Managers can be quite demanding, and they usually want it before lunch.
How to use Data Tables (one- and two-variable)
A Data Table answers “how does the result move as this input changes?” across a whole range, in one grid. There are two kinds.
One-variable Data Table. Suppose B4 holds your monthly loan repayment and you want to see it at interest rates from 2% to 6%.
1. Type the interest rates down a single column, for example A2:A6.
2. In the cell one row up and one column to the right of the first rate, link to the formula: =B4.
3. Select the block that contains both your rates and that formula link.
4. Go to Data → What-If Analysis → Data Table.
5. Because the rates run down a column, put the interest-rate input cell in Column input cell, then OK.
Press OK. Excel fills the repayment for every rate at once.
Two-variable Data Table. Now you want the repayment for several interest rates and several loan terms together.
1. Put one set of values down a column (interest rates) and the other across a row (terms).
2. In the top-left corner cell of that grid, link to the formula: =B4.
3. Select the whole grid.
4. Open Data → What-If Analysis → Data Table.
5. Set Row input cell to the term cell and Column input cell to the interest-rate cell. Click OK.
Excel returns a full grid of results. This is the same thing many people search for as “how to use excel what if analysis data table” — a one or two-variable grid that updates by itself. A Data Table is capped at two variables. For more than that, switch back to Scenario Manager. If you also need to total or look values up inside that grid, our walkthrough on SUMIF and SUMIFS in Excel pairs neatly with this one.
How to use Solver for harder problems
Solver is the heavy-duty cousin of Goal Seek. It can change several cells at once, aim for a maximum, minimum or exact value, and obey constraints you set, such as “stay within budget” or “buy whole units only”. It is not switched on by default.
To enable it: File → Options → Add-ins → in the Manage box choose Excel Add-ins → Go → tick Solver Add-in → OK. Solver then appears on the Data tab in the Analyze group.
A typical use is spreading a fixed budget across several options to get the most out of it:
1. Click Data → Solver.
2. In Set Objective, choose the cell you want to optimise (for example, total return).
3. Choose Max, Min or Value Of.
4. In By Changing Variable Cells, select the input cells Solver may adjust.
5. Click Add under Constraints to set rules, such as total spend ≤ your budget.
6. Click Solve, then keep or discard the solution.
When every relationship in your model is a straight line — costs, returns and limits all moving in proportion — pick the Simplex LP method in Solver’s options. That is the setting people mean by “how to use excel solver for linear programming”. For curved relationships, leave it on the default GRG Nonlinear engine.
Excel Training Course in Singapore
Build Goal Seek, Scenario Manager, Data Tables and Solver into real budget models, with an ACTA-certified trainer in the room.
A worked example: one budget, all the tools
Here is how the tools fit together on a single model. Imagine you manage the budget for a Singapore team and your sheet works out quarterly profit from enrolment, course fee and venue cost.
First, use Goal Seek to answer “what fee do we need to break even at current enrolment?” Point Set cell at profit, To value at 0, and By changing cell at the fee.
Next, use Scenario Manager to save Best, Base and Worst cases for enrolment and venue cost, then print a Scenario Summary to bring to your manager. This is exactly the kind of comparison our Excel for HR Professionals participants build for headcount and training budgets.
Finally, use a two-variable Data Table to show profit across a grid of fees and enrolment numbers, so leadership can see the whole landscape at a glance. If your model grows into something with cleaning, reshaping and large data sources, that is where tools like Power Query and Power Pivot and broader data analytics and visualization in Excel take over.
Here is an honest opinion after years of teaching this. Anyone can learn these clicks in a week. The hard part is application — most Singapore professionals have heard of scenario planning, but few have actually built a worst-case model and defended it in a meeting. Doing it once on a real budget is worth more than reading ten guides.
Troubleshooting and common mistakes
A few problems come up again and again with What-If Analysis. I still see them trip people up after all these years.
- Goal Seek finds no solution. The changing cell probably does not feed the Set cell formula, the target is impossible for that one input, or you typed a hard number where a live cell reference should be. Check the formula chain first.
- Data Table will not recalculate. Data Tables recalculate with the workbook. If yours looks frozen, go to Formulas → Calculation Options and make sure it is not set to “Automatic Except for Data Tables”.
- Hard-coded numbers. If your formulas contain typed numbers instead of cell references, none of these tools can move them. Always feed formulas from input cells. The same discipline underpins clean lookups — see our guide on VLOOKUP and XLOOKUP.
- Mixing up the add-ins. What-If Analysis is built in. The Analysis ToolPak (and the related “data analysis toolpak”) is a separate add-in for statistics such as regression and histograms. Useful, but not the same thing, and switched on the same way as Solver.
Get these four right and the tools behave predictably.
Ready to build budget models your manager will actually trust? Our WSQ-funded Excel training in Singapore covers Goal Seek, Scenario Manager, Data Tables and Solver with hands-on practice. SkillsFuture credits accepted.
Frequently asked questions
Where is What-If Analysis in Excel?
It is on the Data tab. Look in the Forecast group and click the What-If Analysis button. A short menu drops down with Scenario Manager, Goal Seek and Data Table.
What is the difference between Goal Seek and Solver?
Goal Seek changes one input cell to hit a target in one formula cell. Solver can change many input cells at once and apply rules such as a budget limit, so it suits harder problems with several moving parts.
What is a Data Table used for in Excel?
A Data Table shows how one or two inputs change a formula’s result, all in a grid. It is ideal for things like seeing the monthly loan repayment across a range of interest rates.
Is What-If Analysis the same as the Analysis ToolPak?
No. What-If Analysis (Goal Seek, Scenario Manager, Data Tables) is built in. The Analysis ToolPak is a separate add-in for statistics such as regression and histograms. You can use both, but they are different tools.
How many variables can Scenario Manager handle?
A single scenario can change up to 32 cells. You can store many scenarios in one worksheet and compare them in a Scenario Summary Report.
Why is my Goal Seek not finding a solution?
Usually the changing cell is not actually feeding the formula in the Set cell, the target is impossible for that input, or the input was typed as a number instead of being a live cell. Check the formula chain and try a target closer to the current value.
Do try it out. Open a budget you already use, pick one figure your manager keeps asking about, and run Goal Seek on it this week. I hope you’ll find these four tools as handy as my training participants do.
