Excel Solver finds values for selected cells that maximize, minimize, or reach a target in a formula, while obeying limits you specify. In Microsoft Excel for Microsoft 365 on Windows, enable the Solver Add-in, then use Data > Solver to choose an objective cell, changing cells, and constraints.
What Excel Solver does
Solver is an optimization tool: instead of asking Excel to calculate an answer from inputs you already know, you tell it what result you want and which inputs it may adjust. It tests possible values for those inputs and searches for a solution that meets your conditions. For example, it can help decide how many units of several products to make when materials and labor are limited, or how to allocate a fixed number of hours across projects.
A Solver model has three main parts:
- Objective: a single formula cell that Solver will maximize, minimize, or set to a particular value.
- Changing variable cells: the input cells Solver is allowed to alter.
- Constraints: rules that restrict the changing cells or other calculated cells, such as total hours being no more than 100.
Solver does not know what a sensible business decision is. It only works with the formulas and limits in the workbook. If a formula is missing a cost, a constraint uses the wrong comparison, or units are inconsistent, Solver may produce a mathematically valid answer that is not useful in practice.
Enable the Solver Add-in
Solver is an Excel desktop add-in. In Excel for Microsoft 365 on Windows, turn it on once using these steps:
- Select File > Options.
- Choose Add-ins.
- At the bottom, set Manage to Excel Add-ins, then select Go.
- Check Solver Add-in and select OK. If Excel asks to install or load it, approve the prompt.
- Open the Data tab and look for Solver, usually in the Analyze group.
In Excel for Mac, use Tools > Excel Add-ins, check Solver Add-in, and select OK; then look for Solver on the Data tab. The exact layout can vary slightly by release. Solver is not available as the desktop Solver Add-in in Excel for the web, so open the workbook in the desktop app to use it. If Solver is missing after enabling it, check that the correct add-in was selected and restart Excel if necessary.
How to build a Solver model
Arrange the worksheet so the decision inputs, calculations, and limits are easy to inspect. Put the changing cells in a clearly labeled area, make formulas refer to those cells, and place the objective in one identifiable cell. Then select Data > Solver. In the Solver Parameters dialog, set the objective cell, choose Max, Min, or Value Of, identify the changing cells, and add constraints with Add.
For a beginner, a reliable sequence is:
- Write down the question in plain language, including what should be maximized or minimized.
- Identify the decisions Solver can change and enter reasonable starting values.
- Create formulas that calculate the objective and each quantity that will be limited.
- Translate every real-world limit into a constraint, checking its units and direction.
- Select a solving method that matches the formulas, then select Solve.
- Review the proposed values and status. Choose Keep Solver Solution only if the result is acceptable.
Starting values are not necessarily the answer, but they help Solver begin its search. A constraint such as a nonnegative quantity can be entered explicitly, or you can use the Solver option Make Unconstrained Variables Non-Negative. That setting is useful for quantities that cannot sensibly fall below zero, but it should not be applied blindly if negative values are meaningful in the model.
Worked example: choose a product mix
Suppose a small workshop makes products A and B. Each A earns $30 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, and it can make no more than 40 units of A. The simplified question is how many units of each product to make to maximize profit. This is an illustrative calculation, not business advice.
Set up the sheet with the number of A units in B2 and B units in C2. Put their profits per unit 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. With those values in place, use these formulas:
- With units in B2:C2 and profit per unit in B3:C3, put
=SUMPRODUCT(B2:C2,B3:C3)in D2 for total profit. - With units in B2:C2 and labor per unit in B4:C4, put
=SUMPRODUCT(B2:C2,B4:C4)in D4 for labor used. - With units in B2:C2 and material per unit in B5:C5, put
=SUMPRODUCT(B2:C2,B5:C5)in D5 for material used.
In Solver, set the objective to D2 and choose Max. Set the changing cells to B2:C2. Add constraints D4 <= 100, D5 <= 80, and B2 <= 40. Make B2:C2 nonnegative. For this linear model, select Simplex LP and solve. One optimal solution is 40 units of A and 20 of B: it uses 100 labor hours and 80 material units, and earns 40 × $30 + 20 × $20 = $1,600. Both resource limits are met exactly.
This example assumes fractional production would be allowed, although the answer happens to be whole numbers. If a model must produce whole units, add an integer constraint for the relevant changing cells. Integer restrictions can change the solution and make the search more demanding, so only add them when they reflect a real requirement.
Choose the right solving method
The solving method matters because different formulas have different mathematical properties. The method selector appears in Solver Parameters. As a practical starting point:
- Simplex LP: use it when the objective and constraints are linear combinations of the changing cells, as in the product example. A formula such as total profit from fixed per-unit profits is linear.
- GRG Nonlinear: use it for smooth nonlinear formulas, for example when a formula includes a changing input multiplied by itself or a smooth rate calculation. The result can depend on the starting values and may be a local optimum rather than the best possible result overall.
- Evolutionary: consider it for models with discontinuities, IF-driven choices, or other difficult non-smooth relationships. It searches by an evolutionary approach and may take longer; results can require careful checking.
If a model is linear, selecting Simplex LP is usually preferable to leaving the method on an unsuitable choice. Conversely, a model containing nonlinear formulas should not be labeled linear just because its inputs look like ordinary numbers. Inspect the formulas that feed the objective and constraints before choosing.
Read results and check the model
After solving, Excel presents a results message. Keeping the solution writes Solver’s proposed values into the changing cells; restoring the original values puts back the values from before the solve. The worksheet itself remains important: check that every constraint is satisfied, that the objective formula represents the intended measure, and that the suggested values make sense in context. A Solver result is not an independent verification of the assumptions.
Solver can offer reports, depending on the model and method. The Answer Report summarizes the result and constraints. A Sensitivity Report is associated with suitable linear models solved with Simplex LP; it is not available for every type of model. If the workbook is important, save a copy before experimenting, and retain a record of the assumptions and constraints so someone else can understand how the result was obtained.
Common problems and practical fixes
- The changing cells do not affect the objective. Check whether the objective formula actually refers, directly or indirectly, to the cells Solver is changing. If it does not, changing them cannot improve the objective.
- Solver says no feasible solution exists. Review limits for contradictions, mistaken cell references, or the wrong inequality direction. For example, a maximum capacity should usually be expressed as usage less than or equal to capacity, not greater than or equal to it.
- The result is unexpectedly extreme. A missing limit may let Solver increase a quantity without bound, or an objective formula may reward an input that should have a cost or penalty. Add only constraints that reflect actual requirements; do not add arbitrary limits merely to force a preferred answer.
- The result is fractional. Solver treats changing cells as continuous unless you add integer or binary constraints. Use an integer restriction for whole items, or a binary restriction for yes/no decisions.
- A nonlinear result seems poor or changes between runs. Check the starting values, formulas, and solving method. Nonlinear searches can find different local results; compare plausible starting points and verify the outcome rather than assuming the first result is globally best.
Also check units: hours should be compared with hours, not dollars, and percentages should be represented consistently. A quick manual calculation using the returned values can catch a formula error that a successful Solver status will not reveal.
Solver compared with Goal Seek and alternatives
Goal Seek is better when there is one unknown input and one formula target: it adjusts one cell to make a formula reach a specified value. Solver is the better fit when several cells may change, when the goal is to maximize or minimize, or when limits must be observed. Excel data tables are useful for comparing formula outcomes across selected input values, but they do not search for an optimum subject to constraints in the same way Solver does.
For larger or recurring optimization work, Solver may not be the right tool. Its standard Excel implementation is limited to 200 variable cells, and complex models can be slow or difficult to maintain. Depending on the problem, alternatives include restructuring the workbook, using a dedicated optimization package, or using a programming tool with an optimization library. A larger tool is not automatically better: a transparent worksheet model is often easier to audit for a small, well-defined problem.
Frequently Asked Questions
Is Excel Solver free?
Solver is included as an add-in with desktop Excel and does not normally require a separate Solver purchase. Whether you can use desktop Excel depends on your Microsoft 365 or Office license.
Where is Solver in Excel?
After enabling Solver, find it on the Data tab in desktop Excel, usually in the Analyze group. If it is absent, enable Solver Add-in through Excel Options on Windows or Tools > Excel Add-ins on Mac.
Can I use Solver in Excel for the web?
The desktop Solver Add-in is not available in Excel for the web. Open the workbook in desktop Excel for Windows or Mac to set up and run Solver.
Why does Solver give a different answer when I run it again?
For nonlinear models, the starting values and search behavior can lead to different local solutions. Check the method and model, try reasonable starting values, and compare results. For a linear model, verify that it is truly linear and that constraints are entered correctly.
Can Solver make a cell contain only whole numbers?
Yes. Add an integer constraint for the relevant changing cells in Solver. For a yes-or-no decision, use a binary constraint if that option is appropriate. Integer restrictions can make solving more complex, so apply them only where needed.
Related
- Excel Slicer Formatting: Styles, Colors, and Layout
- Excel Slicer Greyed Out? Causes and Fixes
- Excel Slicer: How to Hide Blank Items
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create an Outlook on the Web Email Signature in MinutesTo create a signature in Outlook on the web, open Settings, find the signature settings, write and format…
- Excel Zoom Shortcut: Fast Ways to Change MagnificationIn Excel for Microsoft 365 on Windows, press Alt, then W, then Q to open the Zoom dialog.…
- Hanging Indent in Microsoft Word: How to Set It UpTo create a hanging indent in Microsoft Word, select the paragraph or paragraphs, open the Paragraph settings, and…
- Microsoft Word Booklet: Set Up Pages, Format, and PrintTo create a booklet in Microsoft Word for Microsoft 365 on Windows, open Layout > Margins > Custom…
- Excel Sum Minus: How to Add and Subtract Numbers in ExcelTo subtract a value from a total in Excel, combine SUM with the minus sign: with numbers in…
- Microsoft Word Bullet Hanging Indent: Set It CorrectlyIn Microsoft Word, select the bulleted paragraphs, then go to Home > Paragraph dialog launcher > Indents and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.