A normal formula runs one direction: you put in the numbers, it gives you the answer. What-If Analysis runs Excel the other ways — backward (“what input gets me this result?”) and sideways (“how does the result change across a range of inputs?”). It’s how you answer the questions that actually drive decisions: what rate makes this loan affordable, how profit moves as price and volume change, which scenario you’re really betting on. Excel has three tools for it — Goal Seek, Data Tables, and Scenario Manager — and each fits a different shape of question.
Goal Seek: work backward to a target
Goal Seek answers “I know the result I want — what input gets me there?” You point it at a formula cell, give it the target, and tell it which input to adjust.
- Go to Data → What-If Analysis → Goal Seek.
- Set cell: the formula result (e.g. the monthly payment); To value: your target (e.g. 500).
- By changing cell: the input to solve for (e.g. the interest rate or loan amount).
Excel iterates until the formula hits your target and tells you the input that did it — “to get a $500 payment, the loan must be $26,300.” It’s perfect for the reverse questions a normal formula can’t answer directly: what score do I need on the final to pass, what price hits my margin goal, what rate meets my budget. Goal Seek handles exactly one variable, which is its limitation and its simplicity. Microsoft’s introduction to What-If Analysis covers all three tools.
One-variable Data Table: one input, many values
A Data Table shows how a result changes across a whole range of inputs at once — not one answer, but a column of them. The one-variable version tests many values of a single input.
- List the input values down a column — interest rates 3%, 4%, … 8%.
- One row up and one column right, put a reference to your result:
=B10(the payment cell). - Select the whole block, then Data → What-If Analysis → Data Table.
- Leave Row input blank; set Column input cell to your rate input cell.
Lay your input values down a column (interest rates from 3% to 8%, say), put a reference to your result formula at the top of the next column, select the block, and use Data → What-If Analysis → Data Table, pointing the “column input cell” at your rate input. Excel fills in the result for every rate — a complete sensitivity table in one step. Instead of changing the rate, noting the payment, changing it again, you see all the outcomes side by side. It’s the fastest way to ask “how sensitive is my answer to this one assumption?”
Two-variable Data Table: a grid of outcomes
The two-variable version is where it gets powerful: test two inputs at once and get a grid showing the result for every combination.
- Put one input’s values down a column, the other input’s values across a row.
- In the corner cell where the row and column meet, put the result formula reference.
- Select the whole grid, then Data → What-If Analysis → Data Table.
- Set Row input cell (for the across values) and Column input cell (for the down values).
Put one input’s values down a column and the other’s across a row, with the result formula in the corner where they meet. Run Data Table with both a row input cell and a column input cell, and Excel fills the whole grid — profit at every combination of price and units sold, or payment at every mix of rate and term. A two-way data table turns “what if both of these change?” into a single readable matrix, which is exactly the question real planning asks. It’s limited to two variables, but within that limit you can test as many values of each as you like.
Scenario Manager: save named sets of inputs
When you have more than two inputs, or want to save and compare whole situations, Scenario Manager is the tool. A scenario is a named set of input values Excel can swap in on demand.
- Data → What-If Analysis → Scenario Manager → Add.
- Name it (“Best case”), pick the input cells, and enter their values for that scenario.
- Repeat for “Worst case” and “Expected,” then Show any scenario to swap its values in.
Each scenario stores up to 32 input values, so you can model genuinely complex situations — a whole budget’s assumptions under optimistic, pessimistic, and likely cases — and flip between them in a click. The Summary button generates a side-by-side report of all your scenarios’ results, which is the deliverable decision-makers actually want: here’s how the numbers look under each set of assumptions. It’s how you present “what if things go well versus badly” without keeping three copies of the workbook. The summary report is the part worth emphasizing: rather than clicking through scenarios one at a time, it lays best, worst, and expected case in adjacent columns on a new sheet, so a manager sees the full range of outcomes at a glance. That single table — same model, three sets of assumptions, results side by side — is often the most persuasive thing you can put in front of a decision, because it makes the uncertainty explicit instead of hiding it behind one confident-looking number.
Watch the gotchas
Two quirks are worth knowing before you lean on these tools. Both are easy to handle once you expect them.
Neither is a real obstacle — they’re just the kind of behavior that confuses people the first time and then never again. The Data Table slowdown only matters on big models, and the Goal Seek approximation is usually within rounding distance of the target. Knowing both up front means you’ll trust the tools rather than second-guessing a result that’s actually fine. With those in mind, the question is simply which tool fits the job.
Which tool for which question
| Your question | Use |
|---|---|
| What input hits this exact target? | Goal Seek |
| How does the result vary across one input’s range? | One-variable Data Table |
| How does it vary across two inputs together? | Two-variable Data Table |
| How do several named situations compare? | Scenario Manager |
The choice comes down to how many inputs and what kind of answer. Goal Seek for one input and an exact target; a Data Table for seeing one or two inputs vary across ranges; Scenario Manager for comparing whole sets of assumptions. All three turn a static model into a tool you can interrogate, which is what makes them so valuable on top of financial models and any serious analysis. Build your model with clean input cells and good formulas first, then let What-If Analysis explore it — the difference between a spreadsheet that reports one answer and one that helps you choose between many.