How to Use Solver in Excel: Objective, Variables, and Constraints
Learn how to use Solver in Excel: set the objective cell, define decision variables, add constraints, pick Simplex LP, and read the Answer report.
Turn a spreadsheet layout into a solvable optimization model.
Learning how to use solver in excel means wiring three spreadsheet ideas together: one objective cell, one or more adjustable variable cells, and constraints that limit what Solver may choose. The dialog looks intimidating only until those three roles have obvious addresses on the sheet.
For the full build-or-buy guide to structured Solver workbooks, see the Excel Solver example template hub.
Key takeaway: Put the objective in a single formula cell, unlock the decision-variable range, express every limit as a constraint row, choose the engine that matches linearity, then read the Answer report—not just the status message.
Map the model on paper first
Write the story in plain language: "Choose production quantities to maximize profit without exceeding machine hours or warehouse space." Translate that sentence into labels on the sheet before opening the dialog. Microsoft's define and solve with Solver article uses the same sequence.
Build the worksheet layout
Objective cell
The objective is one cell—often total profit:
=SUMPRODUCT(B5:B7,C5:C7)
where B5:B7 are units to produce and C5:C7 are per-unit profits. For a cost minimization model, the objective still sits in one cell; you select Min in the dialog.
Decision variables
Variable cells must contain numbers Solver can adjust. Unlock them on the Format Cells > Protection tab if the sheet is protected. Keep variables in a contiguous block so the By Changing Variable Cells box can reference B5:B7 instead of scattered addresses.
Constraints as formula rows
Each constraint compares a formula to a limit. Capacity might read:
=SUMPRODUCT(B5:B7,D5:D7) <= 1200
where D5:D7 are hours per unit and 1200 is available hours. In the Solver dialog, add F10 <= H10 if F10 holds the left side and H10 the limit. Excel Easy shows similar layouts for small tutorials.
Load Solver → lay out objective, variables, constraints → open Data > Solver → set objective and engine → Add constraints → Solve → review Answer report
- Confirm the Solver add-in is loaded (enable steps).
- Enter coefficients and
SUMPRODUCTformulas. - Unlock variable cells; keep limits in labeled cells.
- Open Data > Solver (Windows) or Data > Solver on Mac after enabling.
- Set Objective to your profit cell; choose Max, Min, or Value Of.
- Set By Changing Variable Cells to the quantity range.
- Click Add for each constraint; use Simplex LP for linear models.
- Click Solve, keep reports if prompted, and read variable results.
Warning: If every variable stays at zero, check that cells are not locked, not formatted as text, and included in the changing-cells reference.
Choose the solving method
Linear product-mix models use Simplex LP. Models with nonlinear functions—curves, products of variables, volatile IF branches—need GRG Nonlinear or Evolutionary. Our linear programming deep dive and optimization methods comparison explain when to switch engines.
Product highlight: Most classroom Solver failures are layout problems—wrong changing cells or a constraint that references constants—not mysterious optimization math.
Read the Answer report
After Solve, inspect final variable values and whether constraints bind. A binding warehouse constraint explains why profit stops rising when you try to force more volume. Ablebits' Solver examples include annotated reports beginners can mimic.
If Solver reports infeasibility, remove or relax one constraint at a time to locate the conflict. If it stops at a strange point on a linear model, verify you did not accidentally select GRG Nonlinear.
Reuse the pattern on new problems
Swap coefficients, keep the geometry: objective on top, variables in a column block, constraints in a table beneath. Save the model inside the workbook (Load/Save in the dialog) when you teach recurring classes.
When setup time exceeds solving time, a structured example file helps. The PlanoNest Excel Solver Example1 template is a one-time purchase with instant digital download—confirm features on the product page. Walk through a full numeric story in our product-mix Solver example.
Sanity checks before you share the file
Pick a feasible manual solution—say 10 units each—and verify constraints hold. Run Solver and confirm it meets or beats your manual objective. Change one profit coefficient and confirm the optimal mix shifts logically. Document the engine used so the next reader does not rerun with the wrong method.
Common Solver dialog mistakes
Objective not a formula. If the objective cell is a typed constant, Solver adjusts variables but the displayed objective never changes. The cell must be a formula that references decision variables.
Changing cells include labels. Including header text in the variable range can produce nonsense or failure. Reference numeric cells only.
Constraints point at wrong sheet. Cross-sheet references work, but only if you intend them. A constraint that still points at last month's tab silently optimizes the wrong scenario.
Max versus Min confusion. Cost minimization models need Min. Students often leave Max from a prior exercise and wonder why Solver pushes variables to zero.
Teaching notes for trainers
When you teach how to use solver in excel, assign students to screenshot three states: blank model, feasible manual trial, and optimal solution with one binding constraint highlighted. That sequence forces them to connect algebra to the dialog.
Pair this article with the linear programming guide when every formula is SUMPRODUCT. Pair with optimization methods when the story includes nonlinear efficiency curves.
If your cohort spends more time fixing layouts than interpreting shadow prices, point them to the Excel Solver example template hub and the PlanoNest Example1 workbook as a one-time purchase with instant digital download—live details on the product page.
Handoff note
Before handing off the sheet, replace example coefficients with clearly marked test values and ask another person to reproduce one objective total manually. That review catches mislabeled constraints, locked variable cells, and wrong changing-cell ranges faster than trusting the Solver status line alone.
Treat Solver reports as calculated outputs, not standalone evidence. Keep the workbook, engine choice, assumption date, and coefficient table together. If profits or capacities change later, create a new dated scenario instead of silently replacing numbers behind a report already shared with stakeholders.
Archive one solved workbook per month if you use Solver for planning. Future you will need to explain why March's mix differed from February's when commodity prices moved. A short changelog tab beats reconstructing decisions from email threads.
If students or colleagues fear Solver, remind them it only adjusts cells you permit. You remain in control of every formula and limit; Solver searches within the fence you built.
Before handing off the sheet, replace example coefficients with clearly marked test values and ask another person to reproduce one objective total manually. That review catches mislabeled constraints, locked variable cells, and wrong changing-cell ranges faster than trusting the Solver status line alone.
Treat Solver reports as calculated outputs, not standalone evidence. Keep the workbook, engine choice, assumption date, and coefficient table together. If profits or capacities change later, create a new dated scenario instead of silently replacing numbers behind a report already shared with stakeholders.
Archive one solved workbook per month if you use Solver for planning. Future you will need to explain why March's mix differed from February's when commodity prices moved. A short changelog tab beats reconstructing decisions from email threads.
Frequently asked questions
Where is Solver in Excel?
On Windows desktop Excel, open Data > Analyze > Solver after loading the add-in. Mac paths differ slightly; enable the add-in first.
What goes in the Set Objective box?
The single cell you want to maximize, minimize, or set to a target value—usually a formula referencing decision variables.
Which cells are decision variables?
Unlocked cells Solver may change, typically quantities, hours, or budget allocations with numeric bounds.
How do I add a constraint?
In the Solver dialog, choose Add, point to a formula cell on the left, pick <=, =, or >=, and enter a limit or cell reference on the right.
What engine should beginners use for linear models?
Choose Simplex LP when the objective and constraints are linear in the variables, as in classic product-mix examples.
About the author
PlanoNest Editorial prepares practical Excel and planning template guides for the PlanoNest blog.
Disclosure: PlanoNest sells digital Excel, Google Sheets, and Notion templates. Links to PlanoNest products and collections point to our own template shop.
