How to Do Linear Programming in Excel: A Step-by-Step Guide

Linear Programming (LP) is one of those topics that looks intimidating on paper but becomes much more approachable once you see it laid out in a spreadsheet. Most stats and operations research courses eventually ask you to solve an LP problem not just by hand, but in Excel using Solver. The good news: you don’t need to be a programmer or have specialized optimization software. Excel’s built-in Solver add-in can handle LP problems with just a bit of setup.

This guide walks through the full process from enabling Solver to structuring your spreadsheet to interpreting the output, to using a worked example so you can follow along.

A Quick Refresher: What Is Linear Programming?

Linear Programming is a method for finding the best outcome (maximum profit, minimum cost, etc.) in a mathematical model whose requirements are represented by linear relationships. Every LP problem has three components:

1. Decision variables are the quantities you’re trying to determine (e.g., how many units of Product A and Product B to make)
2. Objective function is the linear expression you want to maximize or minimize (e.g., total profit)
3. Constraints are linear inequalities or equations that limit your decision variables (e.g., limited labor hours, limited raw materials)

Excel’s Solver uses the Simplex LP algorithm to find the optimal values of your decision variables. These values give you the best possible objective function value while satisfying every constraint.

The Example We’ll Work Through

Let’s say a small furniture workshop makes tables and chairs. Each product requires labor and wood, and the workshop wants to figure out the production mix that maximizes profit.

• Profit per table: $40
• Profit per chair: $30
• Each table requires 2 hours of labor; each chair requires 1 hour
• Each table requires 1 unit of wood; each chair requires 1 unit of wood
• Available labor: 40 hours per week
• Available wood: 30 units per week

Mathematically:

Maximize:   Z = 40x₁ + 30x₂

Subject to:
  2x₁ + x₂  ≤ 40      (labor constraint)
   x₁ + x₂  ≤ 30      (wood constraint)
        x₁ ≥ 0, x₂ ≥ 0

Where x₁ = number of tables, x₂ = number of chairs.

This is a small enough problem that you could solve it graphically by hand, which makes it perfect for checking that Excel gives you the right answer.

Step 1: Enable the Solver Add-in

Solver doesn’t ship active by default, so you’ll need to turn it on. On Mac, add-ins live under the Tools menu in the top menu bar — not under File, and not inside a ribbon dialog like on Windows.

1. Click Tools in the top menu bar
2. Select Excel Add-ins… (make sure it’s this exact item. Mac Excel has both “Add-ins” and “Excel Add-ins” as separate menu entries, and Solver only lives in the latter)
3. In the dialog that opens, check the box next to Solver Add-in
4. Click OK
5. Go to the Data tab; you should now see Solver on the far right, in the Analysis group

If Solver doesn’t show up right away, fully quit Excel (Cmd+Q) and reopen it. The add-in occasionally needs a fresh launch to register on the ribbon.

(If you ever work on this in Excel for Windows: the path there is File → Options → Add-ins → Manage: Excel Add-ins → Go → check Solver Add-in.)

Step 2: Structure Your Spreadsheet

Good LP spreadsheets follow a consistent layout: one cell per decision variable, one cell for the objective function, and one row per constraint. Here’s a layout that works well:

Cell Content

B2 Tables (x₁) decision variable, starts at 0

C2 Chairs (x₂) decision variable, starts at 0

B4 Profit per table ($40)

C4 Profit per chair ($30)

B6 Objective function: =B2*B4 + C2*C4

B9:C9 Labor coefficients (2, 1)

B10:C10 Wood coefficients (1, 1)

D9 Labor used: =B2*B9 + C2*C9

D10 Wood used: =B2*B10 + C2*C10

E9 Labor available: 40

Get Esraeslem’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

E10 Wood available: 30

A simple way to think about it: row for each constraint, column for each variable, with a “used” column that sums up consumption and an “available” column showing the limit. This structure makes Solver setup almost mechanical, and it’s the same structure your professor will expect if you’re being graded on process, not just the final numbers.

