How to Use the Solver Feature in Excel (with Practical Examples)

How to Enable Solver Feature in Excel

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

• Choose the File
• Select Options from the menu.

• The Excel Options dialog box appears.
• At the bottom of the Excel Options dialog box, select Excel Add-Ins from the Manage drop-down list and click Go.

• The Add-ins dialog box appears.
• Place a checkmark next to Solver Add-In, and click OK.

Once you activate the add-ins in your Excel workbook, they will be visible on the ribbon.

• Move to the Data tab to find the Solver add-in on the Analyze group.

Example 1 – Using Excel Solver to Maximize Profit of Products

Below is the dataset for this example.

Steps:

• Go to the Data tab on your ribbon.
• Select Solver from the AnalysisÂ group.

• Select cell E8 as the objective cell of the Solver ParameterÂ box.
• Next to the To: option, select Max.Â
• Select the cell values we want to change in the By Changing Variable CellsÂ (i.e., the range C5:C7).
• Add the constraints by clicking the Add button on the right of the box.
• Select Simplex LP in the Select a Solving Method box.

• Click Solve. The Solver Results box will appear.
• Select the options and reports you want in this box. We are enabling the Keep Solver Solution option only.

• Click OK.
• The dataset will now change to this.

This indicates the optimum number of units required for 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 -Using the Excel Solver for Minimizing Shipping Cost

Below is the dataset for this example.

Steps:

• Enter the following formula:

`=SUM(D14:F14)`

• Press Enter.

• Drag the Fill Handle icon to cell G19 to fill the other cells with the formula.
• The output will look like this.

• To calculate the total, enter the following formula:

`=SUM(C14:C19)`

• Press Enter.

• Drag the Fill Handle icon to the right up to cell G20 to fill the other cells with the formula.

The output will look like this.

• To calculate the shipping costs, enter the following formula:

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

• Press Enter.

• Drag the Fill Handle icon to the right up to cell F26 to fill the other cells with the formula.
• Enter the following formula in cell G26:

`=SUM(D26:F26)`

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

• Fill the Set Objective field with this value: \$G\$26.
• Select the radio button of the Min option in To Control.
• Select the cell range \$D\$14 to \$F\$19 to fill the field By Changing Variable Cells. This field will then showÂ \$D\$14:\$F\$19.
• 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.
• Select the Make Unconstrained Variables Non-Negative check box.
• Select Simplex LP from the Select a Solving Method drop-down list.

• Click Solve.Â The following figure shows the Solver Results dialog box.

The Solver displays the solution shown in the following figure.

Example 3 – Using Investment Portfolio Optimization With Excel Solver

Below is the dataset for this example.

Steps:

• Select the Data
• Select Solver from the Analysis

• Fill the Set Objective field with this value: \$E\$13.
• Select the radio button for the Max option in To
• Select cell range \$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.

• 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 return to their original values.
• Then from the right side of the dialog box, select all the options in the Reports.
• Click OK.

• 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%.
• This is how we got the highest optimization value for the total yield, considering all the constraints.

Example 4 – Using the Linear Integer Programming with Excel Solver

Below is the dataset for this example:

Steps:

• Go to the Data tab and select Solver from the Analysis

• Enter the values and constraints in the Solver Parameter box, as shown in the figure.

• Click Solve.
• Click OK on the Solver Results.

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

Read More: Â Example with Excel Solver to Minimize Cost

Example 5 – Scheduling with Excel Solver

Below is the dataset for this example.

Steps:

• Go to the Data tab on your ribbon and select Solver from the Analysis

• Enter the values of the constraints and the parameters, as shown in the figure below.

• Click Solve.
• Click OK on the Solver Results.

Because of the choices we made in the steps, the solver will automatically show the scheduling problem’s result on the Excel spreadsheet.

Example 6 – Using Excel Solver for Marketing Budget Allocations

Below is the dataset for this example.

Steps:

• Go to the Data tab on your ribbon and select the Solver from the AnalysisÂ group.

• Enter the following constraints and the parameters, as shown in the figure.

• Click Solve.
• Click OK on the Solver Results

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

Excel Solver Examples: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio