How to Use Solver in Excel for Optimization
Excel Solver is one of the most underutilized tools in the Microsoft Office suite. While most users stick to basic formulas and pivot tables, Solver quietly waits in the background, ready to tackle...
Key Insights
- Excel Solver transforms spreadsheets into powerful optimization engines capable of solving complex resource allocation, scheduling, and portfolio problems that would otherwise require specialized software
- The three core components—objective cell, variable cells, and constraints—form the foundation of every optimization problem, and understanding their relationships is essential for correct model formulation
- Choosing the right solving method (Simplex LP, GRG Nonlinear, or Evolutionary) determines whether you find the true optimal solution or get stuck at a local maximum
Introduction to Excel Solver
Excel Solver is one of the most underutilized tools in the Microsoft Office suite. While most users stick to basic formulas and pivot tables, Solver quietly waits in the background, ready to tackle optimization problems that would otherwise require expensive specialized software or custom programming.
At its core, Solver finds the best value for a formula in one cell—called the objective—by changing values in other cells while respecting constraints you define. This simple concept powers solutions to remarkably complex problems: maximizing profit given limited resources, minimizing shipping costs across distribution networks, balancing investment portfolios, scheduling employees to meet demand, and allocating advertising budgets across channels.
Solver ships with every copy of Excel but isn’t enabled by default. Before diving into optimization problems, you need to activate it.
Enabling and Accessing Solver
Solver is an add-in that requires manual activation. Here’s how to enable it:
- Open Excel and click File > Options
- Select Add-ins from the left sidebar
- At the bottom, ensure “Excel Add-ins” is selected in the Manage dropdown
- Click Go…
- Check the box next to Solver Add-in
- Click OK
After activation, you’ll find Solver in the Data tab on the far right of the ribbon under the Analysis group. If you don’t see it immediately, restart Excel.
Data Tab Location:
[Data] → [Analysis Group] → [Solver]
The Solver button opens the Solver Parameters dialog, which is your command center for defining and solving optimization problems.
Understanding Solver Components
Every Solver problem consists of three fundamental components. Misunderstanding any of these leads to incorrect solutions or errors.
Objective Cell (Set Objective): This is the cell containing the formula you want to optimize. It must contain a formula that depends—directly or indirectly—on your variable cells. You can maximize it, minimize it, or set it to a specific value.
Variable Cells (By Changing Variable Cells): These are the cells Solver adjusts to find the optimal solution. They should contain values (not formulas) that influence your objective cell. Think of these as the “knobs” Solver turns.
Constraints (Subject to the Constraints): These are the rules and limitations your solution must respect. Constraints reference cells and define relationships using operators like ≤, ≥, =, or integer requirements.
Here’s a simple spreadsheet structure for a profit maximization scenario:
| | A | B | C | D |
|---|----------------|----------|-------------|--------------|
| 1 | Product | Quantity | Profit/Unit | Total Profit |
| 2 | Widget A | 0 | $15 | =B2*C2 |
| 3 | Widget B | 0 | $22 | =B3*C3 |
| 4 | Widget C | 0 | $18 | =B4*C4 |
| 5 | | | | |
| 6 | Total Profit | | | =SUM(D2:D4) |
| 7 | | | | |
| 8 | Resource | Used | Available | |
| 9 | Labor Hours | (formula)| 100 | |
| 10| Raw Material | (formula)| 200 | |
In this setup:
- Objective Cell: D6 (Total Profit)
- Variable Cells: B2:B4 (Quantities to produce)
- Constraints: Labor and material usage ≤ available amounts
Setting Up Your First Optimization Problem
Let’s work through a complete linear programming example. A manufacturer produces three products using two resources: labor hours and raw materials.
| | A | B | C | D | E |
|---|----------------|----------|-------------|--------------|----------------|
| 1 | PRODUCT MIX OPTIMIZATION |
| 2 | | | | | |
| 3 | Product | Quantity | Profit/Unit | Total Profit | Labor Required |
| 4 | Standard | 0 | $25 | =B4*C4 | 2 |
| 5 | Premium | 0 | $40 | =B5*C5 | 3 |
| 6 | Deluxe | 0 | $60 | =B6*C6 | 5 |
| 7 | | | | | |
| 8 | Total Profit | | | =SUM(D4:D6) | |
| 9 | | | | | |
| 10| CONSTRAINTS | | | | |
| 11| Resource | Used | Limit | | |
| 12| Labor Hours | =SUMPRODUCT(B4:B6,E4:E6) | 120 | | |
| 13| Material (lbs) | =SUMPRODUCT(B4:B6,F4:F6) | 200 | | |
| 14| Min Standard | =B4 | 10 | | |
Add material requirements in column F:
| F |
|------------------|
| Material Required|
| 3 |
| 4 |
| 6 |
The key formulas use SUMPRODUCT() to calculate resource consumption:
Labor Used (B12): =SUMPRODUCT(B4:B6,E4:E6)
Material Used (B13): =SUMPRODUCT(B4:B6,F4:F6)
Total Profit (D8): =SUM(D4:D6)
Now configure Solver:
- Set Objective: $D$8
- To: Max
- By Changing Variable Cells: $B$4:$B$6
- Subject to Constraints:
- $B$12 <= $C$12 (labor constraint)
- $B$13 <= $C$13 (material constraint)
- $B$4 >= $C$14 (minimum Standard units)
- $B$4:$B$6 >= 0 (non-negativity)
- $B$4:$B$6 = integer (whole units only)
Click Solve and Solver finds the optimal product mix.
Choosing the Right Solving Method
Solver offers three solving methods, and selecting the wrong one can mean the difference between finding the true optimum and getting stuck.
Simplex LP: Use this for linear problems where both the objective function and all constraints are linear (no exponents, no products of variables). It’s fast, reliable, and guarantees finding the global optimum if one exists. The product mix problem above is linear—profit is a linear function of quantities, and resource usage is linear.
GRG Nonlinear: Use this for smooth nonlinear problems—those involving exponents, logarithms, or products of decision variables. GRG finds local optima, which may not be the global best. Running from multiple starting points helps verify results.
Linear (use Simplex LP):
Profit = 25*X1 + 40*X2 + 60*X3
Nonlinear (use GRG):
Profit = 25*X1 + 40*X2 - 0.5*X2^2
Evolutionary: Use this for non-smooth problems with discontinuities, IF statements, or VLOOKUP functions in the objective or constraints. It uses genetic algorithms and is slower but handles problems the other methods cannot. However, it provides no guarantee of finding the global optimum.
A practical guideline: start with Simplex LP if your problem looks linear. If Solver reports that the problem isn’t linear, switch to GRG Nonlinear. Reserve Evolutionary for problems with lookup functions or other discontinuities.
Interpreting Results and Sensitivity Reports
After solving, Solver offers three reports: Answer, Sensitivity, and Limits. The Sensitivity Report is particularly valuable for understanding your solution.
SENSITIVITY REPORT EXCERPT
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$4 Standard 10 0 25 5 1E+30
$B$5 Premium 30 0 40 15 8
$B$6 Deluxe 0 -5 60 5 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$B$12 Labor 120 12.5 120 40 20
$B$13 Material 180 0 200 1E+30 20
Key interpretations:
-
Reduced Cost: For variables at zero, this shows how much the objective coefficient must improve before that variable enters the solution. Deluxe has a reduced cost of -5, meaning its profit must increase by $5 (to $65) before production becomes worthwhile.
-
Shadow Price: The value of one additional unit of a constraint’s right-hand side. Labor’s shadow price of 12.5 means each additional labor hour adds $12.50 to profit. Material’s shadow price of 0 indicates slack—we’re not using all available material.
-
Allowable Increase/Decrease: The range over which shadow prices and reduced costs remain valid. Labor’s shadow price holds for increases up to 40 hours.
Troubleshooting Common Issues
“Solver could not find a feasible solution”: Your constraints are contradictory or impossible to satisfy simultaneously. Check for typos in constraint references. Relax constraints one at a time to identify the conflict. Ensure constraint directions (≤ vs ≥) are correct.
“The Objective Cell values do not converge”: The problem is unbounded—there’s no limit to how good the solution can get. You’re probably missing a constraint. Check that all relevant limitations are included.
“Solver found a solution, but it seems wrong”: For nonlinear problems, you may have found a local optimum. Try different starting values in your variable cells and re-solve. If you consistently get the same answer, it’s likely the global optimum.
Integer constraints cause failures: Integer programming is computationally harder. Try solving without integer constraints first to verify the model is correct. Increase the Max Time and Iterations in Solver Options. Consider whether integer constraints are truly necessary.
Circular reference errors: Your objective or constraint formulas reference variable cells in a circular way. Restructure your model so variable cells contain only values, not formulas.
Solver transforms Excel from a calculation tool into an optimization platform. Master these fundamentals, and you’ll solve problems that previously required specialized software or were simply avoided altogether.