Excel Solver Examples for Real Optimization Problems

Excel Solver examples include choosing the most profitable product mix, minimizing shipping costs, and assigning staff to shifts. In Microsoft 365 for Windows, open Data > Solver, set a formula cell as the objective, select the decision-variable cells, add constraints, choose a solving method, and click Solve.

What Excel Solver does

Solver is an Excel add-in for optimization: it changes selected worksheet values to maximize, minimize, or reach a target for a formula cell while obeying limits you specify. The changing values are called decision variables. The formula you want to improve is the objective, and rules such as available labor hours or a required minimum order are constraints.

That makes Solver different from ordinary Goal Seek. Goal Seek adjusts one input to reach a specified result in one formula. Solver can adjust multiple inputs at once and work with multiple constraints. It is useful when a problem has a clear measure of success and a set of limits, not simply because a worksheet contains complicated arithmetic.

Solver finds a solution based on the model you build. It cannot tell whether your assumptions are realistic, whether you omitted an important limit, or whether the answer is sensible in the real world. Treat the output as a result of the spreadsheet model, then check the model and the proposed values.

Enable Solver in Excel

In Excel for Microsoft 365 on Windows, Solver may need to be enabled once. Go to File > Options > Add-ins. At the bottom, beside Manage, choose Excel Add-ins and select Go. Check Solver Add-in, then click OK. Solver should then appear on the Data tab, usually in the Analysis group.

In Excel for Mac, use Tools > Excel Add-ins, select Solver Add-in, and click OK; the add-in’s location and interface can vary slightly by release. Excel for the web does not provide the desktop Solver add-in interface, so open the workbook in the desktop app to use these instructions. If Solver is missing after enabling it, check that the add-in is selected and that the Data tab is not simplified or customized.

Example 1: Choose a profitable product mix

Suppose a workshop makes products X and Y. Each unit of X earns $30 and uses two labor hours and three units of material. Each unit of Y earns $50 and uses four labor hours and two units of material. The workshop has 100 labor hours and 90 units of material. How many of each should it make to maximize profit?

Put the products in A2:A3, profit per unit in B2:B3, and the quantities Solver will choose in C2:C3. Enter 30 and 50 in B2:B3, and use starting quantities such as zero in C2:C3. With that layout, put the total-profit calculation in D2:

=SUMPRODUCT(B2:B3,C2:C3)

It multiplies each product’s profit by its chosen quantity and adds the results. For the resource table, enter labor and material in rows 7 and 8, their per-unit requirements in B7:C8, the quantities used in D7:D8, and the available amounts in E7:E8. For example, B7:C7 contains 2 and 4, while B8:C8 contains 3 and 2. With those cell locations, calculate labor used in D7 with:

=SUMPRODUCT(B7:C7,$C$2:$C$3)

With resource coefficients in B8:C8 and decision variables in C2:C3, calculate material used in D8 with:

=SUMPRODUCT(B8:C8,$C$2:$C$3)

In Data > Solver, set the objective to D2 and choose Max. Set the changing variable cells to C2:C3. Add the constraints D7:D8 <= E7:E8 and C2:C3 >= 0. Choose Simplex LP, then click Solve. This is a linear model, so Simplex LP is appropriate. The optimum is 20 units of X and 15 of Y: labor use is 2 × 20 + 4 × 15 = 100, material use is 3 × 20 + 2 × 15 = 90, and profit is $30 × 20 + $50 × 15 = $1,350.

If products must be made in whole units, add an integer constraint for C2:C3. The continuous solution happens to be whole numbers here, but that is not guaranteed in other models. Integer restrictions can make a problem harder to solve and can change the best result.

Example 2: Minimize shipping costs

A shipping model can decide how much to send from each warehouse to each destination. Put the shipment quantities in a grid, with warehouses as rows and destinations as columns. Put the cost per unit in a matching grid. The objective is the sum of each route’s shipment quantity multiplied by its unit cost. Add constraints so each warehouse does not ship more than its supply and each destination receives at least its demand.

For instance, if two warehouses have supplies of 80 and 70 units, and three destinations require 40, 50, and 30 units, Solver can choose the least-cost feasible routes. Add a nonnegative constraint for the shipment cells. If a route is unavailable, constrain its shipment cell to zero. If shipments must be whole packages, make the shipment variables integers. The model needs to balance total supply and demand; if demand exceeds available supply, Solver will report that no feasible solution exists unless you add another source or relax a requirement.

Example 3: Assign staff to shifts

For a small scheduling problem, use one decision cell for each person-and-shift assignment. A binary variable represents a yes-or-no choice: 1 means assigned and 0 means not assigned. Set the objective to minimize total staffing cost, or maximize coverage subject to a cost limit. Constraints can require a minimum number of people on each shift, prevent an employee from working overlapping shifts, and limit each person’s weekly hours.

