Linear Programming in Excel Solver: Step-by-Step Guide

Last updated: • Sources • Methodology

Linear Programming in Excel Solver: Step-by-Step Setup (Decision Cells, Objective, Constraints, Reports)

This guide shows how to model and solve a linear program in Excel Solver using Simplex LP: how to lay out the sheet, connect formulas, add constraints correctly, and interpret Answer & Sensitivity reports.

If you want to double-check results outside Excel, solve the same model in our Linear Programming calculator. If you’re new to LP concepts, start with Linear Programming (LP) — Beginner Guide.

On this page

Quick takeaway: For linear programming in Excel Solver, your success rate jumps if you follow one rule: every constraint LHS must be a formula that references the decision cells. Then set the objective cell, decision (changing) cells, and constraints, choose Simplex LP, and generate reports. If Solver returns infeasible or unbounded, don’t “tune options” first—use Unbounded vs Infeasible LP (Fix Checklist) and add realistic upper bounds.

Step 0: Enable Solver add-in

Setup

How to add Solver in Excel

  1. Go to File → Options → Add-ins.
  2. At the bottom, choose Excel Add-ins and click Go.
  3. Check Solver Add-in and click OK.

After this, you should see Data → Solver on the ribbon.

Step 1: Build the worksheet model (layout pattern)

Model layout

A reliable spreadsheet pattern for LPs

Use a consistent structure:

  • Decision cells (variables) in one block (e.g., B2:B3).
  • Objective cell as a formula referencing decisions (e.g., =SUMPRODUCT(profits, decisions)).
  • Constraint LHS cells as formulas referencing decisions (e.g., =SUMPRODUCT(Arow, decisions)).
  • Constraint RHS cells as constants (capacities, limits).
Common beginner mistake: typing the constraint “expression” as text into Solver instead of linking to LHS cells. In Solver, you constrain cells (not expressions).

Step 2: Solver Parameters (objective, changing cells, constraints)

Solver screen

What each field means (and what to enter)

Solver field What it is What you should select
Set Objective The objective value cell A single cell formula (profit/cost), e.g., $B$5
To: Max / Min / Value Of Optimization direction Choose Max or Min depending on your model
By Changing Variable Cells Decision variables The variable cell range, e.g., $B$2:$B$3
Subject to the Constraints All constraints Each constraint as LHS_cell ≤/=/≥ RHS_cell (or constant)

Step 3: Choose “Simplex LP” + key options

Method

Use Simplex LP for linear models

In Solver, set Select a Solving Method to Simplex LP for linear programs. Then consider these options:

  • Make Unconstrained Variables Non-Negative if your model assumes x ≥ 0.
  • Assume Linear Model (if available) can improve speed/consistency for true LPs.
If you add integer constraints (binary/integer), you now have an ILP/MILP rather than a pure LP. For the solver logic behind that, see Branch and Bound in Integer Programming and try our ILP/MILP calculator.

Step 4: Read Answer & Sensitivity reports

Interpretation

Which report to use for what

Report What it tells you When it’s most useful
Answer Report Final variable values, objective value, constraint status (binding or not) Basic validation and documentation of the solution
Sensitivity Report Reduced costs, shadow prices, allowable increases/decreases (ranges) Understanding tradeoffs; interpreting “prices” of constraints; diagnosing alternate optima

Shadow prices and reduced costs come from the dual interpretation of LP. For the theory (in plain English), see Primal–Dual Relationship in LP.

Questions people ask about linear programming in Excel Solver (PAA)

People ask

How do you set up linear programming in Excel Solver?

Put decision variables in cells, write the objective as a formula referencing those cells, compute each constraint LHS in its own cell, then in Solver set the objective cell, changing cells, and constraints. Choose Simplex LP to solve.

People ask

Which Solver method should I use for linear programming in Excel Solver?

Use Simplex LP for true linear programs. Use GRG Nonlinear or Evolutionary only if your formulas are nonlinear. If you mistakenly solve an LP with a nonlinear method, results can be slower and harder to interpret.

