Excel** Solver** is one of the most useful features you can come across while analyzing data in Excel. This is a what-if analysis feature in the form of an Excel Add-in. This article will focus on different examples of the **Solver** feature in Excel including many different areas.

**Table of Contents**hide

## What Is Solver in Excel?

Solver is a Microsoft Excel add-in program. The Solver is part of the What-If Analysis tools that we can use in Excel to test different scenarios. We can solve decision-making issues using the Excel tool Solver by finding the most perfect solutions. They also analyze how each possibility impacts the worksheetâ€™s output.

## How to Enable Solver Feature in Excel

You can access Solver by choosing **Data âžª Analyze âžª Solver**. Sometimes it may happen that this command isnâ€™t available, you have to install the Solver add-in using the following steps:

- First of all, choose the
**File** - Secondly, select
**Options**from the menu.

- Thus, the
**Excel Options**dialog box appears. - Here, go to the
**Add-Ins** - At the bottom of the
**Excel Options**dialog box, select**Excel Add-Ins**from the**Manage**drop-down list and then click**Go**.

- Immediately, the
**Add-ins**dialog box appears. - Then, place a checkmark next to
**Solver Add-In**, and then click**OK**.

Once you activate the add-ins in your Excel workbook, they will be visible on the ribbon. Just move to the **Data **tab and you can find the **Solver **add-in on the **Analyze **group.

## How to Use Solver in Excel

Before going into more detail, hereâ€™s the basic procedure for using Solver:

- First of all, set up the worksheet with values and formulas. Make sure that you have formatted cells correctly; for example, the maximum time you canâ€™t produce partial units of your products, so format those cells to contain numbers with no decimal values.
- Next, choose
**Data âžª Analysis âžª****Solver**. The Solver Parameters dialog box will appear. - Afterward, specify the target cell. The target cell also is known as the objective.
- Then, specify the range that contains the changing cells.
- Specify the constraints.
- If necessary, change the Solver options.
- Let the Solver solve the problem.

Initially, we are going to focus on two simple problems using the Excel solver. The first one will be maximizing profit from a series of products and the second one focuses on minimizing the production cost. These are just two examples to show the procedure of Excel solver in two different scenarios. More problems regarding the same feature will follow in the later part of the article.

## Example 1: Application of Excel Solver to Get Maximize Profit of Products

Letâ€™s take a look at the following dataset first.

The highest profit comes from Product C. Therefore, to maximize total profit from production, we can produce only Product C. But if things were so straightforward, you wouldnâ€™t need tools like Solver. This company has some constraints that must be met to produce products:

- The combined production capacity is 300 units per day.
- The company needs 50 units of Product A to fill an existing order.
- The company needs 40 units of Product B to fill an expected order.
- The market for Product C is relatively limited. So the company is not interested in producing more than 40 units of this product per day.

Now letâ€™s see how we can use the solver to work with the problem.

**Steps:**

- First, go to the
**Data**tab on your ribbon. - Then select
**Solver**from the**AnalysisÂ**group.

- Now select cell
**E8**as the objective cell of the**Solver ParameterÂ**box. - Besides the
**To:**options select**Max**as we are trying the maximize the value of the cell. - In the
**By Changing Variable Cells**, select the cell values we are mainly focusing on changing. Here, they belong to the range**C5:C7**. - Now add the constraints by clicking on the
**Add**button on the right of the box. - Finally, select
**Simplex LP**in the**Select a Solving Method**box.

- Once you are done with all the steps above, click on
**Solve**at the bottom of the box. - After that, the
**Solver Results**box will appear. - Now select the options and reports you want to prefer in this box. For the demonstration, we are choosing to enable the
**Keep Solver Solution**option only.

- Next, click on
**OK**. - The dataset will now change to this.

This indicates the optimum number of units required to have the maximum profit within the constrained entered. This is just one of the examples that demonstrate how powerful the Excel solver feature can be.

## Example 2: Use of Excel Solver for Minimizing Shipping Cost

After the maximizing problem above, letâ€™s look at an example focusing on minimizing values. We will use **SUM** and **SUMPRODUCT** functions for calculating different parameters. For that, letâ€™s take the following dataset.

**Shipping Costs Table**: This table contains the cell range **B4:E10**. This is a matrix that holds per-unit shipping costs from each warehouse to each retail outlet. For example, the cost to ship a unit of a product from Boston to Detroit is $38.

**Product needs of each retail store**: This information appears in the cell range **C14:C19**. For example, the retail outlet in Houston needs 225, Denver needs 150 units, Atlanta needs 100 units, and so on. **C18** is a formula cell that calculates the total units needed from the outlets.

**No. to ship fromâ€¦**: Cell range **D14:F19** holds the adjustable cells. These cell values will be varied by Solver. We have initialized these cells with a value of 25 to give Solver a starting value. Column **G** contains formulas. This column contains the sum of units the company needs to ship to each retail outlet from the warehouses. For example, **G14 **shows a value of 75. The company has to send 75 units of products to the Denver outlet from three warehouses.

**Warehouse inventory**: Row 21 contains the amount of inventory at each warehouse. For example, the Los Angeles warehouse has 400 units of inventory. Row 22 contains formulas that show the remaining inventory after shipping. For example, Los Angeles has shipped 150 (see, row 18) units of products, so it has the remaining 250 (400-150) units of inventory.

**Calculated shipping costs**: Row 24 contains formulas that calculate the shipping costs.

The solver will fill in the values in the cell range **D14:F19** in such a way that will minimize the shipping costs from the warehouses to the outlets. In other words, the solution will minimize the value in cell **G24 **by adjusting the values of cell range D14:F19 fulfilling the following constraints:

- The number of units demanded by each retail outlet must equal the number shipped. In other words, all the orders will be filled. The following specifications can express these constraints:
**C14=G14, C16=G16, C18=G18, C15=G15, C17=G17,**and**C19=G19** - The number of units remaining in each warehouseâ€™s inventory must not be negative. In other words, a warehouse canâ€™t ship more than its inventory. The following constraint shows this:
**D24>=0, E24>=0, F24>=0**. - The adjustable cells canâ€™t be negative because shipping a negative number of units makes no sense. The Solve Parameters dialog box has a handy option: Make Unconstrained Variables Non-Negative. Make sure this setting is enabled.

Letâ€™s walk through the following steps to do the task.

**Steps:**

- First of all, we will be setting some necessary formulas. To calculate to be shipped, type the following formula.

`=SUM(D14:F14)`

- Then, press
**Enter**.

- Next, drag the Fill Handle icon up to cell
**G19**to fill the other cells with the formula. - Therefore, the output will look like this.

- Afterward, to calculate the total, type the following formula.

`=SUM(C14:C19)`

- Then, press Enter.

- Next, drag the Fill Handle icon to the right up to cell
**G20**to fill the other cells with the formula. - Therefore, the output will look like this.

- Afterward, to calculate the shipping costs, type the following formula.

`=SUMPRODUCT(C5:C10,D14:D19)`

- Then, press
**Enter**.

- Next, drag the Fill Handle icon to the right up to cell
**F26**to fill the other cells with the formula. - Now type the following formula in cell
**G26**.

`=SUM(D26:F26)`

- To open the
**Solver Add-in**, go to the**Data**tab and click on**Solver**.

- Next, fill the
**Set Objective field**with this value:**$G$26**. - Then, select the radio button of the
**Min**option in To Control. - Select cell
**$D$14**to**$F$19**to fill the field**By Changing Variable Cells**. This field will show then**$D$14:$F$19**. - Now,
**Add**constraints one by one. The constraints are:**C14=G14, C16=G16, C18=G18, C15=G15, C17=G17, C19=G19, D24>=0, E24>=0, and F24>=0**. These constraints will be shown in the Subject to the Constraints field. - Afterward, select the
**Make Unconstrained Variables Non-Negative**check box. - Finally, select
**Simplex LP**from the Select a Solving Method drop-down list.

