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.
What it is: a free add-in bundled with Excel that solves optimization problems — find the cell values that maximize, minimize, or hit a target. How to enable: File → Options → Add-ins → Manage Excel Add-ins → Go → check Solver Add-in → OK. The button appears on the Data tab. Three methods: Simplex LP (linear), GRG Nonlinear (smooth nonlinear), Evolutionary (any function, slower). Free version limits: 200 decision variables, 100 constraints. Common siblings: Goal Seek (one variable), Data Tables (sensitivity), Scenario Manager (named cases).
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.
For problems where the objective and all constraints are linear — meaning they involve only addition, subtraction, and multiplication by constants. No exponents, no IF statements, no MIN/MAX functions inside the formulas Solver touches. Simplex LP is the fastest, most reliable method and always returns a provably optimal answer when one exists. Production mix, blending, transportation, and assignment problems are nearly always linear and should use Simplex LP for best results across hundreds or thousands of variables.
Generalized Reduced Gradient method for smooth nonlinear problems — anything that includes products of variables, exponents, logs, or other curved functions in the objective or constraints. GRG converges quickly when the problem has a single optimum near the starting values but can get stuck on a local maximum if the surface has many peaks. Portfolio optimization with quadratic risk terms, curve fitting, engineering design with nonlinear physics, and pricing optimization with elasticity curves all sit in GRG territory.
A global search method that works on any function, including non-smooth ones with IF statements, lookups, ABS, MIN/MAX, or integer-only variables. Doesn't use derivatives, so it handles discontinuous problems Simplex LP and GRG can't. The trade-off is speed — Evolutionary runs much longer and never guarantees the answer is optimal, only that it's the best found within the time budget. Good for scheduling, routing, layout problems, and anywhere the math isn't smooth enough for GRG to handle reliably.
Real problems sometimes need switching between methods to see which gives the best answer. Start with Simplex LP if the problem looks linear. If Solver complains the model is nonlinear, retry with GRG. If GRG gets stuck or the problem has integers and conditionals, try Evolutionary. The Solver dialog has a Method dropdown that switches between the three without losing your other settings. It's fine to try all three on the same model and compare the answers — that's often what experienced users do.
All three methods can handle integer constraints (variable must be whole numbers) or binary constraints (must be 0 or 1) added as ordinary constraints in the dialog. Integer programming is slower than continuous LP because the solver must search a discrete grid, but Simplex LP with integer constraints handles the classic assignment, set covering, knapsack, and facility location problems well. For mixed-integer nonlinear problems, switch to Evolutionary — the smooth GRG method struggles when some variables must be discrete.
Most spreadsheet optimization problems are linear, even when they don't look like it at first glance. If your formulas use only SUM, SUMPRODUCT, addition, and multiplication by constants, Simplex LP applies and will be fastest. The moment you include IF, MIN, MAX, VLOOKUP, or multiply two decision variables together, you've moved into nonlinear territory and need GRG or Evolutionary. Recognizing which camp your problem sits in before opening Solver saves the cycle of failed runs and confusing error messages later.
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.
A factory makes several products. Each product earns a known profit per unit and uses different amounts of labor, machine time, and raw material. Total labor, machine time, and material per period are limited. Decision variables: how many of each product to make. Objective: maximize total profit (SUMPRODUCT of profits and units). Constraints: total labor used ≤ labor available, total machine time used ≤ machine time available, total material used ≤ material available, units made ≥ 0, units made = integer if products can only be made whole. Simplex LP solves this in milliseconds for any reasonable size.
An investor has a list of assets with expected returns and a covariance matrix of risks. Decision variables: percentage of total capital allocated to each asset. Objective: minimize portfolio variance (a quadratic formula involving allocations and covariances) for a target expected return — or maximize expected return for a target risk. Constraints: allocations ≥ 0, allocations sum to 100%, possibly individual asset limits or sector caps. The quadratic objective makes this a nonlinear problem — use GRG Nonlinear. This is the textbook Markowitz mean-variance optimization, solvable in Excel for portfolios up to a few hundred assets.
Multiple factories ship products to multiple warehouses. Each route has a known per-unit shipping cost. Factories have supply limits; warehouses have demand requirements. Decision variables: units shipped on each factory-to-warehouse route. Objective: minimize total shipping cost (SUMPRODUCT of route costs and units shipped). Constraints: units shipped from each factory ≤ supply, units shipped to each warehouse ≥ demand, all shipments ≥ 0, units = integer if partial shipments aren't allowed. This is one of the cleanest Simplex LP applications and the answer comes back almost instantly even for large networks.
A retail manager covers seven days of shifts with employees who each work five consecutive days. Decision variables: how many employees start their work week on each of the seven days. Objective: minimize total employees hired (sum of starts) while meeting each day's coverage requirement. Constraints: coverage on each day ≥ required staff (each day's coverage is the sum of starts from that day and the four preceding days), starts ≥ 0 and integer. This is a set covering problem — Simplex LP with integer constraints handles it cleanly and produces schedules that real businesses use.
A refinery blends crude oils to produce a finished fuel that must hit minimum quality specs (octane, sulfur, density). Each crude has known properties and a known cost. Decision variables: gallons of each crude in the blend. Objective: minimize total cost (SUMPRODUCT of crude prices and gallons). Constraints: blend octane ≥ minimum, blend sulfur ≤ maximum, blend density between specs, total blend = target volume. Linear constraints make this a Simplex LP problem, identical in structure to the diet, feed, and concrete blending problems used in operations research textbooks for fifty years.
Assignment: match workers to tasks so total cost is minimized, with binary variables for each worker-task pair and constraints that each worker takes one task and each task is taken by one worker. Knapsack: choose which items to put in a fixed-capacity container to maximize total value, with binary variables and a single capacity constraint. Both are classic integer programming problems Solver handles with Simplex LP plus binary constraints. They scale up to a few hundred items in the bundled version, well past most practical problem sizes encountered in real planning work.
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.
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.
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.
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.
Clears any existing Solver settings from the active worksheet. Call this first at the top of any VBA routine that configures and runs Solver, so you start from a clean slate rather than inheriting settings from a previous run. Without SolverReset, leftover constraints or variable references from a different problem can produce confusing results when the macro runs against new data. Listed as Sub call, no arguments required.
Sets the main Solver parameters: objective cell, max/min/value direction, target value (for Value Of), variable cells, and solving method. Syntax: SolverOk SetCell:="$D$15", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$2:$B$11", Engine:=1, EngineDesc:="Simplex LP". The Engine number is 1 for Simplex LP, 2 for GRG Nonlinear, 3 for Evolutionary. Equivalent to filling in the top half of the Solver dialog box programmatically with code.
Adds a single constraint to the model. Called once per constraint. Syntax: SolverAdd CellRef:="$D$20", Relation:=1, FormulaText:="100". Relation codes: 1 is ≤, 2 is =, 3 is ≥, 4 is integer, 5 is binary, 6 is dif (alldifferent for combinatorial problems). To rebuild a model with five constraints, call SolverAdd five times. Equivalent to clicking Add in the constraints section of the Solver dialog box and entering each row by hand.
SolverSolve runs the optimization. SolverSolve UserFinish:=True suppresses the results dialog so the macro doesn't pause waiting for user input. SolverFinish KeepFinal:=1 keeps the Solver answer in the variable cells; KeepFinal:=2 reverts to original values. Pair these two at the end of a Solver macro to make the optimization run silently and leave the optimal values in place for downstream worksheet calculations to use directly without manual confirmation steps.
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.