Excel Linear Programming (Using Solver and Graphical Methods)

Get FREE Advanced Excel Exercises with Solutions!

Are you interested in learning how to solve Linear Programming in Excel? Linear programming has numerous applications in various fields such as allocating resources, scheduling, optimizing vehicle routes and supply chain networks, analyzing game theories, etc.

In this article, we will present how to use Excel for solving linear programming problems using the Solver Add-in. It is a built-in Excel Add-in that can solve linear and non-linear programming problems that contain multiple variables and constraints. For solving linear programming models, it uses the Simplex LP method. While using the Excel Solver Add-in, we can also save and load a linear programming model for reuse purposes.

We can also apply the graphical method to solve a linear programming problem in Excel. However, this method is applicable only for problems with two variables.

We used Excel 365 for our operations while preparing this article. But you can find all the features and functions used in this article in Excel 2010 or newer versions.

Overview of Excel Linear Programming using graphical method

Click the image to get a detailed view


Download Practice Workbook


How to Do Linear Programming with Solver in Excel?

Excel Solver Add-in is a built-in tool, which is used for solving linear or non-linear optimization problems in Excel. It is a four-step process: adding Excel Solver Add-in, formulating a linear programming problem, tabulating the problem, and finally, solving the problem using Excel Solver.


How to Add Excel Solver Add-in?

The Excel Solver Add-in is not present in the Data tab by default. In such instances, we have to add in from the Add-ins menu of the File tab.

  • To add the Solver add-in from Excel Add-Ins, go to the Data tab >> check if the Solver add-in is present >> go to the File tab if the Solver tool is not present.

Looking for Solver Add-in in Data tab

  • Click on the Options menu.

Options menu in File tab

  • Afterward, from the Excel Options user form, click on the Add-ins option >> click on the Go option.

Going to Add-in options

  • Now, from the Add-ins user form, check the checkbox of Solver Add-in option >> click on the OK button. The Solver Add-in will be added to the Data tab.

Installing Solver Add-in


How to Define and Formulate the Linear Programming Problem?

A linear programming problem consists of an objective function and some constraints. The objective function can be maximized or minimized. Here, we will solve the following linear programming model which has an objective function Z, which we want to maximize, and 3 different constraints for the X1, X2, and X3 variables.

Linear Programming Model Description for Excel Solver


How to Tabulate Linear Programming Problems in Excel?

We have to format the coefficients of the objective function and constraints in a tabular format to solve the linear programming model with Excel Solver Add-in.

  • Now, tabulate the linear programming model in the following format to input the objective functions and constraints in the Excel Solver Add-in. Here, the Light Green colored boxes are kept empty for calculations and solutions.

Tabulating the Linear Programming Model

First, insert the following formula in cell F5. Since the range C5:E5 is empty now, cell F5 shows 0. This value indicates the current value of the objective function.

=SUMPRODUCT(C5:E5,C6:E6)

Applying formula for Objective function value

Afterward, for the constraints, apply the following formula in cell F8 >> press Enter key >>  drag the Fill Handle icon down for the remaining constraint values.

=SUMPRODUCT($C$5:$E$5,C8:E8)

Applying formula for constraint value


How to Solve the Problem Using Excel Solver?

After tabulating the problem, we can apply the Solver Add-in to solve our linear programming model.

  • Now, to solve the model, go to the Data tab >> click on the dropdown menu of the Analysis ribbon >> select the Solver option.
Using Excel Solver Add-in

Click the image for a detailed view

  • In the Solver Parameters user form, select the Set Objective parameter as cell $F$5 >> click on the radio button of the Max option for the To parameter >> select the range $C$5:$E$5 for the By Changing Variable cells parameter >> click on the Add button to add constraints.

Setting parameters in Solver

  • In the Add Constraint user form, set the Cell Reference to $F$8 >> select >= operator for the first constraint >> set the Constraint to $H$8 >> click on the Add button to similarly add constraint 2 and 3 >> click on the OK button.