- Now, click on the
**Solve**The following figure shows the**Solver Results**dialog box. Once you click**OK**, your result will be displayed.

- The Solver displays the solution shown in the following figure.

## More About Excel Solver

Weâ€™re going to discuss the Solver Options dialog box in this section. Using this dialog box, you can control many aspects of the solution process. You can also load and save model specifications in a worksheet range using this dialog box.

Usually, youâ€™ll want to save a model only when you use more than one set of Solver parameters with your worksheet. Excel saves the first Solver model automatically with your worksheet using hidden names. If you save additional models, Excel stores the information in the form of formulas that correspond to the specifications. (The last cell in the saved range is an array formula that holds the options settings.)

It may happen that Solver will report it canâ€™t find a solution, even when you know that one solution should exist. You can change one or more of the Solver options and try again. When you click the Options button in the Solver Parameters dialog box, the Solver Options dialog box shown in the following figure appears.

A simple Solver example in Excel

We can control many aspects of how Solver will solve a problem.

Here is a brief description of Solverâ€™s options:

**Constraint Precision**: Specify how close the Cell Reference and Constraint formulas must be to satisfy a constraint. Specifying less precision will make Excel solve the problem more quickly.

**Use Automatic Scaling**: It is used when the problem deals with large differences in magnitudeâ€” when you attempt to maximize a percentage, for example, by varying cells that are very large.

**Show Iteration Results**: By selecting this checkbox, the Solver is instructed to pause and display the results after each iteration.

**Ignore Integer Constraints**: If you select this check box, Solver will ignore constraints that mention that a particular cell must be an integer. Using this option may permit Solver to find a solution that canâ€™t be found otherwise.

**Max Time**: Mention the maximum amount of time (in seconds) that you want Solver to spend on a single problem. If the Solver reports that it exceeded the time limit, you can increase the amount of time that it will spend searching for a solution.

**Iterations**: Enter the maximum number of trial solutions that you want Solver to try to solve the problem.

**Max Subproblems**: It is used to solve complex problems. Specify the maximum number of sub-problems that may be solved by the Evolutionary algorithm.

**Max Feasible Solutions**: It is used for complex problems. Specify the maximum number of feasible solutions that may be solved by the Evolutionary algorithm.

## Example 3: Investment Portfolio Optimization with Excel Solver

In this section, we will look at an investment portfolio problem, which can also be said to be a financial problem. We are going to optimize such with the help of the Excel solver. The aim of portfolio or financial optimization is to identify the optimal portfolio (asset distribution) among those that are portfolios given a certain objective. In most cases, the objective is to maximize benefits, like predicted return, while minimizing liabilities, such as financial risk.

Letâ€™s look at the following investment portfolio.

The problem statement is described below.

- The amount that the credit union will invest in new-car loans must be at least three times the amount that the credit union will invest in used-car loans. The reason is: that used car loans are riskier investments. This constraint is represented as
**C5>=C6*3** - Car loans should make up at least 15% of the portfolio. This constraint is represented as
**D14>=.15** - Unsecured loans should make up no more than 25% of the portfolio. This constraint is represented as
**E8<=.25** - At least 10% of the portfolio should be in bank CDs. This constraint is represented as
**E9>=.10** - The total amount invested is $5,000,000.
- All investments should be positive or zero.

Follow these steps to see how you can use the solver in Excel for examples like this.

**Steps:**

- First, select the
**Data** - Then select
**Solver**from the**Analysis**

