Excel Goal Seek for Multiple Cells: How Solver Works

Excel’s Goal Seek can change only one input cell at a time. To make Excel adjust multiple cells to reach a target, use the Solver add-in: set the formula cell as the objective, choose a target value, then specify all the changing cells and any necessary constraints.

Why Goal Seek changes only one cell

Goal Seek is a built-in what-if analysis tool for finding an input that makes a formula return a particular result. For example, it can find the sales price in one cell that makes a profit formula equal a target. Its dialog has one field for the cell to change, so it cannot vary several inputs together.

To open it in Excel for Microsoft 365 on Windows, select Data > What-If Analysis > Goal Seek. Set Cell must contain a formula, and By Changing Cell accepts one input cell. Goal Seek repeatedly changes that input and recalculates the worksheet. It is useful when there is one unknown, but it is not a simultaneous multi-input optimizer.

This distinction matters because a formula can often reach the same target through many combinations of inputs. If you want Excel to choose among those combinations, you must define what counts as a good solution, usually with an objective to minimize or maximize and constraints that rule out unsuitable answers.

Use Solver to adjust multiple cells

Solver is Excel’s optimization add-in. Unlike Goal Seek, it can change a range of decision cells at once, subject to constraints. You can ask it to make a formula equal a specified value, maximize it, or minimize it. Solver is therefore the standard Excel answer to the question of changing multiple cells to hit a goal.

To turn it on in Excel for Microsoft 365 on Windows:

Select File > Options > Add-ins.
At the bottom, set Manage to Excel Add-ins, then select Go.
Check Solver Add-in and select OK.
Open the Data tab and select Solver.

If Solver is not listed after enabling it, close and reopen Excel, then check the Add-ins dialog again. In Excel for Mac, enable Solver through Tools > Excel Add-ins, select Solver Add-in, and then look for Solver on the Data tab. Excel for the web does not provide the same built-in Solver workflow as the Windows desktop app; open the workbook in desktop Excel for these steps.

Set up a multi-cell target with an example

Suppose a small business sells two products and wants to find quantities that produce $1,000 in revenue. Put the quantity for product A in B2 and product B in B3. Put their prices, $12 and $20, in C2 and C3. In B5, calculate total revenue. With quantities in B2:B3 and prices in C2:C3, enter =SUMPRODUCT(B2:B3,C2:C3) in B5. If B2 is 10 and B3 is 20, the result is $520: 10 × $12 plus 20 × $20.

Open Solver and use these settings:

Set Objective: B5.
To: Value Of, then enter 1000.
By Changing Variable Cells: B2:B3.
Constraints: add B2:B3 >= 0 so Solver does not propose negative sales.
Solving method: choose a method appropriate to the model; for this straightforward linear calculation, Simplex LP is suitable.

Select Solve and review the result. For example, 0 units of A and 50 units of B produce exactly $1,000. There are other valid combinations, including 65 units of A and 11 units of B, which also produce $1,000. Solver may find a different valid combination depending on the starting values and model settings. If products must be sold in whole units, add integer constraints for B2:B3; without them, Solver is allowed to return fractional quantities.

This example also shows why a target alone may not identify one unique answer. If the real business question is to reach at least $1,000 while minimizing the number of units, create a separate formula for total units and set that formula as the objective to minimize. Add a constraint requiring revenue to be at least 1,000. The model’s objective tells Solver which answer to prefer, while the constraints describe what answers are allowed.

Understand objectives, changing cells, and constraints

A Solver model has three main parts. The objective is a formula cell whose value you want to control. The changing cells are the inputs Solver may alter. Constraints are rules those inputs or other formula cells must obey. A well-defined model needs all three when simply reaching the target would leave too many possible answers.

Set Objective: choose a formula cell, not a fixed value. Solver can target a value or optimize the objective by selecting Max or Min.
By Changing Variable Cells: enter the cells Solver is permitted to change. They can be a contiguous range such as B2:B3 or multiple ranges selected in the field.
Subject to the Constraints: add rules such as nonnegative values, capacity limits, integer quantities, or binary yes/no decisions.

For instance, a staffing plan might minimize total labor cost while requiring enough workers on every shift and preventing a person from being assigned more than one shift at a time. A product mix model might maximize profit while keeping labor hours and materials within available limits. These are optimization problems, not just requests to make one formula equal a number.

Solver changes worksheet values and recalculates dependent formulas as it searches. The objective and constraints must therefore refer to cells that actually depend on the changing cells. If changing a decision cell has no effect on the objective, Solver cannot use it to improve the result.