Adding Constraints in Solver

  • We can see the added constraints in the Subject to the Constraints list box. Now, click on the dropdown menu of the Select a Solving Method option >> select Simplex LP >> click on the Solve button.

Selecting Solving Method

We will get our required values for the variables and objective function for the linear programming maximization problem.

Solution from Excel Solver for the Linear Programming Model

Here, the maximized value of the objective function is 71, and the values for the variables X1, X2, and X3 are 0, 11.76, and 4.12 respectively for this solution.

Read More: How to Find Optimal Solution with Linear Programming in Excel


How to Save and Load Solver Scenarios in Excel?

We can save and/or load any solved linear programming model variable values to view or reuse them later.


What Is the Process to Save Linear Programming Model?

  • To save a linear programming model, select a range to input the variable values >> go to the Data tab >> click on the Solver option.
Range for Saving the Model

Click the image for a detailed view

  • In the Solver Parameters user form, we can view the last solved model. Click on the Load/Save button.

Saving the Linear Programming model

  • In the Load/Save Model user form, provide the reference of the first cell in the selected range (cell $J$3 in this case) >> click on the Save button.
Selecting Range for Saving the Linear Programming Model

Click the image for a detailed view

We will get the linear programming model variable values in the selected range that we can view or reuse later.

Saved Model

Click the image for a detailed view


What Is the Process of Loading the Saved Model?

  • Similar to the previous example, we need to click on the Load/Save button from the Solver Parameters user form >> select the range holding the previous model ($J$3:$J$9 in this case) >> click on the Load button.
Loading Previously Saved Linear Programming Model in Excel

Click the image for a detailed view

  • In the Load Model user form, click on the Replace button.
Replacing previous model with saved model

Click the image for a detailed view

This will open the Solver Parameters user form and replace the existing model parameters with the loaded model’s parameters. All we need to do is to click the Solve button to recalculate it.


How to Do Linear Programming Using Graphical Method in Excel?

If a linear programming problem consists of only two variables, then we can use a graphical method to solve that model.


How to Define and Formulate the Problem?

Like the previous example, we also have to define an objective function and some constraints for the linear programming problem. Here, we will solve the following linear programming model which has an objective function Z, which we want to maximize, and 2 different constraints for the X and Y variables.

Linear Programming model description for Graphical method


How to Tabulate the Linear Programming Problem in Excel?

To start, we have to tabulate the linear programming model in the following format using the variable coefficients.

Tabulating the model for Excel Linear Programming

Read More: How to Do Linear Programming in Excel


How to Plot a Chart Using the Constraints?

Although the given constraints are inequality constraints, we will consider them as equality constraints while plotting charts and later use the inequality operators to find the feasible region.

Now, we have to calculate the X and Y-axis intersection points for each constraint. This can be accomplished by setting one variable equal to zero. For example, for the first constraint, if Y = 0 then X = 7, and if X = 0 then Y = 7.

  • We have to list these intersection points for each constraint in the following format.

Calculating X and Y-axis intersections for the constraints

  • Now, select the intersection points of constraint 1 >> go to the Insert tab >> click on the dropdown menu of the Scatter option >> select the Scatter with Smooth Lines chart.
Selecting a scatter chart for the X and Y-axis intersection points

Click the image for a detailed view

  • A chart like the following will appear.

Scatter chart for the first constraint

  • Select the chart and right-click on your mouse >> click on the Select Data option.

Format Control for the Chart

  • From the Select Data Source user form, select Series 1 and click on the Edit button.

Editing Chart Parameters

  • In the Edit Series user form, set the Series Name to range $B$9:$C$9 and press the OK button.

Setting up Chart Title for the first constraint

  • Afterward, click on the Add button.

Adding the second constraint in the chart

  • Set the Series Name to range $E$9:$F$9 >> Series X Values to $E$11:$E$12 >> Series Y Values to $F$11:$F$12 >> click on the OK button.

Setting up chart parameters for second constraint

  • Click on the OK button in the Select Data Source user form.

Adding the scatter chart

  • The chart for constraint lines is shown below:

