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.
  • 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 click Go.

enabling excel solver

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

solver appearing on ribbon


Example 1 – Using Excel Solver to Maximize Profit of Products

Below is the dataset for this example.

excel solver examples for maximizing profit dataset

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.

solver parameter of excel solver examples of maximizing problem

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

excel solver examples of maximizing profit of products

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.

minimizing shipping cost dataset

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.

parameters of excel solver examples of minimizing problem

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

solver result box of excel solver examples of minimizing problem

The Solver displays the solution shown in the following figure.

excel solver examples of minimizing shipping cost


Example 3 – Using Investment Portfolio Optimization With Excel Solver

Below is the dataset for this example.

excel solver examples of portfolio optimization dataset

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.

solver parameters of portfolio optimization

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

excel solver examples of portfolio optimization


Example 4 – Using the Linear Integer Programming with Excel Solver

Below is the dataset for this example:

excel solver examples of linear integer programming

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.

excel solver examples of linear integer programming

Read More:  Example with Excel Solver to Minimize Cost


Example 5 – Scheduling with Excel Solver

Below is the dataset for this example.

excel solver examples of scheduling

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.

solving parameters for scheduling

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

excel solver examples of scheduling

Read More: How to Assign Work Using Evolutionary Solver in Excel


Example 6 – Using Excel Solver for Marketing Budget Allocations

Below is the dataset for this example.

excel solver examples of marketing budge allocation dataset

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.

solver parameter for marketing

  • Click Solve.
  • Click OK on the Solver Results

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

excel solver examples of marketing budget allocation

Read More: How to Use Excel Solver for Linear Programming


Download the Practice Workbook

Download the workbook to practice.


Excel Solver Examples: Knowledge Hub


<< Go Back to Solver in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Kawser Ahmed
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

4 Comments
  1. Hi

    This is very helpful for me. Thanks.

  2. Thank you, this helps a lot.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo