Linear Programming with Excel Solver

Applicable to Excel 2002-2016 (including Office 365)
(Google Drive Solver Procedures are Available Separately)

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.

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 located).  Notes:
    Example:

    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 where example cell formulas go.  Note:  it is not necessary to put data in exactly this format (click here for example spreadsheet):

Spreadsheet Data and Formulas for LP

The following steps vary slightly with Excel version:

3.  Start Solver:

Excel 2002/03:  click Tools at the top, then Solver.

Excel 2007/10/13/16:  click the 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 containing 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:

LP Constraint Dialog Box

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):

Solver Parameters Dialog Box

In Excel 2010/13/16, it will look like this:

Solver 2010 Parameters Dialog Box


7.  Important:  two options must be set:

In Excel 2002/03/07:  Click Options, then click to place checkmarks by "Assume Linear Model" and "Assume Non-Negative"  as follows:

Solver Options Dialog Box

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:
Excel 2010 Final Parameters

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 Tips below.

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:
Excel LP Answer Report

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:
     http://faculty.sfasu.edu/fisherwarre/solver_workaround.html
Better yet, click here for instructions on how to get Office 365 for free!

Tips:


Send comments and corrections concerning this page to:
wfisher@sfasu.edu
Last updated October 16, 2016