- Now Fill the Set Objective field with this value :$E$13.
- Then select the radio button for the
**Max**option in**To** - After that, select cell
**$D$6**to**$D$10**to fill the field**By Changing Variable Cells**. This field will show then**$D$6:$D$10**. - Add constraints one by one. The constraints are:
**$D$11= $C$4 $D$6>= $D$7*3, $E$15>= 0.15, $F$9<= 0.25, $F$10>= 0.1**. These constraints will be shown in the**Subject to the Constraints**box. - Select the
**Make Unconstrained Variables Non-Negative**check box. - Select
**GRG Nonlinear**from the**Select a Solving Method**drop-down list.

- Now click the
**Solve**Click**OK**. - There will be another dialog box in which you need to select the result types.
- This means you need to select
**Keep Solver Solution**. Otherwise, the values will revert back to the original values. - Then from the right side of the dialog box, select all the options in the
**Reports**. - Then click
**OK**after this.

- Weâ€™ve entered 1,000,000 in the changing cells as the starting values. When you run Solver with these parameters, it produces the solution shown in the following figure which has a total yield of 25%.
- The Auto Loans values also changed to 15%.
- And this is how we got the highest optimization value of Total Yield considering all the constraints.

And this is how we complete the optimization of an investment portfolio using the Excel solver.

## Example 4: Linear Integer Programming Using Excel Solver

Letâ€™s take a look at an example of an Excel solverâ€™s usage in Integer Linear programming. First, take a look at a suitable dataset for the problem.

Now itâ€™s time to look at the particulars of the Excel solver for this integer linear programming example:

**Decision Variables:**

X1: Production quantity of product 1.

X2: Production quantity of product 2.

Y: 1 if the first setting is selected or 0 if the second setting is selected.

**Objective Function:**

Z=10X1+12X2

**Constraints:**

X1+X2<=35

X1-8Y<=12

X2+15Y<=25

Y={0,1}

X1,X2>=0

Now follow these steps to see how we can solve this particular integer linear programming example in Excel using the solver.

**Steps:**

- First, go to the
**Data**tab and select**Solver**from the**Analysis**

- Now put in the values and constraints in the
**Solver Parameter**box as shown in the figure.

- Then click on
**Solve**. - Next, click
**OK**on the**Solver Results**.

The final result of using the Excel solver on the integer linear programming example will be like this.

## Example 5: Scheduling with Excel Solver

Suppose that the bank has 22 employees. How should the workers be scheduled so that they would have the maximum number of weekend days off? We will maximize the number of weekend days off with a fixed number of employees in this scheduling example of the Excel solver.

Letâ€™s look at the dataset.

The constraints are shown in the figure. To solve the scheduling problem and use the solver in examples like that you can follow these steps.

**Steps:**

- First, go to the
**Data**tab on your ribbon and select**Solver**from the**Analysis**

- Next, put in the values of the constraints and the parameters as shown in the figure below.

- After that, click on
**Solve**. - Finally, click on
**OK**on the**Solver**Results.

The solver will automatically show the result of the scheduling problem on the Excel spreadsheet because of the choices we made in the steps.

You can use the solver in Excel in similar examples like that.

## Example 6: Excel Solver for Marketing Budget Allocations

Finally, letâ€™s take a look at a scenario where we need to use the solver in Excel for marketing budget allocations. For that, letâ€™s take a dataset like this.

Here, we have the current stats on the left, and the portion where we are going to use the solver is on the right.

Follow these steps to find out how we can tackle this marketing problem with Excel Solver.

**Steps:**

- First, go to the
**Data**tab on your ribbon and select the**Solver**from the**AnalysisÂ**group.

- Then write down the following constraints and the parameters as shown in the figure.

- After that, click on
**Solve**. - Next, click on
**OK**on the**Solver Results**

The values will change because of the constraints and parameters we have chosen.

You can use the solver in Excel in similar examples like that.

**Download Practice Workbook**

Download the workbook used for the demonstration from the link below.

## Conclusion

That concludes the article for Excel solver examples. Hopefully, you have grasped the idea of using the Excel solver for different scenarios from these examples. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

Hi

This is very helpful for me. Thanks.

Hi JOHN,

It is a pleasure to hear that this article has been helpful to you.