Use Solver’s binary constraint for assignment cells rather than expecting a decimal answer such as 0.4 of a person to become a useful schedule. Include availability and rest requirements explicitly; Solver does not know employment rules or practical preferences unless they are represented in the worksheet. For a large roster with many interacting rules, a dedicated scheduling system may be more suitable than a spreadsheet model.

Choose the right solving method

The solving method matters because different models have different mathematical structures:

Simplex LP: Choose this for a linear model: the objective and constraints are formed from variables multiplied by fixed coefficients and added together. The product-mix and shipping examples are linear.
GRG Nonlinear: Use this for smooth nonlinear formulas, such as a model involving a variable multiplied by itself or a smooth curve. It can find a local solution, which may not be the best solution over every possible combination of values.
Evolutionary: Consider this for models with discontinuities or formulas that are not smooth. It uses a search approach, so results can depend on settings and may take longer. Check the result rather than assuming it is a global optimum.

Integer and binary constraints can also increase the search effort. Start with the simplest method that fits the model. If a linear model is set to GRG Nonlinear unnecessarily, or a nonlinear model is treated as linear, Solver may fail or return an unsuitable result.

Set up a Solver model step by step

Lay out the inputs, decision variables, objective formula, and calculations for each constraint in separate, clearly labeled cells.
Open Data > Solver. Select the objective formula cell and choose Max, Min, or Value Of, depending on the question.
Enter the decision-variable range in By Changing Variable Cells. These should be actual input cells, not formula cells.
Click Add to define each limit. Use cell references for the calculated amount and the right-hand side of the limit; choose the appropriate relationship, such as <=, =, or >=.
Select a solving method that matches the model, then click Solve. Review the result and choose Keep Solver Solution if it is valid, or Restore Original Values if it is not.

Keep worksheet units consistent. If labor availability is in hours, labor use should also be in hours; if shipping demand is in cases, shipment quantities should use cases. Label units and limits so a reviewer can follow the logic. It is often helpful to reserve a small section of the sheet for the decision variables and another for model assumptions.

Check the result and troubleshoot common problems

Solver’s message is not a substitute for validation. Recalculate resource use, confirm every constraint is satisfied, and check that the objective formula points to the intended cells. In the product example, a proposed answer can be checked by substituting the quantities into both resource calculations and the profit calculation.

If Solver says it cannot find a feasible solution, look for conflicting requirements, incorrect units, or a formula that is not linked to the changing cells. For example, a demand minimum of 100 cannot be met when the model has only 90 available units and no other supply. If the result is unexpectedly extreme, check for a missing upper bound: a maximization model with no meaningful limit on a profitable variable may have no finite optimum.

Other common issues include selecting the wrong objective direction, forgetting nonnegativity, leaving a decision cell out of the changing range, or using the wrong solving method. A constraint on a blank or incorrect cell can make the model appear to solve while failing to represent the actual problem. Avoid rounding decision-variable cells inside formulas unless rounding is genuinely part of the model; rounding can make the relationship nonlinear or obscure whether a limit is met.

For a linear model without integer restrictions, Solver can offer a Sensitivity report after a solution. That report can help analyze how changes in certain coefficients or limits affect the solution, but it is not available for every model type, including models with integer constraints. Reports are analytical aids, not a guarantee that a model remains valid after assumptions change.

Solver compared with other Excel tools

Use Goal Seek when one input needs to make one formula reach a target. Use a one-variable or two-variable data table when you want to compare results across a range of inputs. Solver is the better fit when you need to choose several values at once while balancing limits. For simple selection or lookup tasks, ordinary formulas may be easier to maintain than an optimization model.

Solver is included as an Excel add-in rather than a separate paid Excel feature for users with the desktop application, but availability can depend on the Excel environment and organizational settings. Standard Solver also has practical model-size limits; the traditional Solver add-in is limited to 200 changing variable cells. Very large optimization problems may need specialized software or an organization-approved Solver product with higher capacity. Before choosing an alternative, compare the model size, required constraints, collaboration needs, and whether the result must be reproducible by other users.

Frequently Asked Questions

Is Excel Solver free?
Solver is available as an Excel add-in in supported desktop editions and does not generally require a separate purchase. You still need access to an eligible desktop version of Excel, and workplace administrators may control add-ins.

Why can’t I see Solver on the Data tab?
Enable Solver through File > Options > Add-ins on Windows, or Tools > Excel Add-ins on Mac. If it still does not appear, confirm that Solver Add-in is checked and restart Excel if necessary.

Can Solver work with whole numbers or yes/no choices?
Yes. Add an integer constraint for whole numbers or a binary constraint for 0/1 decisions. These restrictions can make the search take longer and may alter the result compared with a model that permits decimals.

Why does Solver say there is no feasible solution?
At least one constraint may conflict with another, or a formula or cell reference may be wrong. Check that the available resources can satisfy the required totals, and verify that each constraint refers to the intended calculated cell.

Can I use Solver in Excel for the web?
The desktop Solver add-in described here is not available through the usual Excel for the web interface. Open the workbook in Excel for Windows or Mac to set up and run this Solver model.

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.