Kumu / Reference / Excel Solver
Excel Solver
Solver answers one question: what values of the cells I choose make this other cell as large (or small) as possible, without breaking these rules? Three things go in — an objective, the changing cells, and the constraints — and a set of numbers comes out.
The mechanics take ten minutes. The part worth reading twice is the last section: the algorithm you will be told to use can return a different answer depending on where it starts, which is why your audit has to check that it didn't.
First: check that you can run it at all
Excel for the web has no Solver
If you work in Excel in a browser tab, Solver is not there and cannot be added — there is no add-in to enable. This is the single most common way students lose an evening, and it is worth finding out in week one rather than the night before a deadline.
Three ways forward, in order of preference:
| Route | What to do |
|---|---|
| Desktop Excel — the intended path | Most universities license the full Microsoft 365 desktop apps to enrolled students at no cost. Sign in to your institution's software portal with your school email and install the desktop version; Solver ships with it and only needs enabling. |
| Google Sheets + Solver add-on | Sheets has an add-on marketplace with linear/nonlinear solvers. Workable, but the dialog and the option names differ from everything written below, and your model has to be rebuilt in Sheets. |
| OpenSolver | A free open-source add-in for desktop Excel. Its real use here is as a second opinion — if you have any doubt about a result, a second engine that agrees with the first is meaningful evidence. |
Whichever route you take, say so in your spec's assumptions. A grader who knows you used a different engine reads a small numerical difference as a tool difference rather than an error.
Turning it on
Solver ships with desktop Excel but is switched off until you ask for it. You do this once.
| Windows | Mac | |
|---|---|---|
| Where to start | File → Options → Add-ins | Tools → Excel Add-ins in the menu bar |
| Then | At the bottom, set Manage to Excel Add-ins and click Go… | The add-ins list opens directly — there is no Manage dropdown |
| Finally | Tick Solver Add-in → OK | Tick Solver → OK |
| Where it appears | Data tab, far right, in an Analyze group | Data tab, far right — the group may be unlabeled |
If the button does not appear after enabling, restart Excel. If it still does not, you are almost certainly in the web version — see above.
The Mac dialog is not the Windows dialog
It does the same three things, but the layout, the wording, and the placement of the options button differ enough that Windows-shaped instructions will strand you. Where this page names a control, it names what it does rather than exactly where it sits, so both versions can follow along. Expect to hunt slightly on a Mac; nothing is missing.
The three inputs
Everything Solver needs fits in one dialog, and the dialog is one idea per box.
| Box | What goes in it | The mistake to avoid |
|---|---|---|
| Set Objective | The single cell you want maximized, minimized, or driven to a value. In a profit model this is the total-profit cell. | Pointing it at an input, or at a range. It is one cell, and it must contain a formula that ultimately depends on the changing cells. If it is a typed number, Solver has nothing to work with. |
| By Changing Variable Cells | The cells Solver is allowed to rewrite — your decisions. In a planting model, the bed counts. | Including a cell that a formula already computes. Solver will overwrite the formula, and the model quietly stops being a model. |
| Subject to the Constraints | The rules, added one at a time:
beds_total <= 64, tomato_beds <= 20,
temp_workers <= 4. |
Leaving out the ones that feel obvious. "Obviously it can't be negative" is obvious to you and invisible to the solver — if a negative value would be nonsense, constrain it. |
Name your ranges before you open the dialog. beds_tomato in a constraint is
checkable by a reader; $D$14 is not, and your spec is supposed to be readable by
someone who has never opened your file.
Integer decisions
To force a whole number, add a constraint whose relation is int
rather than <=: pick the changing cells on the left, choose int, and
the right-hand box fills itself in. You do this whenever a fraction would be meaningless —
you cannot plant 14.7 beds or hire 3.2 people.
It has a cost worth knowing: integer constraints make the problem discrete, so the solver can no longer nudge a value slightly and see what happens. It searches instead, which is slower and — relevant below — makes the starting point matter more, not less.
What GRG Nonlinear actually is
The method dropdown offers three engines. You will usually be told to pick GRG Nonlinear, and it is worth thirty seconds to know why, because the reason explains a check your audit is asked to run.
| Method | Use it when |
|---|---|
| Simplex LP | Every relationship is a straight line — profit per unit fixed, costs proportional. Fast, and it finds the true best answer. If your model has an exponent or a product of two decisions anywhere, this is the wrong engine and Excel will say so. |
| GRG Nonlinear | Smooth curves — diminishing returns, compounding, anything with a power or a rate. The default for the models in these courses. |
| Evolutionary | Jagged, discontinuous models — IF
chains, lookups, hard cutoffs. Slow, and it stops when it stops improving rather than when it is
finished. A fallback, not a default. |
GRG stands for Generalized Reduced Gradient, and "gradient" is the whole idea. From wherever the changing cells currently sit, the engine looks at the local slope of the objective, takes a step in the steepest uphill direction, and repeats until no direction goes up any further. Then it stops and reports that point.
Which is exactly the problem. It walks uphill from where it starts, and it stops at the first summit it reaches. If the objective has more than one peak, the summit it finds is the one above wherever you happened to leave the changing cells — a local optimum, not necessarily the best one. Solver reports it with the same confidence either way. It does not say "this is one of several"; it says "Solver found a solution."
This is why you run it twice from different starting points
Set the changing cells to one starting guess, run Solver, write down the answer. Set them to a very different guess — all zeros, or everything at its cap — and run it again. If both runs land on the same numbers, you have real evidence the answer is not an artifact of where you started. If they disagree, you have found something: the model has multiple peaks, the better answer is the one you now know about, and the disagreement itself is the finding your audit note should report.
That check is not busywork and it is not a formality. It is the only cheap test that distinguishes "the optimum" from "an optimum," and skipping it is how a confident wrong number reaches a memo.
Two habits that cost nothing and prevent most of the rest:
- Start from a sensible guess, not from zero. Zeros are a legitimate second run, but as a first run they leave the engine on a flat plain with no slope to follow.
- Read the message, not just the numbers. "Solver found a solution. All constraints and optimality conditions are satisfied" is the good one. "Solver converged to the current solution" means it stopped improving, which is weaker. "Solver could not find a feasible solution" means your constraints contradict each other — that is a modeling result, not a failure, and it usually means two rules cannot both hold.
Before you trust the answer
Solver output is a draft, exactly like AI output. Four checks, in the order that catches the most for the least effort:
- Does every constraint actually hold? Look at the cells, not at the dialog. A constraint typed against the wrong range is silently satisfied.
- Did the two starting points agree? See above. This is the one that catches the error nothing else catches.
- Do the changing cells still contain values rather than formulas? If Solver overwrote a formula, the model's logic has a hole in it now.
- Does the answer make economic sense? If the model plants half of what you expected of the most profitable crop, that is either a real finding worth explaining or a defect worth locating. Both are worth your attention; neither is worth accepting silently.
Record what you checked and what you found. "Ran Solver, got 10/20/30" is a result. "Ran from two starting points, both returned 10/20/30, all constraints verified at the cells" is evidence.
Related
- Farm Profit Lab — the same optimization with sliders, and an independent optimizer to check a workbook against
- Deliverable templates → the spec — where the objective, the changing cells, the constraints, and the method belong in writing, before the workbook exists
- AI Tools Lab — handing that spec to an AI tool to build from