Choose a solving method and define the model

Solver offers different methods because models behave differently. Simplex LP is intended for linear models, where the relationships in the objective and constraints are linear. GRG Nonlinear is for smooth nonlinear models. Evolutionary can help with models that are not smooth, such as ones involving IF formulas or discrete choices, though it may take longer and may not find the best possible result.

For the revenue example, revenue is a linear function of the quantities, so Simplex LP is a sensible method. A model involving a percentage growth formula, powers, or products of decision variables may be nonlinear and require a different method. Choosing a method does not fix a poorly specified model: check that formulas, units, constraints, and the intended objective all represent the real question.

Solver’s standard Excel add-in supports up to 200 changing variable cells. For a modest planning model this is often enough, but a larger model may need a different optimization tool or a more compact formulation. Integer and binary restrictions can also make a problem substantially harder to solve, especially as the number of decisions grows.

Goal Seek, Solver, and other alternatives

Use Goal Seek when there is one input to adjust and you want one formula to reach a target. Use Solver when multiple inputs can change, or when the task involves maximizing, minimizing, or respecting rules. Solver can handle a one-cell model too, but Goal Seek is usually quicker for a simple one-input calculation.

A one-variable or two-variable Data Table is another what-if tool, but it evaluates selected input values and displays the resulting formula values. It does not search freely for an answer or enforce a set of optimization constraints. A Data Table is useful for comparing scenarios, such as revenue at several prices and quantities, rather than asking Excel to choose the inputs.

Other alternatives include testing scenarios manually, using formulas to calculate a direct answer when the algebra is straightforward, or using a dedicated optimization package for a very large model. A macro can automate repeated Solver runs, but it adds code to maintain and is not necessary for an ordinary single model. Avoid treating a Data Table, Goal Seek, or a hand-built formula as a multi-cell optimizer when the task requires Solver’s constraints and search.

Common problems and how to fix them

Solver says it cannot find a feasible solution: the constraints may conflict. Check for accidental limits, mismatched units, or a target that is impossible under the stated bounds. Temporarily remove constraints one at a time to identify the conflict.
The result is unexpected: confirm the objective cell is the intended formula and that each changing cell affects it. Check that constraints use the right cells and comparison signs.
Quantities are decimals: that is allowed unless you add integer restrictions. Use an integer constraint when the real-world quantity must be a whole number.
Solver reaches a different answer on another run: the model may have multiple valid solutions, or the selected method may be sensitive to starting values. Add a secondary objective or more constraints if you need a preferred result.
Solver does not converge or takes too long: check for formulas with abrupt changes, unnecessary decision cells, and overly complex constraints. Choose a method suited to the formulas and simplify the model where possible.
The workbook shows an old result: Solver’s answer is a set of input values, not a permanent rule that keeps the target satisfied as other cells change. Run Solver again after changing assumptions.

Before accepting a result, independently inspect the changed cells and recalculate the objective. Check that every constraint is satisfied and that the result makes sense in the units of the problem. For business or scientific models, compare the answer with a plausible manual scenario; a mathematically valid result can still reveal a mistaken assumption in the spreadsheet.

Frequently Asked Questions

Can Excel Goal Seek change two cells at once?
No. Goal Seek has one By Changing Cell field. Use Solver and enter both cells, or a range containing them, under By Changing Variable Cells.

Where is Solver in Excel 365?
First enable Solver through File > Options > Add-ins, choose Excel Add-ins in Manage, select Go, and check Solver Add-in. Then open Data > Solver.

Can Solver make multiple formulas hit targets at the same time?
Solver has one objective cell. You can build a combined objective formula that represents several targets, such as a total error measure, and ask Solver to minimize it. The model needs a suitable formula and constraints; Solver does not provide a separate Goal Seek target field for each formula.

Why does Solver give me a different combination each time?
There may be several combinations that meet the objective and constraints. Add meaningful constraints or a clear secondary objective if one combination is preferable. Check that any integer requirements are explicitly included.

Does Solver work in Excel for Mac?
Excel for Mac includes the Solver add-in. Enable it through Tools > Excel Add-ins, then use Solver from the Data tab. Ribbon placement can vary slightly with window size and Excel updates.

Can Solver change cells on multiple worksheets?
Solver’s changing cells and objective are worksheet cell references in the workbook, but keep the model organized and verify the references carefully. If using cells on different sheets, create a clear objective formula that links the relevant inputs and results, then select the intended changing cells in Solver.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.