Excel’s Goal Seek can change only one input cell per run; it cannot adjust several changing cells at once. For multiple inputs, use Solver, or run Goal Seek separately when the inputs and target calculations are genuinely independent.
How Goal Seek works
Goal Seek is a what-if analysis tool for finding the input value that makes a formula return a result you specify. You provide three things: a formula cell, the result you want, and one input cell Excel may change. Excel then estimates a value for that input and recalculates the formula until it gets close to the target.
For example, suppose a profit formula depends on a selling price, a number of units, and a fixed cost. With the selling price in B2, units in B3, fixed cost in B4, and profit in B5, enter this formula in B5:
With selling price in B2, unit count in B3, and fixed cost in B4, the formula in B5 is =B2*B3-B4.
If B2 is 20, B3 is 100, and B4 is 800, profit is 1,200. To find the price that produces 1,500 profit while the units and cost stay fixed, Goal Seek can change B2. The required price is 23: 23 multiplied by 100, minus 800, equals 1,500.
The formula may refer to many cells, but that does not mean Goal Seek can vary them all. It changes only the one cell entered in the “By changing cell” box. The changing cell should be an input value, rather than a cell containing a formula. The “Set cell” must contain a formula that depends, directly or indirectly, on that input.
Run Goal Seek in Excel for Microsoft 365
In Excel for Microsoft 365 on Windows, arrange the model so the result is calculated by a formula and the input you want to adjust is in a separate cell. Then follow these steps:
- Select Data > What-If Analysis > Goal Seek.
- In Set cell, enter or select the formula cell, such as B5.
- In To value, type the result you want, such as 1500. Enter a number, not a cell reference or formula.
- In By changing cell, enter or select the input cell, such as B2.
- Select OK and review the proposed value and recalculated result. Select OK again to keep the result, or Cancel to discard it.
On Excel for Mac, Goal Seek is also available from the Data tab under What-If Analysis in current desktop editions; ribbon placement can vary slightly by release. Excel for the web does not provide the desktop Goal Seek command, so open the workbook in the desktop app to run it. Save a copy before changing important model inputs, and check that the result makes sense in the context of the worksheet.
When separate Goal Seek runs are enough
Running Goal Seek several times can be reasonable if each calculation is independent. For example, if three separate products each have their own price input and profit formula, and changing one product’s price does not affect the others, you can solve each target in turn. Record the result for each product and verify that later runs have not changed any shared inputs.
Repeated runs do not create a simultaneous multi-cell solution. If two targets both depend on the same changing cell, solving the second target will generally replace the value found for the first. Likewise, if formulas connect several inputs—for example, a price affects demand, and demand affects both revenue and inventory—changing those inputs one by one can produce a result that fails the overall model.
Also distinguish between “several cells that contain formulas” and “several cells Excel is allowed to change.” Goal Seek can set one formula cell to one target while changing one input cell, even when the worksheet contains many other formulas. It cannot set multiple formula cells to different targets in one Goal Seek operation.
Use Solver when several inputs must change
Excel Solver is the more suitable tool when one objective depends on multiple decision variables. Solver can change a range of cells, subject to constraints, to maximize or minimize an objective or make it equal to a specified value. It is an Excel add-in, so it may need to be enabled first.
On Windows, enable it through File > Options > Add-ins. At the bottom, select Excel Add-ins in the Manage box, choose Go, check Solver Add-in, and select OK. Solver then appears under Data > Solver. On Mac, open Tools > Excel Add-ins, check Solver Add-in, and look for Solver on the Data tab. Solver is not available in Excel for the web.
In the Solver Parameters dialog, choose the objective formula cell, select whether to maximize, minimize, or set its value, and specify the changing variable cells. Add constraints when the model requires limits, such as nonnegative quantities, a maximum budget, or whole-number units. Select a solving method that fits the model: Simplex LP is for linear models, while GRG Nonlinear is commonly used for smooth nonlinear models. The Evolutionary method can handle some nonsmooth or discontinuous problems, but may take longer and may not find the best possible answer.
For the profit example, Solver could adjust both price and units, but a profit target alone may not identify a unique combination. Many price-and-quantity pairs could produce the same profit. A practical model therefore needs additional information or constraints, such as a demand relationship, a maximum production capacity, or a requirement that units be whole numbers. Solver finds a result consistent with the model; it does not supply missing business assumptions.
When you have several target results
Solver normally optimizes one objective cell at a time. If you need multiple outputs to match separate targets, you can sometimes create a single measure of total error and ask Solver to minimize it. This approach is useful only when the model is meaningful and there are enough adjustable inputs to meet the targets. It does not guarantee an exact or unique solution.
For example, suppose actual outputs are in B5:B6 and their desired targets are in C5:C6. In a separate objective cell, use this formula to total the squared differences:
With actual results in B5:B6 and target values in C5:C6, enter =SUMXMY2(B5:B6,C5:C6) in the objective cell.
Then set Solver to minimize that objective by changing the relevant input cells. A value of zero means both actual results match their targets exactly; a positive value means at least one differs. Squaring gives larger misses more weight and prevents a positive error from cancelling a negative one. Consider scaling outputs that use very different units, such as dollars and units sold, because one measure could otherwise dominate the total error. Validate the result against the original formulas and constraints rather than treating a low error score as proof that the assumptions are sound.
Other tools and approaches
Not every multi-cell question needs Solver. Choose a tool based on whether you want a solution, a comparison, or a saved set of assumptions:
- Data Table: Use a one-variable or two-variable data table to see how a formula’s result changes across a grid of input values. It evaluates combinations you supply; it does not search for multiple input values that satisfy a target.
- Scenario Manager: Save and compare named sets of input values, such as an optimistic, expected, and cautious case. It compares scenarios rather than automatically finding the values that meet a target.
- Formulas or direct algebra: If the relationship is simple and has a direct mathematical solution, calculating the input with a formula can be easier to audit than an iterative tool.
- Separate Goal Seek runs: Use these for independent calculations with different inputs and targets, but document the inputs and check for shared dependencies.
For a Data Table in desktop Excel, the command is Data > What-If Analysis > Data Table. A table can show sensitivity across candidate values, which is useful when you want to compare outcomes rather than accept one computed answer. Goal Seek and Solver, by contrast, work backward from a target or objective.
Common problems and checks
If Goal Seek reports that it could not find a solution, first check the model rather than assuming Excel is malfunctioning. A target may be impossible for the permitted input range, the formula may not actually depend on the changing cell, or the calculation may be too complex for the method to converge from its starting value.
- Confirm that Set cell is a formula cell and that the formula ultimately refers to the changing input.
- Check for hard-coded values, misspelled references, and formulas that return errors such as
#VALUE!or#DIV/0!. - Try a sensible starting value for the input. Nonlinear formulas can behave differently depending on the starting point.
- Check whether the target is attainable. For example, a model that caps output at 100 cannot reach a target of 120 by changing an unrelated price.
- Review rounding and precision. A displayed result may look equal to the target even if the stored value differs slightly.
Goal Seek uses an iterative numerical process, so its result can be an approximation rather than an exact symbolic answer. It also does not enforce a collection of business rules the way a properly configured Solver model can. After a run, inspect the changed input, the resulting formula value, and any related calculations. If you need reproducible results, record the target and assumptions alongside the answer.
Frequently Asked Questions
Can Excel Goal Seek change multiple cells at once?
No. Each Goal Seek run changes one input cell to make one formula cell reach one specified value. Use Solver when a single objective depends on multiple changing inputs.
Can I run Goal Seek for several worksheets?
You can run it separately for calculations on different worksheets, provided each run has a formula cell and one changing input cell. It does not solve targets across multiple worksheets simultaneously; Solver may be appropriate if the linked model has several changing cells.
Why is the Goal Seek command unavailable in Excel for the web?
Goal Seek is a desktop Excel feature rather than a command in Excel for the web. Open the workbook in Excel for Microsoft 365 on Windows or Mac to use it.
Can Goal Seek work with a changing cell that contains a formula?
The changing cell should be an input value, not a formula cell. If a formula calculates the value you want to adjust, identify the underlying input that drives it and use that cell instead.
Should I use Goal Seek or Solver?
Use Goal Seek for one input and one target formula. Use Solver when multiple input cells must change, when you need constraints, or when you are optimizing an objective such as maximum profit or minimum cost.
Related
- Y Balance Score: How to Calculate and Interpret It
- Excell Battery Calgary: What to Know Before You Go
- X++ Enum Values in SQL: How Stored Numbers Work in D365
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Checkbox in Microsoft Word: Two WaysTo insert a checkbox in Microsoft Word, use Developer > Check Box Content Control for a box readers…
- Excel Sales Tracking Template by RegionThe quickest way to track sales by region in Excel is to use a PivotTable on raw sales…
- SUBSTRING in Snowflake: Syntax, Examples, and TipsThe SUBSTRING function in Snowflake extracts a portion of a string starting at a specified position and continuing…
- Outlook Auto Forward: Setup Steps and Common LimitsYes. You can set up automatic email forwarding in Outlook using the account’s forwarding setting or, in classic…
- Excel Table Slicers: How to Add, Use, and TroubleshootAn Excel table slicer is a clickable filter panel for an Excel table: choose a button such as…
- Sort by Row in Google Sheets: How to Reorder Data SafelyTo sort columns by the values in a row in Google Sheets, select the full range of columns…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.