Excel Solver: Optimize Business Decisions Without Guesswork

Most spreadsheet decisions still get made by trial and error: change a number, see what happens, change it again. That works fine for simple questions, but it falls apart the moment you have multiple variables and real constraints — how many staff to schedule per shift without exceeding budget, how to allocate ad spend across channels to maximize leads, or how to route shipments at the lowest total cost while meeting delivery deadlines. Excel Solver exists exactly for this class of problem, and most professionals who have Excel installed have never turned it on.

Solver is a built-in optimization engine that finds the best combination of inputs to maximize or minimize an outcome, subject to constraints you define. It ships free with Excel, requires no VBA, and can replace hours of manual "what if" guessing with a single calculation.

The Problem With Manual What-If Analysis

Goal Seek and manual scenario testing work well when you are adjusting one variable to hit one target. But real business decisions rarely involve just one variable. A staffing plan has a headcount limit, a budget ceiling, minimum coverage requirements per shift, and overtime rules — all interacting at once. Adjusting one cell to satisfy one constraint often breaks another, and manually balancing five or six interacting variables by hand is slow and error-prone.

This is the gap Solver closes. Instead of manually iterating toward a workable answer, you describe the objective (maximize profit, minimize cost, hit a specific target), define the variables Solver is allowed to change, and set the constraints those variables must respect. Solver then runs an optimization algorithm and returns the best feasible combination it can find.

Understanding How Solver Works

Solver in Excel is built around three components, and understanding each one is the key to using it correctly.

The Objective Cell

This is the single cell containing the formula you want to maximize, minimize, or set to a specific value. It might be total profit, total cost, or total production output. Solver needs exactly one objective cell, and that cell's value must depend, directly or indirectly, on the variable cells you're optimizing.

Variable Cells (Decision Variables)

These are the cells Solver is allowed to change to reach the objective. In a staffing model, this might be the number of employees scheduled per shift. In a marketing budget model, it might be the dollar amount allocated to each channel. Solver searches across combinations of these cells to find the optimal outcome.

Constraints

Constraints are the rules the solution must respect: total budget cannot exceed $50,000, staff per shift must be at least 3, production cannot exceed factory capacity. Without constraints, Solver would simply push variables to unrealistic extremes (infinite ad spend, zero staff). Constraints keep the answer grounded in what is actually feasible.

Enabling and Setting Up Solver

Solver ships with Excel but is not visible by default. To enable it, go to File > Options > Add-ins, select Excel Add-ins from the Manage dropdown, click Go, and check the box next to Solver Add-in. Once enabled, you will find it on the Data tab, usually in the Analysis group on the far right.

Step 1: Build Your Model First

Before opening Solver, build a normal spreadsheet model with clearly labeled cells for your inputs, formulas connecting those inputs to outputs, and a clear total or result cell. Solver works on top of your existing formulas — it does not replace good spreadsheet design.

Step 2: Open Solver and Set the Objective

Click Solver on the Data tab. In the Set Objective field, select your total profit, cost, or output cell. Choose whether you want to Max, Min, or set it to a specific Value Of target.

Step 3: Define Variable Cells

In By Changing Variable Cells, select the range of cells representing your decision variables — the inputs Solver is allowed to adjust, such as units produced per product line or budget per channel.

Step 4: Add Constraints

Click Add to define each constraint. A constraint has three parts: a cell reference, a comparison operator (<=, >=, =), and a value or another cell reference. For a staffing model, you might add constraints like total hours <= budget hours, staff per shift >= minimum coverage, and staff per shift <= available headcount.

Step 5: Choose a Solving Method

Excel offers three solving engines: Simplex LP for linear problems (most budget and resource allocation models), GRG Nonlinear for problems involving non-linear relationships like diminishing returns, and Evolutionary for problems with discontinuous or highly non-linear formulas. Start with Simplex LP if your relationships are proportional; switch to GRG Nonlinear if Solver reports the model is not linear.

Real-World Example: Optimizing Staff Scheduling

Imagine a call center that needs to schedule agents across four shifts to meet minimum coverage requirements while staying under a weekly labor budget. The spreadsheet has columns for shift, hourly wage, minimum required agents, and a variable cell for agents scheduled per shift.

The objective cell calculates total labor cost (agents scheduled multiplied by hourly wage, summed across shifts), set to Min. The variable cells are the agents-scheduled column. Constraints include: agents scheduled per shift >= minimum required agents, total agents scheduled <= total available staff, and agents scheduled must be whole numbers (add an "int" constraint for integer values, since you cannot schedule half an employee).

Running Solver returns the lowest-cost staffing combination that still meets every shift's minimum coverage requirement — a calculation that would take considerable manual trial and error to approximate by hand, and one Solver produces in seconds.

A Second Example: Marketing Budget Allocation

A marketing team wants to allocate a fixed $20,000 monthly budget across four channels — paid search, social ads, email, and content — to maximize projected leads, where each channel has a different cost-per-lead and a diminishing-returns curve as spend increases. The objective cell sums projected leads across channels, set to Max. Variable cells are the budget allocated per channel. Constraints include total spend <= $20,000 and a minimum spend floor per channel to maintain brand presence.

Because diminishing returns make this a non-linear problem, you would switch the solving method to GRG Nonlinear, since Simplex LP assumes straight-line relationships between input and output.

Best Practices / Pro Tips

Always sanity-check Solver's answer against business intuition. Optimization models are only as good as the constraints you define, and a missing constraint can produce a technically optimal but practically absurd result, like allocating your entire budget to one channel because you forgot a spend-floor constraint.

Save different constraint sets as named Scenarios using Solver's "Save Scenario" option so you can compare outcomes under different assumptions, such as a tighter budget or a higher minimum staffing requirement, without rebuilding the model each time.

Watch for the "Solver could not find a feasible solution" message. This usually means your constraints conflict with each other — for example, requiring more minimum staff than your total headcount allows. Loosen or review constraints one at a time to find the conflict.

For recurring optimization tasks, record a macro that opens Solver with pre-set parameters, or use Solver's VBA object model to trigger optimization automatically when source data changes.

Conclusion

Excel Solver turns spreadsheets from static calculators into genuine decision-support tools. Instead of manually testing combinations and hoping you land on something reasonable, you can define the objective, the levers you control, and the real-world limits, then let Excel's optimization engine find the best feasible answer. Whether you are scheduling staff, allocating budget, or planning shipments, Solver replaces guesswork with a defensible, repeatable process — and it is already sitting inside the Excel you use every day.

Frequently Asked Questions

Do I need to know linear programming to use Excel Solver?

No. Solver handles the underlying math automatically. You just need to correctly identify your objective, variable cells, and constraints — the modeling skill matters more than the mathematical theory behind it.

What is the difference between Solver and Goal Seek?

Goal Seek adjusts one input to hit one target value. Solver can adjust multiple variables simultaneously, respect multiple constraints, and maximize or minimize an objective rather than just matching a single target — making it suitable for far more complex decisions.

Why does Solver say my model has no feasible solution?

This usually means your constraints contradict each other, such as requiring more resources than are actually available. Review each constraint individually, starting with the tightest ones, to find where the conflict lies.

Can Solver results update automatically when my data changes?

Not by default — you need to re-run Solver manually after changing inputs, unless you automate it with a macro that re-triggers Solver's calculation whenever source cells change.

---

Related articles: Excel Goal Seek and What-If Analysis: The Complete Guide, Excel Power Query: Merge and Combine Multiple Files Automatically, Build a Dynamic Excel Dashboard Without VBA: 5 Advanced Techniques