Step 3: Enter the Formulas

Using the layout above:

• B2, C2: leave as 0 for now — Solver will fill these in
• B6 (objective cell): =B2*B4+C2*C4
• D9 (labor used): =B2*B9+C2*C9
• D10 (wood used): =B2*B10+C2*C10

Double-check that every formula references cells rather than typed-in numbers — this is what lets Solver adjust B2 and C2 and have everything else update automatically.

Step 4: Launch Solver and Define the Model

Go to Data → Solver. A dialog box opens with three key sections:

1. Set Objective: Click the field, then click cell B6. Choose Max (since we’re maximizing profit).

2. By Changing Variable Cells: Select B2:C2 these are the cells Solver is allowed to adjust.

3. Subject to the Constraints: Click Add for each constraint:

• D9 <= E9 (labor used ≤ labor available)
• D10 <= E10 (wood used ≤ wood available)
• B2 >= 0 and C2 >= 0 (non-negativity — or just check "Make Unconstrained Variables Non-Negative" below the constraint list)

4. Select a Solving Method: Choose Simplex LP from the dropdown. This is critical — Simplex LP is the algorithm designed for linear problems and is what gives you a guaranteed optimal (not just approximate) solution.

Click Solve.

Step 5: Interpret the Results

Solver will pop up a dialog asking if you want to keep the solution. Before clicking OK, notice the Reports box on the right select Answer and Sensitivity, then click OK. This generates two new sheets alongside your model.

For our furniture example, Solver should return:

• x₁ (tables) = 10
• x₂ (chairs) = 20
• Maximum profit = $1,000

You can sanity-check: 2(10) + 20 = 40 (labor fully used), and 10 + 20 = 30 (wood fully used). Both constraints are binding. This means the workshop is using every available hour and every unit of wood, which makes sense at an optimum.

Bonus: Reading the Sensitivity Report

This is the part that trips up a lot of students but is usually exactly what a stats/OR course wants you to interpret. The Sensitivity Report has two tables:

Variable Cells table — shows the “Reduced Cost” for each decision variable. A reduced cost of 0 means the variable is part of the optimal solution (as both of ours are here). A nonzero reduced cost tells you how much the objective coefficient would need to improve before it becomes worthwhile to produce that item at all.

Constraints table — this is where shadow prices live. The shadow price tells you how much the objective function would improve if you had one more unit of a resource. For example, if the shadow price on wood is $10, that means one additional unit of wood would increase profit by $10 — useful information if you’re deciding whether to buy more raw material. The report also gives you the allowable increase/decrease range over which that shadow price remains valid, which is often exactly what homework problems ask you to report.

Common Mistakes to Avoid

• Forgetting to select “Simplex LP”: Excel defaults to a nonlinear solving method (GRG Nonlinear), which can give you a locally optimal (and wrong) answer for a linear problem.
• Hardcoding numbers into the objective/constraint cells: instead of referencing the variable cells. This breaks Solver’s ability to search for a solution.
• Missing non-negativity constraints: without them, Solver may return nonsensical negative production quantities.
• Not checking “Answer” and “Sensitivity” reports: if your assignment asks about shadow prices or binding constraints, you need these reports, not just the final variable values.
• Confusing ≤ and ≥ when adding constraints a flipped inequality will silently give you a completely different (and wrong) feasible region.

Wrapping Up

Once you’ve built one LP model in Excel, the pattern becomes very repeatable: define your variable cells, write your objective as a formula, list your constraints as “used vs. available” pairs, and let Simplex LP do the searching. The real skill your course is testing isn’t the Excel mechanics; it’s translating a word problem into decision variables, an objective function, and constraints in the first place. Once that translation is done correctly, Excel does the heavy lifting.

If you want to practice, try modifying the furniture example: what happens to the optimal mix if labor drops to 30 hours? What if the profit per chair rises to $35? Re-running Solver on small tweaks like these is a great way to build intuition for how shadow prices and binding constraints actually behave, and it’s exactly the kind of exploration that makes concepts like this stick for exams.