To run Solver in Excel for Microsoft 365 on Windows, enable the Solver Add-in, prepare a formula for the result you want to optimize, then select Data > Solver. Set the objective cell, the cells Solver may change, and any constraints before choosing a solving method and clicking Solve. Solver is a desktop Excel add-in for finding the best result when several values or limits interact; set up your worksheet first so its formulas and assumptions are clear.
What You’ll Need
- The desktop version of Excel for Microsoft 365. Solver is not available as an add-in in Excel for the web, so open the workbook in the desktop app to run it.
- A worksheet with an objective formula, such as total profit or lowest cost, and cells containing the values Solver is allowed to change.
- Any limits the answer must obey, such as a budget, available hours, minimum quantities, or whole-number requirements.
- A saved copy of the workbook before you begin, especially if it contains important data or you plan to compare different models.
Step-by-Step
- Enable the Solver Add-in. In Windows Excel, select File > Options > Add-ins. At the bottom, beside Manage, select Excel Add-ins and click Go. Check Solver Add-in and click OK. If Excel asks to install or enable it, confirm. Solver should then appear on the Data tab, usually in the Analysis group. On a Mac, use Tools > Excel Add-ins, select Solver Add-in, and click OK; then look for Solver on the Data tab. Menu labels can vary slightly by Excel build. If you use Excel for the web, choose Open in Desktop App because the add-in cannot run there.
- Build a small, readable model. Keep input values, decision cells, formulas, and limits in distinct cells, and label them. For example, suppose a shop makes two products. Put the number of units of product A in B2 and product B in B3, starting both at 0. Put their profit per unit, 30 and 20, in C2 and C3. Put resource used per unit, 2 and 1, in D2 and D3. The shop has at most 100 units of this resource and can make at most 80 products in total. These numbers are illustrative, not a business recommendation.
- Create the objective formula. With decision quantities in B2:B3 and unit profits in C2:C3, enter
=SUMPRODUCT(B2:B3,C2:C3)in B5. This calculates total profit. With both quantities initially zero, B5 returns 0. Solver will change B2 and B3 to increase this result; the formula itself should remain in place. - Calculate each constrained total. With quantities in B2:B3 and resource use per unit in D2:D3, enter
=SUMPRODUCT(B2:B3,D2:D3)in B6. This returns the resource used; with the starting quantities of zero, it returns 0. With product quantities in B2:B3, enter=SUM(B2:B3)in B7 to calculate the total units made, also 0 at the starting values. Put the corresponding limits, 100 and 80, in other clearly labeled cells or keep them nearby for reference. - Open Solver and set the objective. Select Data > Solver. In Set Objective, select B5, either by clicking the cell or typing its reference. Under To, choose Max to maximize profit. Choose Min when the goal is to reduce a cost or use Value Of when you need a specific target rather than the largest or smallest result.
- Choose the changing cells. In By Changing Variable Cells, select B2:B3. These cells represent the quantities Solver can adjust. Do not include formula cells such as B5:B7 in this range: Solver should change the inputs, while the formulas recalculate the outcomes.
- Add the resource and production limits. Click Add in the constraints area. Set B6 to less than or equal to 100, then click Add again and set B7 to less than or equal to 80. Click Add once more and set B2:B3 to greater than or equal to 0, then click OK to return to the main Solver Parameters window. If your model already uses nonnegative variables, you can also select Make Unconstrained Variables Non-Negative instead of adding that constraint. Avoid applying both approaches unless you have a reason to make the rule explicit.
- Select an appropriate solving method. For this example, choose Simplex LP because the objective and constraints are linear: the totals change in direct proportion to the product quantities. Use GRG Nonlinear when formulas include smooth nonlinear relationships, such as a squared value or a curve. Use Evolutionary for models with discontinuities, IF-based choices, or other relationships that the other methods do not handle well. Selecting an unsuitable method can prevent Solver from finding a useful result.
- Run Solver and review its answer. Click Solve. For the example, Solver should find 20 units of A and 60 units of B, with total profit of 1,800. The resource total is 2 × 20 + 1 × 60 = 100, and the production total is 20 + 60 = 80, so both limits are met. In the Solver Results dialog, choose Keep Solver Solution to retain those values, or Restore Original Values to undo the proposed solution. Click OK to close the dialog.
- Check the model before relying on it. Confirm that the objective and constraint cells contain formulas, that the changing cells are the intended inputs, and that every limit reflects the real problem. Try a few different starting values or temporarily adjust an input to see whether the results respond sensibly. Save the workbook after keeping a solution. Solver gives an answer to the model as entered; it cannot confirm that your data, assumptions, or constraints are correct.
Common Mistakes
Using a fixed value instead of a formula. Solver needs an objective cell whose value changes when the variable cells change. If B5 contains a typed number rather than a formula linked to B2:B3, maximizing B5 will not meaningfully optimize the product quantities. The same issue applies to constraint cells: a typed total will not update as Solver changes the inputs.
Leaving out a real-world limit. If you forget the resource constraint, Solver may choose quantities that produce attractive profit but cannot be made. Include all relevant limits, and check their units. A limit in minutes cannot be compared directly with a total measured in hours unless you convert one of them.
Choosing the wrong changing cells or objective direction. Including formula cells among the changing cells can undermine the model, and choosing Min instead of Max can produce the opposite of the intended result. Before clicking Solve, read the objective and variable ranges in the Solver Parameters window and confirm that the goal matches the question you are asking.
Assuming quantities are automatically whole numbers. Solver can return fractions, such as 20.5 units, unless you require integer values. If the decision genuinely represents indivisible items, add an integer constraint to the relevant changing cells. Do not round a fractional answer after solving without checking all constraints again; rounding can make a previously valid answer infeasible.
Using a linear method for a nonlinear model. Simplex LP is appropriate when the objective and constraints are linear. If formulas contain products of changing cells, powers, or nonlinear functions, the model may need GRG Nonlinear or Evolutionary instead. The method menu is not a way to repair an incorrect formula, so inspect the model structure as well.
Tips
- Give cells descriptive labels and use a consistent layout. A clearly labeled objective, decision-variable range, and constraint total make the Solver window easier to check and the workbook easier to maintain.
- Start with realistic initial values. Solver can often find a solution from zero in a simple linear model, but a more complicated nonlinear model may be sensitive to its starting point. If a nonlinear run fails, try several reasonable starting values and compare outcomes.
- Use the narrowest suitable variable range and add only meaningful constraints. Extra constraints can make a model harder to solve, while missing constraints can make its answer impractical.
- Keep a baseline copy or record the original input values before solving. Solver can overwrite the changing cells when you keep a solution, and retaining a result makes it easier to compare scenarios.
- When you change a key assumption, run Solver again. A saved solution does not automatically represent a newly edited model; the formulas may recalculate, but the decision variables may no longer be optimal or satisfy the intended limits.
- For a model with whole-number decisions, explicitly add integer constraints and verify that the solution still meets every limit. Integer restrictions can make a problem harder to solve and can change the best result.
Troubleshooting
Solver is missing from the Data tab: Check whether the add-in is enabled. On Windows, revisit File > Options > Add-ins, choose Excel Add-ins beside Manage, click Go, and check Solver Add-in. On a Mac, check Tools > Excel Add-ins. If you are in a browser, open the workbook in desktop Excel.
Solver reports that it cannot find a feasible solution: A feasible solution is one that satisfies every constraint. Check for contradictory limits, such as requiring a total to be at least 100 and at most 80. Confirm that the inequality signs point in the intended direction, units match, and constraints refer to the correct cells. Temporarily remove one constraint at a time to identify which one rules out all possible answers, then restore it with the corrected value or logic.
The result does not change or looks wrong: Make sure the objective formula depends on the changing cells and that the changing-cell range contains the intended inputs. Check for hard-coded totals, incorrect cell references, and formulas that do not recalculate as expected. Then confirm whether the goal is Max, Min, or Value Of and whether every important limit is present.
Solver stops without a satisfactory answer: Recheck the selected method. Use Simplex LP for a linear model, GRG Nonlinear for smooth nonlinear formulas, and Evolutionary for certain discontinuous or highly irregular models. For nonlinear problems, try different reasonable starting values. If the model is large or complex, simplify it or solve a smaller section first to locate formula or constraint problems.
The solution contains fractions or violates expectations: If a decision must be a whole number, add an integer constraint to those variable cells and solve again. If the displayed result appears to violate a limit, inspect the underlying values and formulas rather than relying on rounded display formatting. Increase the displayed decimal places if necessary and check whether the constraint was entered against the intended total.
Frequently Asked Questions
Is Solver included in Excel?
Solver is available as a Microsoft Excel desktop add-in, but it may need to be enabled before it appears on the Data tab. It is not available to run in Excel for the web.
What is the difference between Solver and Goal Seek?
Goal Seek finds one input value that makes a formula reach a specified result. Solver can change multiple cells at once and account for multiple constraints while maximizing, minimizing, or targeting an objective.
Can Solver change more than one cell?
Yes. Enter a range or several variable cells in By Changing Variable Cells. In standard Excel Solver, the changing-cell limit is 200, so a problem with more decision variables may require a different optimization tool or a redesigned model.
Can I use Solver for budgets or costs?
Yes, if the workbook represents the amounts, formulas, and limits correctly. For money calculations, check the real terms and assumptions with the lender or a qualified professional; a Solver result is only as reliable as its inputs and does not constitute financial, tax, or legal advice.
How do I keep Solver’s result?
When the Solver Results dialog appears, select Keep Solver Solution and click OK. Select Restore Original Values if you want to discard the proposed values and return the changing cells to their prior entries.
Why does Solver give a different answer after I change the starting values?
For a linear model with a suitable method, the best objective value should generally remain consistent if the model has a well-defined optimum. Nonlinear models can have multiple local solutions or be sensitive to starting values, so compare results from reasonable starts and check that each satisfies the constraints.
Related
- Print Excel Sheets With Gridlines: Settings and Fixes
- Creating a Table in Excel from Data: A Practical Guide
- Create a Table in Excel With a Title: Step-by-Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Import CSV Decimal Separator: How to Set ItTo import a CSV into Excel with the intended decimal separator, use Data > From Text/CSV, choose the…
- Excel Blue Border Around Print Area: What It MeansA blue border around a worksheet in Excel usually means you are viewing it in Page Break Preview,…
- How to Change Excel Shortcut Keys in Microsoft 365To change an Excel shortcut on Windows, assign a shortcut to a macro in Developer > Macros >…
- How to Insert a Line in Word Under Text: Easy MethodsTo put a line under text in Word, select the text and press Ctrl+U on Windows or Command+U…
- Small Excel Sample Data: Examples to Copy and Use NowSmall Excel sample data is a short, clearly labeled set of rows you can paste into a worksheet…
- How to Compare Two Lists in Google Sheets Step by StepTo compare two lists in Google Sheets, put the first list in column A and the second in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.