Excel Solver is an optimization add-in that finds values for selected cells to maximize, minimize, or reach a target in a formula, while respecting constraints you define. In Excel for Microsoft 365 on Windows, enable it through File > Options > Add-ins, choose Excel Add-ins beside Manage, select Go, check Solver Add-in, and then open Data > Solver.
What Excel Solver Does
Solver answers questions such as “How many of each product should I make to earn the most, given limited time and materials?” You provide a formula that measures the outcome, cells Excel is allowed to change, and any limits those cells must obey. Solver searches for a set of values that meets the limits and improves the outcome.
It is useful when a decision involves several interacting choices. Examples include allocating staff hours, selecting a product mix, planning shipments, balancing ingredients, or choosing a portfolio of projects under a fixed budget. Solver does not decide what a good business objective is, interpret uncertain data, or guarantee that the assumptions in a model are realistic. The quality of its answer depends on the quality of the spreadsheet model.
Solver is different from the Analysis ToolPak. The ToolPak provides statistical and engineering analyses, such as regression and descriptive statistics. Solver is for optimization: it changes decision cells to find a solution to a defined objective and constraints. Both are Excel add-ins, but enabling one does not enable the other.
How to Enable Solver in Excel
Solver is included with desktop Excel for Windows, but it may not be turned on initially. Use these steps in Microsoft 365 for Windows:
- Select File > Options.
- In the Excel Options window, select Add-ins.
- At the bottom, beside Manage, choose Excel Add-ins, then select Go.
- Check Solver Add-in and select OK. If Excel asks to install or load the feature, confirm the prompt.
- Open the Data tab and look for Solver, usually in the Analysis group.
If Solver is missing from the list, check that Excel is installed correctly and that your organization has not restricted add-ins. On a Mac, the route differs: use Tools > Excel Add-ins, select Solver Add-in, and confirm. Solver is then available from the Data tab. Excel for the web does not provide the desktop Solver add-in, so open the workbook in the desktop application to set up and run a Solver model.
Enabling Solver does not change the workbook’s data by itself. It makes the command available. Solver settings and results can be saved with a workbook, but another person may need Solver enabled in their own desktop Excel installation to run the model.
How a Solver Model Works
A model has three main parts. The objective cell contains a formula for the result to improve. The variable cells contain the decisions Solver may change. Constraints set the permitted range or relationship for those decisions, such as a production limit, a minimum requirement, or a rule that quantities must be whole numbers.
In the Solver Parameters window, set the objective cell and choose Max, Min, or Value Of. Value Of asks Solver to reach a particular target. Set By Changing Variable Cells to the cells representing decisions, then add constraints with Add. Select a solving method, and choose Solve. Solver evaluates the model and displays a results dialog. You can keep the solution or restore the original values.
For a simple linear model, such as maximizing profit with linear resource limits, choose Simplex LP. If formulas are smooth but nonlinear, GRG Nonlinear may be appropriate. Evolutionary is intended for models with discontinuities or highly irregular relationships where the other methods are not suitable. The method matters: a model that does not fit the selected method may fail to solve or return an unsuitable result.
Solver is not a command that automatically understands the meaning of labels such as cost, hours, or demand. It reads cell references and formulas. A constraint like resource used less than or equal to capacity must be represented by formula cells and the correct comparison in Solver.
Worked Example: Choosing a Product Mix
Suppose a small workshop makes Basic and Premium items. Each Basic item contributes $30, uses two machine hours and three labor hours. Each Premium item contributes $50, uses four machine hours and two labor hours. The workshop has 100 machine hours and 90 labor hours available. The aim is to maximize total contribution without exceeding either capacity.
Put the number of Basic items in B2 and Premium items in C2; put their contributions, machine hours per item, and labor hours per item in B3:C5. Put the total contribution formula in E2. With quantities in B2:C2 and contributions in B3:C3, enter =SUMPRODUCT(B2:C2,B3:C3) in E2. At the optimal quantities of 20 Basic and 15 Premium items, it returns 1350, or $1,350.
Use E4 and E5 to calculate resources used, with the capacities in F4 and F5. With quantities in B2:C2 and machine hours per item in B4:C4, enter =SUMPRODUCT($B$2:$C$2,B4:C4) in E4. At the optimal quantities, it returns 100 machine hours. With labor hours per item in B5:C5, enter =SUMPRODUCT($B$2:$C$2,B5:C5) in E5; it returns 90 labor hours. Enter 100 in F4 and 90 in F5.
In Solver, set the objective to E2 and choose Max. Set the changing cells to B2:C2. Add E4 <= F4 and E5 <= F5, and make B2:C2 nonnegative. If the items can only be produced in whole units, add an integer constraint for B2:C2. Choose Simplex LP and select Solve. In this example the intersection of the two resource limits is 20 Basic and 15 Premium: machine use is 2×20 + 4×15 = 100, labor use is 3×20 + 2×15 = 90, and contribution is 30×20 + 50×15 = 1,350.
The example illustrates why the formulas matter. If the machine-use formula accidentally points to the contribution row, Solver may still produce an answer, but it will be optimizing the wrong model. Check each formula and constraint against the real-world definition before relying on the result.
Choosing the Solving Method
Choose a method based on the relationships in the model, not simply on which option appears first. Simplex LP is suitable when the objective and constraints are linear: changing a decision variable by one unit has a consistent effect on the relevant totals. SUMPRODUCT formulas with fixed coefficients often form part of a linear model.
GRG Nonlinear is designed for smooth nonlinear models. For instance, a formula involving a variable multiplied by itself may create a nonlinear relationship. Such models can have more than one locally optimal answer, so a result may depend on the starting values. It is often useful to try reasonable starting points and check whether the model’s answer makes sense.
Evolutionary can handle some discontinuous or non-smooth models, but it may take longer and its search is not the same as a proof that the best possible answer has been found. Use it when the model’s structure calls for it, and review the result rather than treating it as automatically definitive. Integer and binary constraints can also make an otherwise straightforward model more demanding.
Understanding Solver Results and Reports
After Solver finishes, review its status message and the results dialog. Keep Solver Solution applies the proposed values to the changing cells; Restore Original Values returns those cells to their prior values. Save a copy before a substantial modeling exercise, particularly if the workbook contains important inputs or linked calculations.
Where available for the chosen model and method, Solver can produce reports such as Answer, Sensitivity, or Limits. An Answer Report summarizes the objective, variable values, and constraints. A Sensitivity Report provides information about how changes to certain model inputs may affect a linear solution; it is not available for every model type, including models with integer constraints. A Limits Report shows ranges for decision variables under the report’s assumptions. Read reports in the context of the model, because they do not validate the underlying assumptions or data.
A useful check is to recalculate the important totals independently or by inspection. Confirm that resource use does not exceed capacity, required minimums are met, units are consistent, and the objective formula reflects the intended measure. If a solution sits exactly at a constraint, that may be expected; it does not by itself indicate an error.
Solver Compared with Goal Seek and Other Tools
Goal Seek is a better fit when there is one formula, one input cell, and a desired result. For example, it can find the sales quantity needed to reach a target profit when the other assumptions are fixed. It does not offer Solver’s general framework for changing multiple cells subject to multiple constraints.
Excel’s Data Tables can show how one or two input assumptions affect a formula across a range of values. They are useful for sensitivity-style what-if analysis, but they do not search for an optimum under a collection of constraints. Solver is the relevant built-in tool when Excel must choose values to optimize an objective.
For a small, clearly defined model, the included Solver add-in may be enough. Larger or more specialized optimization problems may call for a dedicated optimization package, a programming language, or a cloud service. Those alternatives can support different model sizes, algorithms, collaboration, or automation, but they may involve extra cost, setup, or data-handling considerations.
Common Problems and Practical Checks
- Solver is not on the Data tab: Confirm the add-in is enabled, then close and reopen Excel if necessary. On a Mac, enable it through Tools > Excel Add-ins rather than the Windows Options dialog.
- Solver reports no feasible solution: Check whether the constraints conflict. For example, a minimum production requirement may be impossible under the available capacity. Temporarily review constraints one at a time and verify their cell references and comparison signs.
- The answer seems unreasonable: Check units, formula references, objective direction, and whether the changing cells are the intended decisions. Also check that lower or upper bounds have not been omitted.
- Solver finds a fractional quantity: Add an integer constraint if the decision represents indivisible items. Do not round the answer afterward without checking the resource limits and objective again.
- The model solves slowly or inconsistently: Reduce unnecessary formulas, check for accidental nonlinear relationships, select the suitable solving method, and provide sensible starting values where relevant.
One frequent misconception is that selecting a solving method fixes a flawed spreadsheet. It does not. Solver works from the formulas and constraints supplied, and a mathematically valid solution can still be operationally wrong if the model leaves out a cost, uses the wrong unit, or treats an uncertain estimate as a fixed fact.
Cost, Availability, and Safe Use
The standard Solver add-in is included with desktop Excel; there is not normally a separate Solver purchase for ordinary use with a supported Excel installation. Access can vary with licensing, platform, and organizational controls. Third-party optimization products are separate tools and may have their own prices, features, limits, and privacy terms.
Solver changes the values in the cells identified as decision variables when you keep a solution. Preserve an original workbook or save a working copy before making major changes. If the workbook is shared, explain which cells are inputs, which cells are Solver variables, and which result was selected. A saved Solver solution is not a substitute for documenting the assumptions behind it.
Frequently Asked Questions
Is Excel Solver free?
Solver is a built-in add-in for desktop Excel and generally does not require a separate purchase when you have an eligible Excel installation. Your organization’s license and software policies determine which desktop features are available.
Where is Solver in Excel?
After enabling the Solver Add-in, look on the Data tab in the Analysis group. If it is missing, enable it from File > Options > Add-ins 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 to run in Excel for the web. Open the workbook in desktop Excel for Windows or Mac to configure and use Solver.
What is the difference between Solver and Goal Seek?
Goal Seek adjusts one input cell to make a formula reach a target. Solver can change multiple decision cells and optimize a maximum, minimum, or target while observing multiple constraints.
Which Solver method should I choose?
Use Simplex LP for linear objectives and constraints, GRG Nonlinear for smooth nonlinear models, and Evolutionary for certain discontinuous or irregular models. If uncertain, inspect the formulas and try the method that matches their mathematical structure, then validate the result.
Related
- Excel Circular Reference Cannot Be Listed? How to Find It
- Excel Circular Reference Greyed Out? Find the Cause
- Excel Named Range Scope Greyed Out? How to Change It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Get Alternating Color Rows in Excel AutomaticallyTo get alternating color rows in Excel, select your data, choose Home > Format as Table, and pick…
- How to Track Changes in Microsoft Word and Review EditsTo track changes in Microsoft Word, open the document, select Review > Track Changes, then edit as usual.…
- How to Lock Cells in Excel on SharePoint Step by StepTo lock cells in an Excel workbook stored on SharePoint, open it in the Excel desktop app, unlock…
- Google Sheets: Duplicate a Data Validation RuleTo duplicate a data validation rule in Google Sheets, copy a cell that already has the rule, select…
- Microsoft Excel Scatter Plot: How to Create and Read OneIn Microsoft Excel for Microsoft 365 on Windows, put the X values in one column and their matching…
- Excel Time Duration Format: Display Hours Beyond 24To show an elapsed time in Excel without resetting the hours after 24, apply the custom number format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.