The solver tool is used to find an optimum value for a formula in one cell, by changing decision variables. While solving linear programming problems the solver tool is essential. By reading this article you will get to know how to use solver in Excel.

To deliver a solution, the Solver needs a group of cells called the variable cells whose values are adjusted, to meet the target in the objective cell. These variable cells are subject to constraint criteria.

**How to Use Solver in Excel 2016 | 2019 | 2021 | 365**

The** Solver** is a fantastic tool for Excel. It has analysis skills that can solve complex problems with given constraints. Depending on the problem type it finds the maximum or the minimum value of the given problem. Excel Solver is available from Excel 2007 to Excel 365 versions.

Solver is an **Add-in** of Excel. It does not usually exist in Excel. We need to add this tool manually. Have a look at the following process.

**📌 ****Steps:**

- Go to
**File>> OptionsÂ**first. - The
**Excel Options**window appears now. - Select the
**Add-ins**from the left side. - Now, select
**Solver Add-in**from the**Add-ins**on the right side. - Then, click on the
**GoÂ**button.

- The
**Add-ins**window appears. - Check the
**Solver Add-in**from the available add-ins. - Finally, press the
**OKÂ**button.

- We return to the worksheet now.
- Click on the
**DataÂ**tab.

We can see the **Solver** option on the right-most side.

**Algorithm of Excel Solver**

Excel** Solver** has many different algorithms for finding the solution to different complex linear and non-linear problems. We can choose any of the following methods in the Select a Solving Method box.

**GRG Nonlinear:**For the smooth nonlinear problems*Generalized Reduced Gradient Nonlinear*algorithm is used where the data will contain a minimum of one constraint which will be a smooth nonlinear function.**LP Simplex**: The LP Simplex Solving method is used for solving a problem of Linear Programming which is characterized by linear relationships. With the linear relationship among the data, linear programming tends to achieve the desired goal such as minimum loss, maximum profit, etc.**Evolutionary**: It is the most difficult optimization problem where you can not determine the characteristics of the function. The direction of this kind of function can be increasing or decreasing. This is because the functions are either discontinuous or non-smooth.

**Components of Excel Solver Model**

In this section, we will discuss the details of a solver model. A solver model has 3 parts.

**Objective Cell,****Variable Cells,****Constraints.**

**Objective Cell:** The objective cell denotes a cell that contains a formula. This may be minimum, maximum, or a certain value.

**Variable Cells:** This is the range of cells that acts as the variables to solve a problem. We get the solution using the variable.

**Constraints:** Constraints are the conditions set by the user to solve a problem. Those restrictions are fixed by realizing real-world situations.

**Steps to Use Excel Solver Add-In (e.g. Maximizing Profit)**

Now, we will show the use of Excel Solver. We will apply this solver to get the maximum profit within some constraints. Here, we have taken a dataset of a clothing store. Assume, that shop sells only 5 specific kinds of products. In the dataset, the cost, selling price, profit, and available units of each item are given. Also, we calculated the total profit by a simple sum function. But here, we want to get the maximum profit with some conditions. The first one is they will have a maximum of 90% and a minimum of 10 units of each item. A special case for Blazer has a maximum sell unit of 20. And lastly, they will sell a maximum of 400 units of total items. Following those conditions, we want to calculate the maximum profit that will indicate the selling unit of each item specifically.

### 📌 Step 1: Access Solver Parameters Window

- First, click on the
**Data**tab. Then, select the**Solver**option from the**Analyze**group.

- The
**Solver Parameters**window appears.

### 📌 Step 2: Set Objective and Choose Variable Cells

- Choose the Cell where we want to show the calculated value.
- Then, choose the
**MaxÂ**option.

- Now, click on the arrow of the By Changing Variable Cells option.
- Choose the range of the Unit column.

This range is the variable of this problem.

- Then, we will add the constraints. So, click on the
**AddÂ**option.

- The
**Add Constraint**window appears.

### 📌 Step 3: Add Constraints and Choose Solving Method

- Put the
**Cell Reference**, comparing the operator and**Constraint**. - Then, press Add if we want to add a new constraint or
**OK**if there are no more constraints.

- Here, we added all the constraints.
- Also, choose
**GRG Nonlinear**as the solving method. - Finally, press the
**Solve**button.

- The
**Solver Results**window appears.

### 📌 Step 4: Choose Suitable Solver Results Format

- Choose the
**Keep Solver Solution**option, it makes changes in the present worksheet. - Click the
**Answer**from the report section that will print a report of the**Answer**. - Then, check the
**Return to Solver****Parameters Dialog**, it will return to the previous dialog box. If any changes are needed we do that from the dialog box. - Lastly, press the
**OKÂ**button.

- Go back to the worksheet now.

We can see some changes in the unit and total profit cells.

- There is an
**Answer Report**tab added in the Excel file.

In a similar way, we can also get the minimum value like cost minimization by choosing Min from the **Set Objective** section

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**Conclusion**

In this article, we described the details of the Excel Solver, suchÂ as, how to add a solver, solution algorithms, model, and the use with an example. I hope this will satisfy your needs. PleaseÂ give your suggestions in the comment box.

## Related Articles

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

Fabulously SOLVED!

Glad to know, Sandeep!

-Mahdy

ExcelDemy team