Scatter chart for both constraints

  • Select the chart and click on the plus (+) icon to view the Chart Elements >> check the checkbox of Data Labels >> click on the right side of the Data Labels to see additional options >> click on the Data Callout option to see X and Y-axis intersection points >> uncheck Gridlines option to remove them.
Adding Data Label to view the X and Y-axis intersection points

Click the image for a detailed view


How to Determine the Coordinates of Each Corner Point in the Feasible Region?

Since both the constraints have <= (less than or equal to) operator and X, Y values are greater than or equal to zero, the marked ABCD region is our feasible region. (Here we have manually placed A B C D to show the corner points)

Selecting the feasible region for Excel Linear Programming

Now, we need to list all the corner points (A, B, C, and D) of the feasible region and calculate the objective function value in those points. We can get the X and Y values for A, B, and D from the Data Callouts.

  • To calculate X and Y values for point C, we can apply the following formula in cell C17.
=TRANSPOSE(MMULT(MINVERSE(C6:D7),F6:F7))

Corner points for the feasible region

Here, the MINVERSE function calculates the inverse matrix of the matrix formed with X and Y value coefficients in constraints (range C6:D7). And the MMULT function multiplies that inverse matrix with another matrix (range F6:F7). Finally, the TRANSPOSE function converts the rows into columns and columns into rows for the output returned by the MMULT function.

Read More: How to Find Optimal Solution with Linear Programming in Excel


How to Calculate the Value of the Objective Function for Each Corner Point?

Finally, we can calculate the value of the objective function at each corner point.

  • Apply the following formula in Cell E15 >> press the Enter key >> use the Fill Handle tool.
=SUMPRODUCT($C$5:$D$5,C15:D15)

Calculating Objective function value at each corner point for maximization of the linear programming problem in Excel

As we can see, the maximum value has occurred in point C. And the value of the maximized objective function is 23.

Read More: How to Solve Blending Linear Programming Problem with Excel Solver


What Are the Things to Remember?

  • Graphical method can deal with only two variables, whereas Excel Solver can deal with up to 200 variables.
  • You can save and/or load any solved linear programming model variable values to view or reuse them later.
  • While applying the MMULT function, the matrices should be sequenced properly as the matrices have to follow the multiplication rule.

Frequently Asked Questions

1. What are the limitations of using Excel Solver for Linear Programming?

Answer: Although Excel Solver Add-in is a powerful tool, it has many limitations. Some of these are listed below:

>> suited for small to medium-sized linear programming problems (up to 200 variables)

>> operates with limited precision (up to 15 decimal points)

>> operates with very few numbers of algorithms (Simplex Method for Linear Programming Problems), etc.

2. Can I use the graphical method for solving Linear Programming Problems with 3 variables?

Answer: If we apply the graphical method for Linear Programming problems with 3 variables, the feasible region will be a 3-dimensional space. Since representing 3-dimensional space on a two-dimensional plane can be complex and visually overwhelming, the graphical method is not suitable for Linear Programming problems with more than two variables.

3. Are there any alternative tools or software besides Excel Solver for solving Linear Programming problems?

Answer: Besides the Excel Solver add-in, some alternative tools or software used for solving Linear Programming problems are:

>> IBM ILOG CPLEX optimizer

>> MATLAB Optimization Toolbox

>> GNU Linear Programming Kit

>> PuLP, etc.


Conclusion

This concludes our article on learning Excel Linear Programming. We discussed the process of solving Linear Programming models of multiple variables using Excel Solver. We also discussed how to save a solved model and load it later for reuse. For linear programming problems of two variables, we also discussed the graphical method. We hope the demonstrated methods were able to fulfill your quest for learning about Linear Programming in Excel. Feel free to share your thoughts on the article in the comment box.


Excel Linear Programming: Knowledge Hub


<< Go Back to Solver in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Seemanto Saha
Seemanto Saha

Seemanto Saha graduated in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. He has been with ExcelDemy for a year, where he wrote 40+ articles and reviewed 50+ articles. He has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, he is working as a team leader for ExcelDemy. His role is to guide his team to write reader-friendly content. His interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo