Excel Solver Tutorial: Build and Solve Better Models

To use Solver in Excel for Microsoft 365 on Windows, enable the Solver Add-in through File > Options > Add-ins, choose Excel Add-ins and select Go, check Solver Add-in, then select Data > Solver. Set a formula cell as the objective, choose whether to maximize, minimize, or reach a value, specify the cells Solver may change, add constraints, choose a solving method, and select Solve.

What Excel Solver does

Solver is an optimization add-in: it changes selected worksheet cells to find a value for a formula cell that meets conditions you define. The selected cells are called decision variables, the formula being optimized is the objective, and limits or requirements are constraints. Instead of manually trying different combinations, Solver searches for values that best meet the model.

It is useful for questions with many possible answers, such as how much of each product to make, how to assign staff to shifts, or how to allocate a limited budget. A regular worksheet formula calculates an answer from inputs; Solver works in the other direction by adjusting inputs to improve an outcome.

Solver does not know what a good business, scheduling, or financial decision is. It only follows the objective and constraints in the workbook. A missing limit, incorrect formula, or unrealistic input can produce a mathematically valid result that is not useful in practice.

Enable Solver in Excel

In desktop Excel for Microsoft 365 on Windows, Solver is an Excel Add-in. It is generally included with the desktop application rather than installed as a separate Solver product. To turn it on:

Select File > Options, then choose Add-ins.
At the bottom, set Manage to Excel Add-ins and select Go.
Check Solver Add-in and select OK. If Windows asks to install the feature, allow it to complete.
Open the Data tab. Solver should appear in the Analysis group.

In Excel for Mac, use Tools > Excel Add-ins, check Solver Add-in, and select OK. Solver is then available from the Data tab. Excel for the web does not provide the desktop Solver Add-in; open the workbook in the Excel desktop app to run this tutorial. Ribbon details can shift slightly between releases, so if a tab or command is missing, check that you are using desktop Excel and that the add-in is enabled.

Build a small production model

Consider a workshop producing two products. Product A earns $30 per unit and uses two labor hours and one unit of material. Product B earns $20 and uses one labor hour and two units of material. The workshop has at most 100 labor hours and 80 units of material. The question is how many of each product to make to maximize total profit, assuming fractional quantities are acceptable for the first model.

Set up the sheet with product names in B1:C1, decision quantities in B2:C2, profit per unit in B3:C3, labor per unit in B4:C4, and material per unit in B5:C5. Enter the input values in rows 3 through 5. Use D3 for total profit, D4 for labor used, and D5 for material used. The quantities in B2:C2 can start at zero; Solver will change them.

Product A: profit 30, labor 2, material 1.
Product B: profit 20, labor 1, material 2.
Available capacity: 100 labor hours and 80 material units.

With decision quantities in B2:C2 and unit profits in B3:C3, enter this objective formula in D3:

=SUMPRODUCT(B2:C2,B3:C3)

With quantities in B2:C2 and labor requirements per unit in B4:C4, enter this formula in D4:

=SUMPRODUCT(B2:C2,B4:C4)

With quantities in B2:C2 and material requirements per unit in B5:C5, enter this formula in D5:

=SUMPRODUCT(B2:C2,B5:C5)

SUMPRODUCT multiplies corresponding values in the two ranges and adds the products. For example, if B2:C2 contains 40 and 20, D3 returns 1,600, calculated as 40 times 30 plus 20 times 20. D4 returns 100 labor hours, and D5 returns 80 material units. Those quantities use both available resources exactly.

Set the objective, variables, and constraints

Choose Data > Solver to open Solver Parameters. For the production example, fill in the fields as follows:

Set Objective: $D$3, the total-profit formula.
To: Max, because the goal is to earn as much profit as possible.
By Changing Variable Cells: $B$2:$C$2, the quantities Solver may adjust.
Subject to the Constraints: add $D$4 <= 100 and $D$5 <= 80.
Add $B$2:$C$2 >= 0, or select the option that makes unconstrained variables non-negative.

Use cell references for constraints rather than typing a fixed answer into the objective. The objective and resource-use cells need to be formulas that depend on the changing cells. If the objective formula does not reference B2:C2, Solver has no way to improve it by changing those cells.

For this linear model, choose Simplex LP as the solving method and select Solve. If Excel reports a solution, choose Keep Solver Solution to retain the proposed quantities. The optimal values are 40 units of A and 20 of B, for a profit of 1,600. Labor use is 100 and material use is 80, so both capacity limits are met. This result follows from the stated assumptions; changing the unit profits, resource requirements, or available capacities can change it.

Choose the right solving method

Solver offers methods suited to different kinds of models. The structure of the formulas matters: a model with straight-line relationships is different from one with powers, products of decision variables, or conditional logic.

