Choose Simplex LP when your model is linear, GRG Nonlinear when it is smooth nonlinear, and Evolutionary when it is non-smooth; GRG Nonlinear is the default method in Solver. Solver must be enabled first (File > Options > Add-ins > Manage: Excel Add-ins > Go), after which its button appears in the Analysis group on the Data tab.
Excel Solver is one of those tools that sits inside Excel quietly, doing nothing, until you switch it on. Once you do, it turns a regular spreadsheet into a small optimization engine. You hand it an objective, a few decision cells, and a list of rules, and it works out the best answer for you. No guesswork, no manual trial and error. The same engine that powers university operations-research courses lives inside the spreadsheet you already own.
If you've ever tried to figure out the best product mix, the cheapest shipping plan, or the smartest way to split a budget, you'll know how painful that gets with pure formulas. Solver handles it. This guide walks you through what it does, how to switch it on, how to use the three solving methods, and how to read the reports it spits out. You'll also see a full production-mix example you can copy and tweak for your own numbers.
By the end of this guide, you'll know how to enable Solver, pick between Simplex LP, GRG Nonlinear, and Evolutionary methods, write constraints correctly, run a production-mix optimization, and read Answer and Sensitivity reports. You'll also see common errors and how to fix them fast. Pair what you learn here with an Excel dashboard and you can turn optimization results into something the whole team can read at a glance.
| Solving method | Use for (Microsoft) | VBA Engine code | Reports |
|---|---|---|---|
| GRG Nonlinear (default) | Problems that are smooth nonlinear | 1 | Answer, Sensitivity, Limits |
| Simplex LP | Problems that are linear | 2 | Answer, Sensitivity, Limits |
| Evolutionary | Problems that are non-smooth | 3 | Answer, Population |
Constraint relations available in all cases: <=, =, >=, int, bin, dif (int, bin and dif apply only to decision variable cells). Standard Solver accepts up to 200 variable cells. Sources: Microsoft Support, "Define and solve a problem by using Solver"; Microsoft Learn, SolverOk, SolverAdd and SolverFinish.
So what exactly is Solver? It's a free, built-in Microsoft Excel add-in for solving optimization problems. You give it a target cell (say, total profit), tell it which cells it can change (units to produce), and lay down the constraints (labor hours, material limits, demand caps). It then searches for the values that make your target as big or as small as possible, while staying inside every rule. Operations researchers, finance pros, supply chain planners, and engineers all lean on it. So do students learning linear programming for the first time.
Under the hood, portions of the Solver code are copyright Frontline Systems, according to Microsoft's documentation. The basic engine handles three big classes of problems: linear, smooth nonlinear, and non-smooth. Each gets its own algorithm. You don't need to know the math to use them. You do need to pick the right one for your model, or the solve will fail or return a useless answer.
Compared with building your own search loop in VBA or Python, Solver is a shortcut. It hides the math and gives you a clean dialog box. The trade-off? It has limits. The limit that matters most is documented by Microsoft: you can specify up to 200 variable cells. For most business problems, those limits don't bite.
Solver isn't switched on by default. You have to flip it on once, then it sticks around. Here's the click path: File, Options, Add-ins. At the bottom of the Add-ins screen, where it says "Manage: Excel Add-ins," hit Go. Tick the box next to "Solver Add-in" and press OK. That's it. Head over to the Data tab and you'll see Solver sitting in the Analysis group on the right side of the ribbon. Click it, and the dialog opens.
On a Mac? Same idea. Tools menu, Excel Add-ins, tick Solver. Done. If you're on Excel for Web, bad news: Solver isn't there. You need the desktop version.
Click File, then Options at the bottom of the left rail.
In the Excel Options window, click Add-ins on the left side.
At the bottom, set Manage to Excel Add-ins and click Go.
Check the Solver Add-in box in the popup and click OK.
Switch to the Data tab. Solver now lives in the Analysis group on the far right.
Once Solver is open, the dialog can look busy. Don't let it scare you. There are really only four pieces you have to fill in, plus one dropdown to pick the method. Set Objective is the cell you want to make big, small, or equal to a specific number. To is the direction: Max, Min, or Value Of. By Changing Variable Cells is where your decision cells go, the ones Solver is allowed to tweak. Subject to Constraints is your list of business rules.
Then there's Solving Method. Three options. Simplex LP for linear problems, GRG Nonlinear for smooth curves, Evolutionary for messy stuff with IF, ABS, or OR. Pick wrong and Solver might still find an answer, but it'll be slow or just plain wrong. The Options button lets you tweak tolerance, iteration limits, and precision if you need finer control.
Simplex LP is your go-to for linear programming. Both the objective and every constraint must be linear, meaning no multiplication of decision variables, no IF, no ABS, no nonlinear math. When the model qualifies, Simplex is fast and finds the exact optimal solution every time.
Use it for production planning, transportation, blending problems, and resource allocation. If you're maximizing profit by choosing how many of products A, B, and C to make, with labor and material caps, this is the method. It also supports the Answer, Sensitivity and Limits reports (the Sensitivity report is available with Simplex LP and GRG Nonlinear).
GRG Nonlinear stands for Generalized Reduced Gradient. It handles smooth nonlinear functions, things like x squared, square roots, and exponents. It is the default method when you open Solver.
The catch: GRG can land on a local optimum, not the global one. Try different starting values to see if it changes. It's perfect for portfolio optimization with a quadratic risk term, curve fitting, and engineering design. Answer, Sensitivity and Limits reports are available with this method.
Evolutionary is a genetic algorithm. It throws random candidate solutions at the problem, picks the best, and mutates them. It's slow, but it doesn't care if your model uses IF, ABS, OR, VLOOKUP, or other non-smooth functions.
Use Evolutionary for scheduling, routing, and any problem where the formula graph has jumps or kinks. Set both upper and lower bounds on your decision cells; Solver can otherwise stop with "All variables must have both upper and lower bounds." Evolutionary produces Answer and Population reports. It rarely guarantees the true global optimum, so run it a few times with different starting points.
Let's walk through a classic example so the abstract bits land. Picture a small factory making three products: A, B, and C. Each unit of A makes $10 in profit, B makes $15, and C makes $20. Sounds like a no-brainer — make all C, right? Not so fast. C eats 12 hours of labor and 4 pounds of material per unit. You only have 100 labor hours and 50 pounds of material this week. Plus, the market only wants 10 As, 8 Bs, and 5 Cs at most. Suddenly the math gets interesting.
This is exactly the kind of puzzle Solver was built for. You set up the worksheet with a row for each product, columns for profit, labor needed, material needed, demand cap, and a decision cell for units to make. Your objective cell totals up profit using SUMPRODUCT. Open Solver, point it at the profit cell, set to Max, pick the decision cells, add your constraints, choose Simplex LP, and click Solve. When it finishes, the optimal mix appears in the decision cells.
Try this by hand and you'd be stuck for an afternoon, sketching tables and crossing out combinations. Solver handles a model this small with no manual trial and error. That's the magic. The same template scales: swap in twenty products, ten resources, and a handful of customer-specific demand caps, and Solver still cracks it. The setup discipline matters more than the size. Label every row clearly, keep formulas one cell away from raw inputs, and lock the inputs into a single area so anyone can update them next quarter without breaking the model.
Rows for A, B, C with profit, labor, material, demand columns.
One cell per product showing units to produce. Start at 0.
Total profit = SUMPRODUCT of units and profit per unit.
Data tab, Analysis group, click Solver.
Point Solver at the total profit cell, choose Max.
Select the three units-to-produce cells.
Labor <= 100, material <= 50, demand caps, integer, >= 0.
Linear objective and constraints means Simplex is the right call.
Click Solve. Review the answer report, accept the solution.
Constraints are where Solver beginners trip up. Excel lets you use six operators inside the Add Constraint dialog: less than or equal to, equal to, greater than or equal to, int for whole numbers, bin for binary 0 or 1, and dif for all-different values. You'll use the first three most. The int and bin operators come into play when units must be whole or when you're modeling yes/no decisions like opening a warehouse, picking a vendor, or choosing whether to run a marketing campaign in a given week.
The dif operator is the odd one out. It forces every cell in a range to take a different value, which is handy for permutation problems like sudoku or scheduling exam slots. Watch out: each non-trivial constraint slows Solver down, and integer constraints slow it down a lot. If your solve takes more than a minute, look for redundant constraints first. Often two rules say almost the same thing, and dropping one cuts the search space in half. Group similar constraints with named ranges so you can see the structure at a glance.
When Solver finishes, it asks if you want to keep the solution and offers reports; with Simplex LP or GRG Nonlinear you can choose Answer, Sensitivity, and Limits. Tick all three boxes the first time you solve. Each lands on a new tab in your workbook. The Answer Report lists final values, original values, and how much slack you had on each constraint. Slack of 0 means a constraint is binding, so it's actively limiting your solution. Slack greater than 0 means you have wiggle room and that resource isn't fully used.
Sensitivity Report is the real gem, and Microsoft lists it for both Simplex LP and GRG Nonlinear. It shows shadow prices — how much your objective would change if you loosened a constraint by one unit. If labor has a shadow price of $1.25, every extra hour of labor adds $1.25 in profit. That's how you decide whether overtime is worth it. If overtime pay is $1.50 an hour, skip it. If it's $0.80, work the extra hours. The Limits Report shows how high or low each decision variable can go without breaking the solution, useful for stress-testing your plan.
Reports also help with explaining results to non-technical stakeholders. Print the Answer Report, highlight the binding constraints, and you've got a clear story: "We're maxed out on material; investing in more would unlock $X in additional profit." That kind of dollar-value insight makes Solver indispensable in operations meetings. Take the time to interpret reports rather than just accepting the numbers, and Solver pays you back many times over.
Solver throws a few error messages you'll meet sooner or later. The most common: "Solver could not find a feasible solution." Translation — your constraints contradict each other. Maybe you said labor must be at least 120 hours but you only have 100. Loosen something, or open the Answer Report to see which constraints are tight. "The objective cell values do not converge" usually means your problem is unbounded; profit can grow forever because you forgot an upper limit. Add one. A missing demand cap is the classic culprit here.
"The set cell values do not converge" is a GRG problem. The nonlinear search bounces around without settling. Try a different starting point, or switch to Evolutionary if the model has IF or ABS. Hash-style errors like #REF! or #VALUE! mean your formulas have bugs before Solver even ran. Fix the worksheet first. Run a quick sanity check by computing the objective manually for a known input. If Excel and your calculator disagree, the bug is in the worksheet, not in Solver.
Another quiet trap: Solver doesn't always tell you the answer is just a local optimum. With GRG Nonlinear, the algorithm climbs the nearest hill and stops. There could be a taller hill on the other side of the valley. The cure is multistart — re-run with several random starting points and keep the best result. Solver's GRG options include a Multistart setting that tests multiple starting points, or you can re-run by hand with different starting values.
Linear programming covers production mix, blending, and transportation. Mixed-integer programming adds whole-number rules for things like capital budgeting (invest in project or not) and shift scheduling. Both lean on Simplex LP with integer constraints layered on top.
Integer problems are NP-hard in theory, which means they get slow fast as you add variables. Integer constraints slow Solver down, so keep the number of int and bin cells modest.
Portfolio optimization picks the asset mix that maximizes return for a given risk level. The risk side uses a quadratic formula, so you'll need GRG Nonlinear. Assignment problems pair workers with tasks, machines with jobs, or trucks with routes. They're usually linear and binary, so Simplex LP fits.
Network flow problems — shortest path, max flow, min cost flow — are also linear. A spreadsheet of nodes and arcs with flow variables and balance constraints is all you need.
Curve fitting fits a parametric equation to data. Put your parameters in cells, write the predicted-value formula, compute the squared residuals, and tell Solver to minimize the sum. Use GRG Nonlinear.
That said, Excel's built-in TREND, LINEST, and the Analysis ToolPak Regression are faster for linear curves. Save Solver for nonlinear fits where those tools can't help.
Now let's compare Solver to its cousins. Goal Seek lives one menu away in Data, What-If Analysis. It's a one-shot tool: one input cell, one target cell, find the input that hits the target. That's it. No constraints, no multiple variables. If you just want to know what discount rate makes your NPV exactly zero, Goal Seek is perfect. Loan calculators love it. So do break-even analyses.
What-If Analysis goes further with Data Tables and Scenario Manager. Data Tables vary one or two inputs and show a grid of outcomes. Scenarios save sets of inputs. Neither finds optimal values, though. They just show you what happens if. Solver is the only built-in tool that searches for the best answer across many variables with rules. For richer comparisons, a pivot table in Excel alongside scenario outputs can summarize patterns quickly.
You can mix tools, too. Build a base model, run Solver to find the optimal plan, then drop a Data Table next to it to see how sensitive that plan is to small input changes. Or save several Solver runs as Scenarios so a manager can flip between them. Stacking the tools this way gives you both an answer and the context around it.
Sensitivity analysis is worth its own paragraph because it's the part most people skip. After a Simplex LP solve, the Sensitivity Report tab gives you shadow prices and allowable ranges. Shadow prices tell you the marginal value of each constraint. A shadow price of $3 on material says one extra pound of material adds $3 to profit. If material costs $2 a pound, buy more. If it costs $4, don't. Simple decision, real money.
Allowable increase and decrease show how much each coefficient or constraint can shift before the optimal mix has to change. If your labor cap can go from 100 hours up to 130 with the same product mix staying optimal, you've got room. Past 130, Solver would pick different products. This kind of insight is what turns Solver from a calculator into a strategy tool. To visualize ranges, try conditional formatting in Excel on the report so the binding constraints jump out in red.
Beyond business, Solver shows up in plenty of fields.
Our Excel formulas cheat sheet covers SUMPRODUCT, INDEX, MATCH, and the other workhorses you'll plug into Solver-driven models. Logic-heavy setups often rely on multiple IF statements, but remember those force you to use Evolutionary instead of Simplex. Plan your formulas around the method you want to use, not the other way round. Smart modelers keep the objective and constraints purely linear when they can, then handle messy logic in a pre-processing step before Solver ever sees the model.
What about Excel versions and platforms? Solver ships with Excel 2016, 2019, 2021, 2024, and Microsoft 365 on Windows and Mac. It's the same engine on both. Excel for Web does not include Solver, and neither do the mobile apps. If your team works mostly online, that's worth knowing. The workaround is to build the model in the desktop app, save, and view results online.
For bigger problems, other optimization tools exist. Open-source alternatives exist too. OpenSolver is a free Excel add-in built on COIN-OR. Outside Excel, Python with PuLP or SciPy gives you free industrial-strength optimization if you can code. Pick the tool that fits your problem size and team skills, not the other way around.
One last practical note: Solver plays nicely with VBA. You can record or write a macro that fills the Solver dialog, runs the solve, and grabs the result. That's how teams build repeatable optimization tools. Picture a weekly production planner where the user clicks one button, Solver runs against fresh demand and inventory data, and the spreadsheet writes the plan to a tab. No menu clicking, no missed constraints. The VBA reference is SolverOk, SolverAdd, SolverSolve, SolverFinish. Worth learning once if you'll run the same model more than a few times.
If you're worried about reproducibility, save the model with Solver's Save Model button. It writes the objective, variables, constraints, and method into a range of cells you choose on the sheet. You can load it back later with Load Model. Pair that with a documented set of assumptions on a notes tab and your future self (or your replacement) will thank you. Optimization models age poorly without notes; six months later, no one remembers why constraint 7 was set to 42.
Bottom line: Excel Solver is a small, free piece of software with disproportionate power. Switch it on, pick the right method, lay down clean constraints, and read the reports. You'll solve problems that used to take days in minutes. Start with the production-mix example above, plug in your own numbers, and go from there.