Solver in Excel: Find the Best Answer Within Limits

Solver in Excel finds the best answer to a problem with limits: the product mix that earns the most profit, the cheapest way to staff a week, the budget split that stays under a cap. You build the model with ordinary formulas, then tell Solver what to maximise or minimise, which cells it may change and which limits it must respect. This guide is for analysts, planners and students who have outgrown trial and error, and it walks through a complete example.

What is Solver in Excel?#

Solver is an optimisation add-in that ships with desktop Excel for Windows and Mac. Optimisation means finding the best possible value of something, such as the highest profit or the lowest cost, while staying within limits.

Every Solver model has three parts:

Solver tries combinations of the variable cells, recalculates your formulas and keeps the best result that meets every constraint. It is the next step up from Goal Seek, which can only change one cell.

How to turn on the Solver add-in#

Solver is installed with Excel but switched off by default. Turn it on once and it stays on.

Windows:

1. Go to File > Options > Add-ins.
2. In the Manage box at the bottom, choose Excel Add-ins and click Go.
3. Tick Solver Add-in and click OK.

Mac:

1. Go to Tools > Excel Add-ins.
2. Tick Solver Add-in and click OK.

Solver now appears on the Data tab, at the right-hand end in the Analyze group. These steps are for the desktop apps.

Set up the model: inputs, formulas and limits#

Solver only works on formulas, so build the model first. A small workshop makes chairs and tables. Each uses labour and wood, and both are limited:

The workshop has 40 labour hours and 60 metres of wood a week. Column E holds the quantities, the cells Solver will change. Start with any values; Solver will replace them.

Now add the formulas. SUMPRODUCT multiplies two ranges row by row and adds the results, so it's perfect here:

Total profit is 30 × 20 + 50 × 10 = 1,100. For a long list of products you would write the same formula with longer ranges; the SUMPRODUCT guide and the SUMPRODUCT function reference explain it in detail. For a simple total of one column, a plain SUM does the job (see the SUM function guide).

Keep limits in their own cells, like C6 and C7, rather than typing them into Solver. Then you can change a limit and run Solver again.

Run Solver step by step#

With the model ready, open Data > Solver and fill in the dialog:

1. Set Objective: $B$5 (total profit).
2. To: choose Max.
3. By Changing Variable Cells: $E$2:$E$3.
4. Subject to the Constraints: click Add and enter $B$6 <= $C$6. Click Add again and enter $B$7 <= $C$7.
5. Optionally add $E$2:$E$3 with the relation int, so you only get whole chairs and tables.
6. Tick Make Unconstrained Variables Non-Negative, so Solver can't suggest making −5 tables.
7. Select a Solving Method: Simplex LP, because every formula here is a simple multiplication.
8. Click Solve.

Solver reports "Solver found a solution" and offers to Keep Solver Solution or Restore Original Values. Keep it.

Read the result#

Solver sets the quantities to 20 chairs and 10 tables, for a total profit of 1,100. Labour used is 40 of 40 and wood used is 60 of 60, so both limits are fully used.

You can check why this is the best mix by testing the extremes:

Making only tables runs out of labour while wood is left over. Making only chairs runs out of wood while labour is left over. The mix uses both fully, so it earns the most. Solver finds this kind of answer in a fraction of a second, even with dozens of products and limits.

When Solver finishes, you can also ask for an Answer Report, which lists the final values and which constraints are binding (fully used).

Choose a solving method#

Solver has three methods, and the right one depends on your formulas:

• Simplex LP: for linear models, where every result is inputs × fixed numbers added together. Product mix, budget allocation and simple shift plans are usually linear. It is fast and finds the true best answer.
• GRG Nonlinear: for smooth curves, such as a price that changes with quantity or a growth formula with powers.
• Evolutionary: for models with jumps, such as IF, VLOOKUP or rounding inside the formulas. It is slower and the answer may be very good rather than perfect.

If Simplex LP says the model isn't linear, switch to GRG Nonlinear.

Solver vs Goal Seek#

Both work backwards from a result, but they do different jobs:

Use Goal Seek for questions like "what price makes profit exactly 5,000?", as in a break-even analysis. Use Solver when there are several choices and limits, such as splitting a project budget across tasks or building a shift schedule that covers every slot at the lowest cost.

Common Solver problems#

These are the usual reasons Solver fails or gives a strange answer:

1. "Could not find a feasible solution." No mix meets every limit. Check each constraint's sign and that it points at the right cells.
2. "The objective cell values do not converge." The objective can grow forever, usually because a limit is missing.
3. Typed numbers instead of formulas. If B5 is a typed 1,100, Solver can't change it by changing E2:E3.
4. Negative or fractional quantities. Tick the non-negative box and add an int constraint.
5. Wrong method. Models with IF or lookups need GRG Nonlinear or Evolutionary.

Practise it#

Solver is only as good as the model underneath, and the model is built from SUM, SUMPRODUCT and clean references. Try the profit exercise on this page, then practise SUM in a live spreadsheet and build a full model in the department budget practice. Every answer is checked instantly. For a related planning project, see the monthly budget guide.