Linear Programming with Excel Solver
Applicable to Excel 2002-2016
(including Office 365)
1. Before attempting to solve a linear programming problem with
Excel, make sure that the "Solver" add-in has been
activated. See the Excel Add-Ins
web page for details.
(Google Drive Solver Procedures are Available Separately)
2. Enter all data from the problem into cells. The format
below is acceptable but not required (Excel doesn't care where you put things, but
you do have to tell the Solver program where key elements are
- include separate cells for both the values and objective coefficients of
- objective coefficients are the numbers in the objective function
- 'values' represent the actual values of the decision variables.
Initially, the "values" are normally set to
zero. After solution, Excel will place the optimal decision
variable values in the value cells.
- include one
cell in which profit is computed from the decision variable values and
objective coefficients (easiest with a sumproduct function--click here for explanation).
- include cells in which the total quantity used of each constraint
is calculated, i.e., the constraint's left-hand side (again,
easiest with a sumproduct).
- careful use of absolute cell references will allow you to enter
just the first formula, then copy it to the remaining rows--see Example
- include cells that contain the right-hand side of each constraint.
- you may optionally include
cells for constraint type (<=, >=, or =). Doing so is
encouraged to make your spreadsheet more readable and to remind you of
the constraint types, but these cells are
not used by the solver.
- do not include
non-negativity constraints on the spreadsheet (non-negativity is
handled under Solver Options).
Max Z= 5 X1 + 8 X2
ST 2 X1 + 4 X2 <= 40
6 X1 + 3 X2 <= 42
X1 >= 3
X1, X2 >= 0
You could set up a spreadsheet as follows, in which arrows indicate
example cell formulas go. Note: it is not necessary to
put data in exactly this format (click here for example spreadsheet):
The following steps vary slightly with Excel version:
3. Start Solver:
Tools at the top, then Solver.
Excel 2007/10/13/16: click
Data tab at the top, then click Solver in the Analysis section toward
the top right (called "Analyze" in Excel 2016).
4. Click in the "Set Target Cell" box for Excel 2002/03/07, or
the "Set Objective" box for Excel 2010/13/16, then select the one cell
the objective function
formula. Also click the Max or Min button as applicable
(linear programming does not use the "Value of" option).
5. Click in the "By Changing Cells" box for Excel 2002/03/07, or
the "By Changing Variable Cells" box for Excel 2010/13/16, then select the
decision variable value cells.
6. Click the Add button next to the "Subject to the
Constraints" section, which brings up:
For each constraint:
Repeat the above steps for additional constraints. After adding
the last constraint, click Cancel to return to the Solver Parameters
dialog box. In Excel 2002/03/07, it should look something like
the following (cell
references from the above example):
- click in the "Cell
Reference" box, then click the cell that contains the formula for the
left-hand side total
- change the constraint type if necessary
- click the Constraint box and select the cell that contains the constraint right-hand side
- click Add to
finish the constraint
In Excel 2010/13/16, it will look like this:
two options must be set:
In Excel 2002/03/07:
Click Options, then
place checkmarks by "Assume Linear Model" and "Assume
Non-Negative" as follows:
Leave the other options alone. Click OK to return to the Solver
Parameters box, which should look the same as shown in step 6 above.
In Excel 2010/13/16: the
necessary options are on the main Solver Parameters screen shown above:
The Excel 2010/13/16 Parameters should now look like:
- Check the box "Make Unconstrained Variables Non-Negative"
- Click the down arrow to the right of "Select a Solving Method"
and change it from "GRG Nonlinear" to "Simplex LP".
See Tips below if you open a spreadsheet in an Excel version other than
the one in which it was created.
8. Check the problem as entered. If everything
appears correct, click Solve.
If all is well, you will see "Solver
found a solution." If solver says anything else, it
did NOT find a solution--see
9. After Solver finds a solution, click to select the Answer
and/or Sensitivity reports in the Reports section as
required for your assignment. Do not change any other settings. Click OK, and Solver will
create new spreadsheet(s) containing the reports. Solver will also
place the optimal decision variable values in the respective cells of
the original sheet. Click on the tab at the bottom of the screen
to view or print each report. (click here for solved example spreadsheet).
The Answer Report for the above problem should look something like this
in Excel 2016, with some variation in other Excel versions:
Possible bug: If you use
Excel 2007, you may encounter a bug when you attempt to create the
reports. If you get a solution but it won't create the reports,
visit the following web page for a work-around:
Better yet, click here for instructions on how to get Office 365 for free!
- with proper use of absolute cell references, you only have to
enter the first
constraint left-hand side formula. It can then be copied to the
other constraint rows. That is done in the above example.
- do not use the
"Guess" button in Excel 2002/03/07 Solver Parameters. It almost
you have two or more constraints in a row of the same type
(<=, for example), you can enter them at one time. To do so,
drag across both left-hand side total formulas for the Cell Reference,
and drag across both right-hand side cells for the Constraint
box. Although it looks like one constraint in Solver, Excel
treats each Cell Reference/Constraint cell pair as a separate
- if you open a spreadsheet with linear programming in a version of
Excel other than the version used to create the spreadsheet, re-check that the options
are set correctly (see step 7 above).
- if the problem won't solve, re-check all the above steps including setting options(step
7). Still won't solve? In Dr. Fisher's classes, bring your
spreadsheet to him on a flash drive, or e-mail it to him. In
other classes, follow your professor's instructions.
Send comments and corrections concerning this page to:
Last updated October 16, 2016