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

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

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

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

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

Hi

This is very helpful for me. Thanks.

Hi JOHN,

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

Thank you, this helps a lot.

Dear

TonyYou are most welcome.

Regards

ExcelDemy