Purpose
By the end of this lesson, you will be able to use Goal Seek and Data Tables to answer “what if” questions in a spreadsheet.
Lesson Explanation
Goal Seek (Data → What-If Analysis → Goal Seek) works backward: given a desired result, it finds the input value needed to achieve it. For example, if a formula calculates profit based on a price assumption, Goal Seek can answer “what price do we need to charge to hit $50,000 profit?” by automatically adjusting the price cell until the profit formula equals 50000.
Goal Seek requires three inputs: the cell containing the formula (“Set cell”), the target value (“To value”), and the cell to adjust (“By changing cell”). It only works with a single changing variable at a time.
A Data Table (also under What-If Analysis) tests a formula across a whole range of input values at once, rather than one at a time. A one-variable data table might show projected profit at ten different price points simultaneously, in a single table – useful for comparing scenarios side by side rather than running Goal Seek repeatedly.
A two-variable data table extends this further, testing a formula’s result across combinations of two changing inputs at once – for example, profit at different combinations of both price AND unit cost, displayed as a grid.
Practice Questions
1. A profit formula in cell B10 depends on a price assumption in cell B2. A manager wants to know what price would produce exactly $75,000 in profit. What Excel tool answers this directly?
View Answer
Goal Seek.
2. Set up the three required inputs for Goal Seek to solve the scenario above: profit formula in B10, target of 75000, price cell to adjust is B2.
View Answer
Set cell: B10; To value: 75000; By changing cell: B2.
3. Goal Seek is asked to find the combination of BOTH price and unit cost that would achieve a target profit. Can Goal Seek do this directly?
View Answer
No – Goal Seek only works with a single changing variable at a time; a two-variable data table or a different tool (like Solver) would be needed for two simultaneous unknowns.
4. A manager wants to see projected profit at 8 different possible price points, all at once in a single table, rather than running Goal Seek eight separate times. What tool is better suited to this?
View Answer
A one-variable Data Table.
5. A one-variable data table shows profit projections for prices ranging from $10 to $50, in $5 increments. What does each row of the resulting table represent?
View Answer
The projected profit outcome for one specific price point within that range.
6. A two-variable data table is built to show profit across combinations of price (rows) and unit cost (columns). What does a single cell within this table’s grid represent?
View Answer
The projected profit for one specific combination of a particular price and a particular unit cost.
7. A Goal Seek is run, and the “By changing cell” is accidentally set to the same cell as the “Set cell” (the formula result itself). What is wrong with this setup?
View Answer
Goal Seek needs to adjust an INPUT that feeds into the formula, not the formula’s own result cell – this setup doesn’t make logical sense and would not work correctly.
8. A sales team wants to know: “If we want $100,000 in revenue, how many units do we need to sell?” assuming a fixed price per unit. Which tool – Goal Seek or a Data Table – is more directly suited to this single-variable question?
View Answer
Goal Seek, since it’s a single target (revenue) with a single variable to solve for (units sold).
9. A one-variable data table already exists showing profit at 10 different price points. A colleague wants to also see how profit changes at different fixed cost levels, combined with those same price points. What kind of data table would show this combined view?
View Answer
A two-variable data table, with price as one variable and fixed cost as the second.
10. Why might a Data Table be more useful than Goal Seek when comparing several scenarios side by side, rather than solving for one specific target?
View Answer
A Data Table shows multiple outcomes across a range of inputs simultaneously in one table, while Goal Seek only solves for one specific target value at a time, requiring it to be rerun for each new scenario.
11. A profit formula depends on three separate assumptions: price, unit cost, and fixed overhead. If a team wants to see how profit changes across combinations of all three simultaneously, is a two-variable data table sufficient?
View Answer
No – a two-variable data table only handles two changing inputs at once; three simultaneous variables would require a different approach, like scenario manager or separate analysis.
12. A Goal Seek result shows that a price of $47.32 would be needed to hit a $60,000 profit target. What should be done with this result before treating it as a final business decision?
View Answer
The result should be reviewed for practical feasibility (e.g., is this price realistic for the market, are there rounding or minimum-price constraints) before being treated as an actionable decision, since Goal Seek only solves the math, not the business judgment around it.