Simplex LP: use this for linear objectives and constraints. In a linear model, decision variables are not multiplied by one another or raised to powers, and their coefficients are constant. It is the appropriate choice for the production example.
GRG Nonlinear: use this for smooth nonlinear models. It can find a local optimum, so the starting values may matter; a local result is not necessarily the best possible result across every feasible combination.
Evolutionary: consider this for models with discontinuities or non-smooth relationships, such as some formulas involving IF logic or discrete choices. It uses a search process and may take longer or require careful settings.

When uncertain, inspect the formulas that feed the objective and constraints. A linear-looking table does not guarantee a linear model if one of its formulas introduces nonlinear behavior. For small models, compare the reported answer with a few sensible alternatives and check that all constraints are satisfied.

Whole-number decisions and practical assumptions

Some decisions cannot be fractional. A schedule might require whole employees, or a production plan might require complete items. In Solver, add an integer constraint for the relevant changing cells: in the constraint dialog, select the decision-variable range and choose int as the relation. If the values must be either zero or one, Solver also supports a binary constraint.

Integer restrictions can make a model harder to solve and can change the best result. In the example, the continuous answer happens to be whole numbers already. In a different model, rounding a fractional Solver answer is not a reliable substitute: the rounded quantities may violate a capacity limit or fail to be optimal. Add the appropriate integer restriction and solve again.

Model the real decision carefully. If production must be at least a minimum batch size, if overtime is available at an extra cost, or if only certain products can be made, represent those rules in formulas and constraints. Solver cannot infer them from labels or notes on the worksheet. Also distinguish a maximum capacity from a requirement: a capacity is normally an upper bound, while a minimum service level is a lower bound.

Check results and troubleshoot common problems

After solving, do not judge the outcome only by the objective value. Check the changing cells, recalculate resource use, and confirm that the result makes sense under the assumptions. In Solver Results, choose to keep the solution or restore the original values. Depending on the model and method, Excel may offer reports such as Answer, Sensitivity, or Limits. Sensitivity analysis is associated with suitable linear solutions; do not treat it as a general report for every nonlinear or integer model.

Solver says it cannot find a feasible solution: the constraints may conflict. For example, a required minimum may exceed available capacity. Review each bound and verify units.
The answer does not change: confirm that the changing cells are correct and that the objective formula depends on them. Check for a fixed value where a formula should be.
The result is unrealistic: add omitted limits such as non-negativity, maximum demand, or whole-number requirements. Solver obeys only rules entered in the model.
A nonlinear answer seems poor: try reasonable starting values and review the model and solving method. GRG can return a local optimum, and a different starting point can lead to a different result.
Solver is missing: confirm the desktop add-in is enabled; the web app does not run the built-in desktop Solver Add-in.

Keep a copy of the original inputs before changing a model, especially when comparing scenarios. Solver’s changing cells are ordinary worksheet values, and choosing to keep a solution replaces their previous values. You can also preserve separate scenarios by copying the model sheet or saving alternative inputs and outputs in clearly labeled areas.

Solver compared with Goal Seek and alternatives

Goal Seek is a simpler tool for finding one input value that makes one formula reach a target. For example, it can find the sales volume needed to reach a specified revenue if there is one changing input. Solver is more appropriate when there are multiple changing cells, limits on those cells, or a maximum or minimum objective rather than a single target value.

For simple what-if comparisons, a data table or manually tested scenarios may be easier to explain and audit. For large optimization problems, models that need specialized algorithms, or processes that must run repeatedly, a dedicated optimization product or programming tool may be a better fit. The desktop Solver add-in is a convenient starting point for worksheet-sized models, but it is not a substitute for validating a complex operational model.

Frequently Asked Questions

Is Solver included with Excel?

The Solver Add-in is included with desktop Excel and usually needs to be enabled before it appears on the Data tab. It is not available as the built-in Solver Add-in in Excel for the web.

Why is Solver not showing on the Data tab?

On Windows, enable it through File > Options > Add-ins, set Manage to Excel Add-ins, select Go, and check Solver Add-in. On Mac, use Tools > Excel Add-ins. If you are in a browser, open the workbook in desktop Excel.

Can Excel Solver handle integer values?

Yes. Add an integer constraint to the relevant decision cells, or use a binary constraint when each value must be zero or one. Integer restrictions can make a problem more demanding to solve.

What is the difference between Solver and Goal Seek?

Goal Seek adjusts one input to make one formula reach a specified value. Solver can adjust multiple cells, apply constraints, and maximize or minimize an objective, making it suitable for broader optimization problems.

Why does Solver give a different answer after I change starting values?

For nonlinear models, GRG Nonlinear can find a local optimum rather than a guaranteed global one, so starting values can affect the result. Check that the model is set up correctly, consider suitable alternative starting values, and verify the result against the constraints.

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.