People ask

Why does Excel Solver say “Solver could not find a feasible solution”?

That usually means your constraints contradict each other, your inequality directions are wrong (≤ vs ≥), or you forgot nonnegativity/bounds. Use Unbounded vs Infeasible LP (Fix Checklist).

People ask

What does “unbounded” mean in Excel Solver?

It means the model has feasible solutions but the objective can improve forever—often because a variable is missing an upper bound or a limiting constraint. Start with Upper Bounds in Linear Programming.

People ask

How do I get the Sensitivity Report in Excel Solver?

After Solver finds an optimal solution using Simplex LP, Excel prompts you to generate reports. Choose Sensitivity (and optionally Answer). If Sensitivity is disabled, your model may not be a linear program or you didn’t use Simplex LP.

People ask

Why do I get different “optimal solutions” in Excel Solver?

Your LP may have multiple optimal solutions, in which case Solver can return any one of them. Use a tie-break objective (secondary objective) or add a preference constraint to select the solution you want.

Worked examples (fully solved)

Worked examples

Expand only the example you need

Each example includes (1) the LP model, (2) a suggested Excel sheet layout, (3) Solver inputs, and (4) the solved optimum.

Example 1 — Product mix linear programming in Excel Solver (maximize profit)

LP (maximize):

maximize   z = 3x1 + 5x2
subject to 2x1 + x2 ≤ 100
           x1 + 3x2 ≤ 90
           x1, x2 ≥ 0

Step 1: Suggested Excel layout

Cell(s) Meaning Formula / value
B2:B3 Decision variables B2=x1, B3=x2 (start with 0)
B5 Objective value (profit) =3*B2+5*B3
B7 Constraint 1 LHS =2*B2+1*B3
D7 Constraint 1 RHS 100
B8 Constraint 2 LHS =1*B2+3*B3
D8 Constraint 2 RHS 90

Step 2: Solver Parameters

  • Set Objective: $B$5
  • To: Max
  • By Changing Variable Cells: $B$2:$B$3
  • Constraints: $B$7 <= $D$7, $B$8 <= $D$8, and (optional) $B$2:$B$3 >= 0
  • Solving Method: Simplex LP

Step 3: Solution (shown here so you can verify)

Optimal corner comes from intersection of binding constraints:

2x1 + x2 = 100
x1 + 3x2 = 90
⇒ x2 = 16, x1 = 42

Objective:

z* = 3(42) + 5(16) = 126 + 80 = 206

Optimal solution (Example 1): x1 = 42, x2 = 16, z* = 206.

Example 2 — Cost minimization linear programming in Excel Solver (minimize)

LP (minimize):

minimize   z = 3x1 + 2x2
subject to 2x1 + x2 ≥ 8
           x1 + 2x2 ≥ 10
           x1, x2 ≥ 0

Step 1: Suggested Excel layout

Use the same pattern as Example 1: decision cells, objective cell, LHS cells, RHS constants.

Cell(s) Meaning Formula / value
B2:B3 Decision variables B2=x1, B3=x2
B5 Objective (cost) =3*B2+2*B3
B7 Constraint 1 LHS =2*B2+B3
D7 Constraint 1 RHS 8
B8 Constraint 2 LHS =B2+2*B3
D8 Constraint 2 RHS 10

Step 2: Solver Parameters

  • Set Objective: $B$5
  • To: Min
  • By Changing Variable Cells: $B$2:$B$3
  • Constraints: $B$7 >= $D$7, $B$8 >= $D$8, and nonnegativity
  • Solving Method: Simplex LP

Step 3: Solution (compute the optimal corner)

Intersection of binding constraints:

2x1 + x2 = 8
x1 + 2x2 = 10
⇒ x2 = 4, x1 = 2

Objective:

z* = 3(2) + 2(4) = 6 + 8 = 14

Optimal solution (Example 2): x1 = 2, x2 = 4, z* = 14.

