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.

**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.

- Click on the
**Options**menu.

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

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

### 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 **X**** _{1}**,

**X**

**, and**

_{2}**X**

**variables.**

_{3}### 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.

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

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

### 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.

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

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

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

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

Here, the maximized value of the objective function is 71, and the values for the variables **X**** _{1}**,

**X**

**, and**

_{2}**X**

**are 0, 11.76, and 4.12 respectively for this solution.**

_{3}**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.

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

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

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

### 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.

- In the
**Load Model**user form, click on the**Replace**button.

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.

### 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.

**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.

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

- A chart like the following will appear.

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

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

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

- Afterward, click on the
**Add**button.

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

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

- The chart for constraint lines is shown below:

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

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

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

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

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

- How to Solve Integer Linear Programming in Excel
- How to Calculate Shadow Price Linear Programming in Excel
- How to Perform Mixed Integer Linear Programming in Excel
- How to Solve Transportation Problem with Linear Programming
- How to Do Linear Programming with Sensitivity Analysis in Excel
- How to Graph Linear Programming in Excel

**<< Go Back to Solver in ExcelÂ |Â Learn Excel**

Excel Linear Programming (Using Solver and Graphical Methods)

I thought this article was made extremely well. It showed how to install the necessary tools, how to use linear programming on excel (step by step) and also showed other excel solver alternatives such as IBM ILOG CPLEX optimizer. The only thing that I would like added to this article is an example along with the step-by-step tutorial just to add context to the steps. I would like to apply these models in figuring out problems such as how many units of product A and B should we produce to maximize profit?

Hello

Martin,We are glad to hear that you found this article useful! Including a real-life example in tutorials can indeed make the steps more relatable and easier to understand. Applying these models to specific scenarios like maximizing profit by determining the production units for products A and B is a great way to leverage linear programming. We will include this in our next update.

Regards

ExcelDemy