Using Solver in Excel to Maximize Profit Step by Step

Yes. In Excel for Microsoft 365 on Windows, use Solver to maximize a cell containing your profit formula by changing the cells that represent your decisions, then add constraints for limits such as labor, materials, or demand. For a straightforward product-mix problem, choose Simplex LP, set the profit cell to Max, and tell Solver which decision cells it may change.

What Solver does—and what it does not do

Solver is an Excel add-in for finding values that optimize a formula subject to rules you specify. In a profit model, the objective is usually total profit, the changing cells might be quantities to produce, and the constraints describe what is possible. Solver searches for a feasible combination that gives the highest objective value; it does not discover your costs, forecast demand, or decide whether your assumptions are realistic.

This distinction matters. A result is only as useful as the model behind it. If a unit profit omits packaging or a capacity limit is entered incorrectly, Solver can return a mathematically valid answer that is commercially misleading. Treat the output as the best answer under the assumptions in the worksheet, not as a guarantee of actual profit.

Build a small product-mix model

Suppose a workshop makes two products, A and B. Each A earns $30 in contribution profit and uses two labor hours and one unit of material. Each B earns $20 and uses one labor hour and two units of material. The workshop has 100 labor hours and 80 material units available. It can sell at most 45 units of A and 50 units of B. The question is how many of each product to make to maximize contribution profit.

Set up a worksheet with the decision quantities in B2:C2: B2 for A and C2 for B. Put unit profits in B3:C3 as 30 and 20, labor per unit in B4:C4 as 2 and 1, and material per unit in B5:C5 as 1 and 2. Use labels in column A so that the model is understandable later. Put total profit in B6, labor used in B7, and material used in B8. Start the decision quantities at zero, or another sensible starting point.

With product quantities in B2:C2 and unit contribution profits in B3:C3, enter =SUMPRODUCT(B2:C2,B3:C3) in B6. At the Solver solution of 40 units of A and 20 units of B, this returns 1,600, because 40 × $30 plus 20 × $20 equals $1,600.

With quantities in B2:C2 and labor hours per unit in B4:C4, enter =SUMPRODUCT(B2:C2,B4:C4) in B7. At 40 A and 20 B, it returns 100 hours: 40 × 2 plus 20 × 1.

With quantities in B2:C2 and material per unit in B5:C5, enter =SUMPRODUCT(B2:C2,B5:C5) in B8. At 40 A and 20 B, it returns 80 material units: 40 × 1 plus 20 × 2. SUMPRODUCT multiplies corresponding entries and adds the products, making it useful for linear totals.

Enable Solver and enter the settings

In desktop Excel for Microsoft 365 on Windows, if Solver is not already available, go to File > Options > Add-ins. At the bottom, choose Excel Add-ins beside Manage, select Go, check Solver Add-in, and select OK. Then open the Data tab and choose Solver. The add-in is available in desktop Excel; Excel for the web does not run the Solver add-in, so open the workbook in the desktop app to use it.

In the Solver Parameters dialog, set the objective to B6 and select Max. Set By Changing Variable Cells to B2:C2. Add the constraints B7 less than or equal to 100, B8 less than or equal to 80, B2 less than or equal to 45, and C2 less than or equal to 50. The resource limits are constraints on the calculated totals; the demand limits are constraints on the quantities themselves.

Ensure the quantities cannot be negative. You can check Make Unconstrained Variables Non-Negative in the Solver dialog, or add B2:C2 greater than or equal to zero as a constraint. Since products must be whole units in this example, add an integer constraint for B2:C2 using the relation int. Choose Simplex LP as the solving method, then select Solve. If Solver reports a solution, choose Keep Solver Solution to retain it in the worksheet.

Solver should return 40 for A and 20 for B, with a profit of $1,600. Both resource limits are fully used, and both quantities are below their demand caps. The result is not an arbitrary forecast: it follows from the stated unit profits and constraints. If your workbook uses different assumptions, its answer will differ.

Choose the right solving method

The solving method should match the relationships in your model. A linear model has an objective and constraints made from sums of variables multiplied by fixed coefficients. The product-mix example is linear, so Simplex LP is appropriate. In Solver, selecting this method can also help identify whether the model is infeasible or unbounded.

Simplex LP: Use this for linear objectives and constraints, including many production, staffing, shipping, and allocation models. Keep formulas linear if you want this method to apply.
GRG Nonlinear: Use this for smooth nonlinear models, such as a formula where changing a variable affects a rate or a curved relationship. It may find a local optimum rather than prove that it found the global best answer.
Evolutionary: Consider this for models with discontinuities, complex IF logic, or other relationships that do not fit the linear or smooth nonlinear assumptions. It uses a search process and may take longer; results can depend on settings and may not be guaranteed to be globally optimal.

Integer restrictions make some optimization problems harder because Solver must account for whole-number choices. Do not round a fractional result and assume it remains optimal: rounding can break a capacity constraint or produce a different, inferior plan. Add integer restrictions where the real decision requires them, then solve the constrained model.

Make the profit objective meaningful

For a product-mix model, a common objective is contribution profit: selling price less costs that vary with each unit, multiplied by the units sold. Include relevant variable costs such as direct materials, per-unit labor, commissions, and shipping if they truly vary with output. Avoid counting the same cost twice—for example, if labor is already included in the per-unit margin, do not subtract it again elsewhere.

Fixed costs need careful treatment. A fixed overhead cost that will be paid regardless of the production plan does not change which plan maximizes profit, although it does change the reported total profit. If a cost is incurred only when a product line is opened, the model may need a yes-or-no decision variable and a linking constraint. That turns a simple linear model into a mixed-integer model. Likewise, if selling more units changes the selling price, fixed unit margins may not represent reality; price and demand may need to be modeled explicitly.

Also distinguish production from sales. If products can remain unsold, a production quantity alone does not necessarily equal a sales quantity. Add inventory, sales, or demand relationships that reflect the business process. Use units consistently—hours should not be mixed with minutes, and dollars should not be mixed with cents without conversion.

Constraints: translate real limits into worksheet rules

Constraints define the feasible region: every combination Solver is allowed to consider. They should describe operational facts, not just numbers that make the answer look plausible. Typical examples include total labor available, a budget cap, minimum service levels, storage capacity, demand ceilings, and required production minimums.

Capacity: total hours or materials used must be less than or equal to what is available.
Demand: a quantity sold cannot exceed a credible sales limit.
Minimums: a product or service may need a required minimum quantity.
Whole-number choices: items, employees, vehicles, or shifts often cannot be fractional.
Yes-or-no choices: opening a facility or selecting a project may call for binary variables.

Do not add a constraint merely because it seems likely to be active. In the example, both resource limits are exactly used, but that is a result, not a rule requiring full use. A constraint such as labor used equals 100 would wrongly force all labor to be consumed even if a different model made unused labor more profitable.

Common Solver problems and how to fix them

If Solver says it cannot find a feasible solution, inspect the constraints for conflicts. For instance, minimum production requirements might demand more labor than is available. Temporarily review each limit against the minimums and confirm that the variable cells and calculated total cells are the intended ones.

If it returns an implausible answer, check for omitted limits, incorrect cell references, and sign errors. A profit objective should normally increase when a profitable quantity increases, while a resource-use total should increase with production. Check formulas by entering a small test quantity and confirming the totals by hand. Verify that quantities are not allowed to go negative and that integer restrictions are applied to the correct cells.

If Solver returns a fractional quantity for something that must be counted in whole units, add an integer constraint rather than manually rounding. If it returns a boundary value, such as the maximum possible demand, that may be correct: the model may be telling you that producing more of the product is profitable until another limit is reached. Investigate whether a missing demand or capacity rule explains an unexpectedly large value.

When a nonlinear model gives different answers from different starting values, it may have multiple local optima. Try sensible starting values and review the model structure and method. For a linear problem, use Simplex LP and make sure the formulas really are linear. Save a copy of the workbook before major model changes; Solver changes worksheet values when you keep a solution, and a saved baseline makes comparisons easier.

Review and communicate the result

After solving, check the quantities, objective, and every constraint in the worksheet. In the Solver Results dialog, you can keep the solution or restore the original values. Depending on the model and Solver output, you may also be able to request reports such as an Answer report. A report can help summarize variable values and constraint status, but it does not validate the assumptions or replace a business review.

Test how sensitive the recommendation is to changes in inputs. For example, change unit profits, labor availability, or demand caps and solve again. In a linear model, a small change may not affect the best product mix until a constraint or relative profitability changes. Comparing scenarios is often more useful than presenting one output as a guaranteed plan. Keep assumptions visible and label units, dates, and source values.

Frequently Asked Questions

Can Excel Solver maximize profit automatically? Solver can maximize a profit formula after you define the changing cells, constraints, and solving method. It does not create a valid profit model automatically, so you need to supply the formulas and business limits.

Where is Solver in Excel for Microsoft 365? Once the Solver Add-in is enabled, find it on the Data tab in desktop Excel. On Windows, enable it through File > Options > Add-ins, then Excel Add-ins > Go. On Mac, enable Solver through Tools > Excel Add-ins, then look for Solver on the Data tab. Excel for the web does not run this add-in.

Which Solver method should I use to maximize profit? Use Simplex LP when profit and constraints are linear, as in a product-mix model with fixed unit margins. Use GRG Nonlinear for smooth nonlinear relationships and Evolutionary for more complex or discontinuous models; nonlinear methods may not guarantee a global optimum.

Why does Solver give a fractional number of products? By default, decision cells can take fractional values unless you restrict them. Add an integer constraint to quantities that must be whole units, then solve again instead of rounding the original answer.

Can I use Solver in Excel online? The Excel Solver add-in is not available to run in Excel for the web. Open the workbook in desktop Excel for Windows or Mac and enable the Solver add-in there.

When another approach may be better

For a small model with a few choices, testing scenarios manually may be enough. For repeatable planning, you can keep the Solver model in a clearly labeled worksheet and save separate copies or scenario inputs for comparison. Larger, specialized, or frequently automated optimization problems may call for dedicated optimization software or a programming library. Whatever tool you choose, the essential work remains the same: define the decision variables, objective, constraints, and assumptions correctly, then check whether the proposed answer makes sense in practice.

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.