ExcelHow-ToBeginner · 3 min read

How to Use Solver in Excel: Step-by-Step Guide

To use Solver in Excel, first enable the Solver add-in from Excel Options. Then set your objective cell, decision variable cells, and constraints in the Solver Parameters window, and click Solve to find the best solution.

📐

Syntax

The Solver tool in Excel does not use a formula syntax but works through a dialog interface where you define:

Set Objective: The cell you want to optimize (maximize, minimize, or reach a value).
By Changing Variable Cells: The cells Solver can change to reach the objective.
Subject to the Constraints: Conditions that limit how variable cells can change.

After setting these, you click Solve to run Solver.

excel

No formula code; use Excel's Solver dialog interface.

💻

Example

This example shows how to maximize profit by changing the number of products made, with constraints on resources.

Suppose you have:

Cell B1: Number of Product A (variable cell)
Cell B2: Number of Product B (variable cell)
Cell B3: Total Profit = B1*50 + B2*40 (objective cell)
Constraints: B1 + B2 ≤ 100 (resource limit), B1 ≥ 0, B2 ≥ 0

Use Solver to find the best number of products to maximize profit.

text

Step 1: Enter data in Excel cells:
B1: 0
B2: 0
B3: =B1*50 + B2*40

Step 2: Open Solver:
- Go to Data tab > Solver

Step 3: Set parameters:
- Set Objective: $B$3
- To: Max
- By Changing Variable Cells: $B$1,$B$2
- Add Constraints:
  $B$1 + $B$2 <= 100
  $B$1 >= 0
  $B$2 >= 0

Step 4: Click Solve and accept the solution.

Output

Solver finds B1 = 100, B2 = 0, maximizing profit to 5000.

⚠️

Common Pitfalls

Not enabling the Solver add-in before use.
Setting incorrect or missing constraints, causing Solver to fail or give unrealistic results.
Using non-numeric or empty cells as variable cells.
Forgetting to set the objective cell correctly.
Not choosing the right solving method (Simplex LP, GRG Nonlinear, or Evolutionary) for the problem type.

text

Wrong: Forgetting to add constraints
- This can cause Solver to suggest impossible solutions.

Right: Always add constraints that reflect real limits
- Example: $B$1 + $B$2 <= 100

📊

Quick Reference

Term	Description
Set Objective	Cell to maximize, minimize, or set to a value
Variable Cells	Cells Solver changes to optimize the objective
Constraints	Limits on variable cells or other cells
Solve	Button to run Solver and find solution
Solver Add-in	Must be enabled via Excel Options > Add-ins

✅

Key Takeaways

Enable the Solver add-in before using it in Excel.

Set a clear objective cell and specify which cells Solver can change.

Always add realistic constraints to guide Solver's solution.

Choose the correct solving method based on your problem type.

Use Solver's dialog interface; it does not use formulas.