This example uses ≥ constraints. If you’re learning simplex starts by hand for ≥ or = constraints, see Two Phase Simplex Method and Slack / Surplus / Artificial Variables.
Example 3 — Multiple optimal solutions in Excel Solver + Sensitivity clue

LP (maximize):

maximize   z = x1 + x2
subject to x1 + x2 ≤ 4
           x1 ≤ 3
           x2 ≤ 3
           x1, x2 ≥ 0

Step 1: What Solver may show

Excel Solver will return one optimal solution (often a corner), e.g., (x1,x2)=(3,1) or (1,3), both with z=4. But there are infinitely many optimal solutions along the segment x1+x2=4 with 1 ≤ x1 ≤ 3.

Step 2: Describe the full set of optimal solutions

(x1, x2) = (t, 4 − t)  for 1 ≤ t ≤ 3
z* = 4

Step 3: Sensitivity report clue (zero reduced cost)

In simplex terms, multiple optimal solutions occur when an optimal tableau has a zero reduced cost for a nonbasic variable. Excel’s Sensitivity Report is where you usually see reduced costs and shadow prices.

Exact report formatting varies by Excel version, but the rule is consistent: a zero reduced cost for a variable that Solver set to 0 can indicate alternate optima. For a dedicated explanation, see Multiple Optimal Solutions in LP.

Conclusion (Example 3): z*=4 with infinitely many optimal solutions. Excel may show one optimum; use a tie-break objective if you need a specific solution.

Debugging: infeasible/unbounded and wrong answers

Troubleshooting

What to do when Solver output doesn’t match your expectation

Symptom Likely cause Fix
“Solver could not find a feasible solution” Contradictory constraints, wrong inequality direction, missing flexibility Use Unbounded vs Infeasible LP (Fix Checklist); validate signs/units
“Unbounded solution” Missing upper bounds or missing limiting constraint Add realistic bounds; see Upper Bounds in LP
Sensitivity report is unavailable/greyed out Not solved with Simplex LP or model is nonlinear Switch solving method to Simplex LP; confirm formulas are linear
Solution violates a constraint you “think” you added Constraint LHS not linked to decision cells (wrong cell referenced) Ensure each LHS is a formula referencing decision cells; re-add the constraint
Different optimal solutions appear on different runs Multiple optimal solutions or degeneracy See Multiple Optimal Solutions in LP; add tie-break objective
You added integer/binary constraints and Solver is slow It’s now a MILP/ILP, not an LP Use ILP/MILP calculator and learn branch-and-bound
If you are converting constraints for simplex theory (slack/surplus/artificial), see Slack / Surplus / Artificial Variables.

Glossary

Glossary

  • Decision cells: spreadsheet cells representing the LP decision variables.
  • Objective cell: a cell formula computing profit/cost from decision cells.
  • Constraint LHS cell: a cell formula computing the left-hand side of a constraint.
  • Simplex LP: Solver method intended for linear programs.
  • Shadow price: dual value of a constraint (marginal value of relaxing the RHS, locally).
  • Reduced cost: simplex quantity indicating objective sensitivity of a variable at its bound (often 0 implies alternate optima).
  • Binding constraint: a constraint satisfied exactly at the solution (slack = 0).

Disclaimer

This article and the associated calculators are provided for educational and informational purposes only. Optimization outcomes depend on modeling assumptions and input data and may not reflect real-world constraints unless you encode them correctly. This is not legal, financial, operational, or safety advice. For high-stakes decisions, validate results against domain requirements and consult a qualified professional. For details, read our full disclaimer.

Sources
  1. Microsoft Support — Load the Solver Add-in in Excel
  2. Microsoft Support — Define and solve a problem by using Solver
  3. OpenSolver — Open-source optimization add-in for Excel (background and alternatives)
  4. Google OR-Tools — Linear Programming documentation (solver concepts)
  5. Wikipedia — Linear programming (definitions, duality context)