What what-if analysis really does
A what-if model is just a formula with inputs you can change. The three built-in tools differ by shape. Goal Seek solves for one input. A Data Table sweeps one or two inputs across many results. Scenario Manager stores several complete input sets and reports them side by side. Pick the tool by how many knobs you are turning.
All three live on the Data tab. Goal Seek and Scenario Manager sit under What-If Analysis; Data Tables sit under the same menu. They do not alter your source data, they only test values, so you can experiment freely and undo if a result looks wrong.
Goal Seek: back into a single input
In a cell write =Units*Price-Cost to get profit, using a Sales table where Units is 1,000, Price is 50, and Cost is 30,000. The formula returns 20,000. Goal Seek will change one of those inputs to hit a number you name.
Open Data, click What-If Analysis, then Goal Seek. Set 'Set cell' to your profit formula, 'To value' to 30000, and 'By changing cell' to the Units cell. Press OK and Excel finds the Units count that makes profit exactly 30,000.
Goal Seek reports a solution like 1,200 units. Because 1200*50-30000 = 30000, the math closes. I recommend writing the solved value onto the sheet as a note, because Goal Seek overwrites the input cell and the old 1,000 disappears.
I recommend noting the starting input before you run Goal Seek, since it overwrites the cell. A common mistake is running it twice and losing the original number. Copy the input to a scratch cell first, or you cannot compare before and after.
Where Goal Seek falls short
Goal Seek changes exactly one cell. If you need both Price and Units to move, it cannot. This will break your plan if the real answer requires two levers; switch to Solver or a Data Table instead for that kind of problem.
Goal Seek uses iteration, so a formula with steps or text thresholds may return 'no solution'. A common mistake is pointing it at a cell with an IF that snaps between values; smooth math like Units*Price works, while a pass/fail flag does not.
One-variable Data Table: sweep one input
A Data Table prints a grid of outcomes as one input moves across a column of test values. It is the fastest way to show a manager 'here is profit at 800, 900, 1000, 1100, 1200 units' without building five formulas by hand.
In column D list 800, 900, 1000, 1100, 1200 down from D2. In C1 put your profit formula =Units*Price-Cost. The table reads the formula from the cell one column left of the top test value, so placement matters.
Highlight C1 through D6, the formula cell plus all test values. Open Data, What-If Analysis, Data Table. Leave 'Row input cell' blank and set 'Column input cell' to the Units cell. Press OK and the grid fills with profits.
At 1000 units the grid shows 20,000, at 1200 it shows 30,000, matching Goal Seek. In my experience this single grid ends more pricing debates than any paragraph, because the reader sees the curve in one glance.
Two-variable Data Table: sweep price and units
A two-variable table puts one input down the side and another across the top, filling a matrix of results. Use it when both Units and Price move, like a Black Friday promo where price drops but volume rises.
Put the profit formula in the top-left corner B1. Down column B list Units 800 to 1200; across row 1 list Prices 45, 48, 50, 52, 55. The corner formula is the only one you write; the rest are computed by the table.
Select the whole block B1 to F6, open Data Table, set Row input cell to Price and Column input cell to Units. Press OK. Cell C3 might show 800 units at 48 price = 800*48-30000 = 8400, and so on across the grid.
Scan the matrix for the highest profit. A common mistake is reading the row header as the answer; the value inside the cell is profit, the headers are the inputs. I recommend conditional formatting to highlight the max cell so it jumps out.
Avoid editing one cell inside a Data Table result; Excel protects the array and throws 'cannot change part of a data table'. To redo it, delete the whole block and rebuild, or change the input cells the table points at instead of the results.
Scenario Manager: save named input sets
Scenarios store complete sets of inputs under names like Best Case or Worst Case, then produce a summary that drops them into one report. Unlike a Data Table, a scenario can change many cells at once, not just one or two.
Open Data, What-If Analysis, Scenario Manager, then Add. Name it Base Case and select the input cells Units, Price, Cost as the changing cells. Enter 1000, 50, 30000. This pins the current numbers under a label you can recall.
Click Add again, name it Worst Case, and enter 700, 45, 32000. Profit drops to 700*45-32000 = 3000. Scenarios let you flip the whole model between moods with one click instead of retyping three cells.
In Scenario Manager click Summary and pick your profit formula as the result cell. Excel builds a new sheet listing each scenario's inputs and the resulting profit side by side. A common mistake is forgetting to name the result cell, which leaves the summary blank.
Common what-if mistakes
If 'Set cell' is an input instead of the result, Goal Seek spins with no solution. This will break the run because it tries to force an input to equal your target. Always set the result formula, then change an input cell below it.
The Column or Row input cell must be the live input your formula uses. A common mistake is selecting a copy of Units rather than the real Units cell, so the grid never moves. Double-check the reference before you press OK.
A scenario saves values at creation time. If you later change the formula, old scenarios keep their stored numbers and the summary lies. I recommend re-saving scenarios after any model edit, or label the summary with the model version date.
Worked example: hit a 30,000 profit target
Your Sales model has Cost fixed at 30,000 and Price at 50. You need profit of 30,000. Goal Seek says Units must be 1,200. The one-variable table confirms profit at 800 is 10,000, at 1000 is 20,000, at 1200 is 30,000.
Set profit cell to 30000 by changing Units. Excel returns 1200. Because 1200*50-30000 = 30000, the target closes exactly. Write 1200 next to the old 1000 so the team sees the required lift.
The Data Table prints 10000, 15000, 20000, 25000, 30000 down the Units sweep. This is the slide I send when a manager asks 'how close are we'; the answer is visible at every step, not just the end.
Next steps
Run Goal Seek on one real target this week, then build a one-variable table around it. Convert your inputs to a Sales Table so the formula reads Sales[Units] style ranges and stays valid as rows grow. See our dynamic arrays guide to replace static tables with FILTER-driven models.
I recommend keeping a 'model inputs' block on its own sheet, clearly labeled, before you use Scenario Manager. When every lever lives in one place, saving Best Case and Worst Case takes seconds and the summary report stays clean.
When you present the sweep, lead with the target row. I put 1,200 units, the Goal Seek answer, in bold so the manager sees the required lift from 1,000 at a glance. The rest of the grid is context that answers the inevitable 'what if we land short' follow-up.
Save the what-if sheet as a template once it works, because the same model returns every quarter with new costs. A clean input block and a labeled scenario summary mean next quarter is a paste, not a rebuild.
