How to Use Solver in Excel

Introduction

You have a workshop that builds desks and chairs. Each product uses assembly time and finishing time, and both resources are limited. You want the production mix that maximizes profit, but adjusting one variable at a time won't find the best combination. Solver adjusts multiple variables at once while respecting every constraint you define.

The Spreadsheet

Here's a production model with two products and two resource limits:

	A	B	C
2		Desks	Chairs
3	Profit per Unit	200	150
4	Assembly (hrs)	3	1
5	Finishing (hrs)	1	2
6	Units to Make	0	0
7	Total Profit	=B3B6+C3C6
8	Assembly Used	=B4B6+C4C6
9	Assembly Limit	30
10	Finishing Used	=B5B6+C5C6
11	Finishing Limit	20

Desks earn $200 each and need 3 assembly hours and 1 finishing hour. Chairs earn $150 each and need 1 assembly hour and 2 finishing hours. The workshop has 30 assembly hours and 20 finishing hours available per week.

With both unit counts at 0, Total Profit (B7) shows $0. The formulas in B8 and B10 track how many hours each department uses, and B9 and B11 hold the limits. Solver will find the unit counts in B6 and C6 that maximize B7 without exceeding the available hours.

Running Solver

Solver isn't on the ribbon by default. To enable it:

1. Go to File > Options > Add-ins.
2. Set the Manage dropdown to Excel Add-ins and click Go.
3. Check Solver Add-in and click OK.

Solver now appears under Data > Solver. You only need to do this once.

To solve the production problem:

1. Go to Data > Solver.
2. Set Objective: B7 (Total Profit). Select Max.
3. By Changing Variable Cells: B6:C6.
4. Click Add and enter the constraint B8 <= B9 (Assembly Used ≤ Assembly Limit).
5. Click Add and enter B10 <= B11 (Finishing Used ≤ Finishing Limit).
6. Check Make Unconstrained Variables Non-Negative and set Solving Method to Simplex LP.
7. Click Solve.

Solver reports that it found a solution. Select Keep Solver Solution and click OK.

B6 now shows 8 and C6 shows 6. Total Profit jumps to $2,500.

Check the resources: Assembly = 3(8) + 1(6) = 30 hours, exactly at the limit. Finishing = 1(8) + 2(6) = 20 hours, also at the limit. Solver used every available hour in both departments to reach the maximum profit.

Key takeaway: Solver needs four things: an objective cell to maximize or minimize, the cells it can change, constraints those cells must satisfy, and a solving method. It finds the best combination systematically, not by trial and error.

Adding a Constraint

A customer places a standing order for at least 8 chairs per week. Open Data > Solver again. The previous settings are still loaded.

Click Add and enter a new constraint: C6 >= 8. Click Solve.

The answer shifts to 4 desks and 8 chairs. Total Profit drops to $2,000.

Check: Assembly = 3(4) + 1(8) = 20 hours, well under the 30-hour limit. Finishing = 1(4) + 2(8) = 20, right at the limit. The assembly department now has slack because the chair requirement pushes the finishing department to capacity first.

The customer order costs $500 in weekly profit. That's the trade-off: the workshop gives up desks to guarantee the chairs, and Solver shows you exactly what the constraint costs.

Solver vs Goal Seek

Solver and Goal Seek both find input values that satisfy a condition, but they solve different types of problems:

	Solver	Goal Seek
Variables	Multiple cells at once	One cell
Direction	Maximize, minimize, or hit a target	Hit a specific target
Constraints	As many as you need	None
Best for	"What mix maximizes profit given these limits?"	"What price gives me exactly $3,000?"

Goal Seek is faster for single-variable questions. Solver handles everything Goal Seek can, plus problems where multiple inputs interact and constraints apply.

Things to Know

• Solving method. Choose Simplex LP for problems where all formulas are linear (additions and multiplications by constants, like the production model above). Choose GRG Nonlinear when formulas involve multiplication between changing cells, exponents, or other nonlinear operations.
• Sensitivity report. After Solver finishes, it offers to create reports. The Sensitivity Report shows the shadow price of each constraint, which tells you how much the objective would improve if you relaxed a constraint by one unit. In the production model, it would show how much profit increases per additional assembly or finishing hour.
• Reset between problems. Solver remembers the last setup. If you're starting a different problem, click Reset All in the Solver dialog to clear the previous objective, variables, and constraints.

Conclusion

Solver finds the best combination of inputs that satisfies a set of constraints, handling optimization problems where Goal Seek's single-variable approach falls short. It covers production planning, resource allocation, and any scenario where you need to maximize or minimize one number while staying within limits.

For single-variable problems where you already know the target, see our guide on Goal Seek. For testing how a formula's output changes across a range of inputs, check out our guide on data tables.