Solver in Excel: Optimization Add-in for Linear, Nonlinear, and Mixed-Integer Problems

Solver in Excel is the optimization add-in that ships with every desktop copy of Microsoft Excel. It finds the values of a set of decision cells that maximize, minimize, or hit a target value in an objective cell, subject to constraints you define.

If you've ever wanted Excel to answer a question like "what mix of products gives the highest profit if I only have 200 labor hours and $50,000 of raw material?" — Solver is the tool for the job. Goal Seek handles a single input changing a single output; Solver handles many inputs, many constraints, and any of three different solving algorithms depending on the structure of your problem.

Despite being included in Excel for nearly three decades, Solver remains underused. Most users don't know it's there. Even users who've heard of it often back away because the dialog box looks intimidating and the math sounds graduate-level. The reality is friendlier — Solver is a wrapper around three well-tested optimization algorithms (Simplex LP, GRG Nonlinear, Evolutionary), and the bulk of the work is just laying out your problem clearly on the worksheet. Once the data is in shape, telling Solver what to do takes about thirty seconds.

This guide walks through everything you need to actually use Solver: turning the add-in on (it's installed but disabled by default), the three solving methods and when each one applies, the structure of an optimization problem in spreadsheet form (objective, variables, constraints), four classic real-world problems Solver handles well, the error messages you'll hit and how to read them, how Solver compares to Goal Seek, Data Tables, and Scenario Manager, the variable limits in the bundled version versus the paid Premium Solver upgrades, and the VBA commands for running Solver from automated workbooks.

By the end, you'll have a usable mental model of when Solver is the right tool and when it isn't.

Whether you're building a production schedule, a portfolio allocation, a shipping plan, or a staff roster, the same Solver pattern shows up: lay out the decision cells, write a formula that summarizes what you want to optimize, list the constraints the answer must satisfy, then let Solver search. Some problems solve in milliseconds.

Others — especially nonlinear and integer-heavy ones — can take minutes and still not converge to a clean answer. Reading the result reports tells you which case you're in and whether the answer you got is provably optimal or just the best Solver could find inside its time budget.

Enabling the Solver add-in

Solver ships with Excel but is turned off by default. To switch it on, go to File → Options → Add-ins. At the bottom of the dialog, select Excel Add-ins from the Manage dropdown and click Go. In the small add-ins window that pops up, tick the Solver Add-in checkbox and click OK. Excel installs the add-in and adds a Solver button to the Data tab (far right end, in the Analyze group on most versions). The whole process takes about ten seconds and only needs doing once per Excel install.

On Mac, the path is slightly different: Tools → Excel Add-ins, then check Solver Add-in. The Solver button shows up under the Data tab the same way it does on Windows. Excel for the Web doesn't have Solver — you'll need desktop Excel to run optimization. If the Solver Add-in checkbox isn't visible in the Add-ins dialog at all, it usually means Excel was installed without the optional components; reinstalling or running a repair from Add or Remove Programs restores them.

If Solver was installed by an IT department on a managed machine, the add-in may already be enabled when you open Excel for the first time. You can confirm by checking the Data tab — if a Solver button is visible at the right end, you're ready to go. If not, the enable steps above are all that's required. There's nothing to download separately and no license to activate; the bundled Solver is fully functional within its size limits and runs offline on any Windows or Mac Excel install from 2010 onward.

The bundled Solver is built by Frontline Systems, the same company that makes commercial optimization products. Frontline's website (solver.com) offers paid upgrades — Premium Solver, Risk Solver Platform, Analytic Solver — that lift the variable count, add more solving engines, and integrate with simulation and machine learning workflows. For most spreadsheet optimization the bundled version is plenty. The paid editions only become necessary when problems exceed the size limits or require specific solvers (interior point, large-scale GRG, etc.) not in the standard package.

Setting up an optimization problem in Excel

Every Solver problem has three ingredients on the worksheet: the objective cell (what you're trying to maximize, minimize, or set to a value), the variable cells (the inputs Solver is allowed to change — usually called decision variables), and the constraints (rules the answer must satisfy). The worksheet layout doesn't matter as long as Solver can reference each piece, but a clean convention is decision variables in one row or column, formulas computing intermediate values in another, and the objective formula in a clearly labelled cell at the top or bottom of the layout.

The objective is a single formula cell whose value you want to optimize. In a profit-maximization problem, the objective cell contains =SUMPRODUCT(unit_profits, units_made). In a cost-minimization problem, =SUMPRODUCT(unit_costs, units_shipped). The objective formula must depend on the decision variables — directly or through other formulas — or Solver has no way to change its value. The Set Objective box in the Solver dialog points at this cell, and the Max/Min/Value Of radio buttons tell Solver which way to push it.

The variable cells (also called decision variables or changing cells) are the inputs Solver can adjust. These should usually start at zero or a reasonable guess — Solver uses their initial values as a starting point, and on nonlinear problems the starting values can affect which local optimum it converges to. The By Changing Variable Cells box in the dialog points at this range. Solver tries different values in these cells, watches the objective cell, and homes in on the combination that gives the best objective value while staying inside the constraint rules.

The constraints capture the real-world rules — total labor hours used can't exceed available labor, production must meet minimum order quantities, units made must be whole numbers, percentages must sum to one hundred. Constraints are added one at a time in the dialog using a left-hand-side cell reference, a comparison operator (≤, ≥, =, int, bin, dif), and a right-hand-side value or cell.

The constraints box typically grows to ten or twenty rows for non-trivial problems. Keep them organized — Solver will tell you which constraint is violated when no feasible solution exists, but only by row number, not by description.

Step by step — running Solver the first time

Once your data is laid out and the formulas check out, running Solver itself is a six-step routine. Step one: click the Solver button on the Data tab. Step two: click the box next to Set Objective, then click the objective cell on the worksheet.

Step three: choose Max, Min, or Value Of (and enter a target if Value Of). Step four: click in the By Changing Variable Cells box and select the decision variables. Step five: click Add in the Subject to the Constraints box and enter each constraint one at a time — cell reference, operator, value or cell. Step six: pick a Solving Method from the dropdown and click Solve.

Solver runs and shows a results dialog. If it found a solution, it offers to keep the Solver answer (which has overwritten your variable cells with the optimal values) or revert to the original values. It also offers three reports — Answer, Sensitivity, and Limits — that can be added to new worksheets.

The Answer report summarizes the optimal values and constraint usage. The Sensitivity report shows how much the objective changes if a constraint or coefficient shifts (only available for linear problems). The Limits report shows the range each variable could take while staying feasible with the others fixed at their optimal values.

For ongoing use, the entire Solver setup is saved with the workbook. Reopen the file later, click Solver, and the objective, variables, constraints, and method are all preserved. You can also save multiple Solver models in the same workbook using Load/Save in the Options dialog — useful for keeping several variants of the same problem (different scenarios, different products) in one file. The Options dialog also controls iteration limits, time limits, precision, and convergence tolerance, which you'll touch when default Solver runs don't converge or take too long on large problems.

One habit that saves frustration: always run Solver once with small, hand-crafted test data before unleashing it on the real numbers. Build a five-variable, three-constraint version where you already know the answer. Run Solver. If it returns the answer you expected, your model structure is correct and you can swap in real data with confidence. If it returns something weird, you'll catch the bug in the model setup rather than spending an hour debugging a real-data result that seemed plausible enough to ship before you noticed the constraint pointing at the wrong cell.

Reading Solver's result messages

Solver always returns a message at the end of the run, and reading it correctly is half the skill. "Solver found a solution. All constraints and optimality conditions are satisfied" is the message you want — it means the answer is provably optimal (for Simplex LP and GRG, when it converges). "Solver has converged to the current solution. All constraints are satisfied" means GRG stopped because successive iterations weren't improving the objective much — the answer is feasible but might not be the global optimum. Try different starting values or switch to Evolutionary to check.

"Solver could not find a feasible solution" means no combination of variable values satisfies all the constraints simultaneously. The constraints contradict each other or are too tight. Open the constraint list and look for the bottleneck — usually a constraint that can't be met given the other limits. Relax one constraint at a time until the problem becomes feasible, then you've identified which limit is the binding one. A common cause is forgetting to set variable cells ≥ 0; negative units made or negative percentages allocated lead to weird infeasible models.

"The Set Cell values do not converge" means the objective can grow without bound — your problem is missing a constraint that should be limiting it. In a profit-max problem, this usually means resource constraints aren't connected to the variables properly. Check the constraint formulas reference the actual decision variables. "Stop chosen when iteration limit reached" or "Stop chosen when time limit reached" means Solver was making progress but ran out of budget. Increase Max Iterations or Max Time in the Options dialog and run again, or accept the current answer if it looks reasonable.

"Solver encountered an error value in the objective or a constraint" means one of your formulas returned #DIV/0!, #VALUE!, or another error during the search. Solver tried a combination of variable values that broke a formula. The fix is either guarding your formulas with IFERROR so they return a number instead of an error, or adding constraints that prevent Solver from exploring the bad region (variables ≥ some small positive number to avoid divisions by zero, for example). Diagnose by manually entering values close to where Solver was searching and watching which formula breaks.

Solver vs. Goal Seek vs. Data Tables vs. Scenario Manager

Excel's what-if analysis tools form a small family with overlapping but distinct purposes. Understanding which one fits the question saves a lot of fumbling. Goal Seek answers "what input value makes this output equal a target?" — single variable, single constraint, no optimization. If a loan calculator shows a monthly payment of $1,250 and you want to know what loan principal would produce $1,000, Goal Seek finds it in seconds. Goal Seek lives under Data → What-If Analysis → Goal Seek and runs a simple iterative search with no method choice or constraints beyond the target value.

Data Tables answer "how does this output change as one or two inputs vary across a range?" — sensitivity analysis with no optimization. A one-variable data table shows the objective at every value of a single input. A two-variable data table shows the objective at every combination of two inputs as a grid. Useful for charting how profit responds to price, or how a project NPV changes with discount rate and growth assumptions. Found under Data → What-If Analysis → Data Table after laying out the input range and a formula referencing the input cell.

Scenario Manager answers "what does the model look like under three or four named sets of assumptions?" — case-based comparison, no search or optimization. Define a scenario as a named bundle of input values (Best Case, Worst Case, Most Likely) and Excel lets you switch between them and see how the worksheet responds. Useful for executive-style sensitivity reporting where the question is comparing a small number of curated scenarios rather than exploring a continuous input range. Under Data → What-If Analysis → Scenario Manager.

Solver is the optimization tool the other three aren't. Many decision variables, many constraints, three solving methods, and a search algorithm that finds the optimum rather than just reporting outputs at sampled inputs. For an actual optimization question ("what mix of products maximizes profit?"), Solver is the only built-in answer. For a sensitivity question ("how does profit shift if material costs rise 10%?"), Data Tables or Solver's Sensitivity Report fits better. Knowing which question you're actually asking points you at the right tool immediately rather than after a few wrong starts.

Size limits and the Premium upgrade

The bundled Solver caps out at 200 decision variables and 100 constraints in the free version that ships with Excel. For most spreadsheet optimization that's plenty — a production mix with 30 products and 15 resource constraints is comfortably inside the limit, as is a transportation problem with a dozen factories and a dozen warehouses, or a portfolio optimization across a hundred securities. The variable count includes every cell in the By Changing Variable Cells range, so an optimization across a 20×10 grid of decision values eats 200 variables in a single shot.

When a problem genuinely exceeds those limits, Frontline Systems sells Premium Solver and larger products that lift the caps significantly. Premium Solver Pro handles 2,000 variables and 1,000 constraints. Risk Solver Platform and Analytic Solver products integrate Monte Carlo simulation, decision trees, and machine learning pipelines on top of optimization. Pricing runs from a few hundred to several thousand dollars per seat per year. For occasional large problems, paying for one user and concentrating heavy runs through that workstation is usually more economical than upgrading the whole team.

An alternative for very large problems is to decompose them into smaller chunks that each fit in the standard Solver. A network of 500 routes can sometimes be split into geographic regions of 100 routes each, optimized separately, and stitched together at the edges. Decomposition adds complexity and rarely produces the truly globally optimal answer, but for a one-off study it often gets you 95% of the way there without buying additional software. The art is finding split points where the cross-region interactions are small enough to ignore for the purpose of the decision being made from the analysis.

For problems that demand large-scale optimization regularly, dedicated tools like Python's scipy.optimize, cvxpy, or pulp, R's lpSolve and nlopt, or commercial systems like Gurobi and CPLEX handle thousands to millions of variables. The hand-off from Excel happens when problem size, runtime requirements, or modelling sophistication outgrow what Solver can do efficiently. For exploratory analysis, prototyping, and small-to-mid production use, Excel Solver remains a strong tool that integrates with the rest of the spreadsheet workflow in a way external tools don't.

VBA automation — running Solver from code

For workbooks that run optimization repeatedly — daily, weekly, on demand — wrapping Solver in a VBA macro eliminates the manual setup each time. The Solver VBA functions sit in a separate library that has to be referenced from the VBA project: in the VBA editor, go to Tools → References, scroll to SOLVER, and tick the box. Without this reference, calls to SolverOk, SolverAdd, and SolverSolve fail with a compile error because VBA doesn't know where the functions live.

A minimal Solver macro looks something like: SolverReset to clear any previous setup, then SolverOk SetCell:=Range("D15").Address, MaxMinVal:=1, ByChange:=Range("B2:B11").Address, Engine:=1 to point at the objective and variables and pick Simplex LP, then SolverAdd CellRef:=Range("D20").Address, Relation:=1, FormulaText:="100" for each constraint, and finally SolverSolve UserFinish:=True followed by SolverFinish KeepFinal:=1. Six lines of code, give or take, replace the entire dialog box workflow.

The MaxMinVal codes: 1 maximize, 2 minimize, 3 value of. The Engine codes: 1 Simplex LP, 2 GRG Nonlinear, 3 Evolutionary. Relation codes in SolverAdd: 1 is ≤, 2 is =, 3 is ≥, 4 means integer, 5 means binary. Keep these handy because the Solver VBA documentation isn't always quick to surface them. The full Solver VBA reference is on Frontline's website (solver.com) under the Programming section, and there's a complete code reference inside Excel under Solver → Help when the Solver add-in is loaded.

A common production pattern is to wrap the Solver call in a routine that copies fresh input data into the worksheet, runs Solver, captures the optimal variable values into a results sheet, and loops over multiple scenarios. A workbook can run dozens of optimization scenarios in a few minutes without any manual intervention. This pattern shows up in financial planning models, supply chain dashboards, and resource allocation tools where the same optimization runs against rolling input data and the answer feeds downstream reports automatically through normal worksheet recalculation paths.