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

**Table of Contents**hide

**Download Practice Workbook**

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

**How to Add 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 problemsalgorithm is used where the data will contain a minimum of one constraint which will be a smooth nonlinear function.*Generalized Reduced Gradient Nonlinear***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 nonsmooth.

**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 the 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. There is a special case for **Blazer **that 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 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.

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

**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 have a look at our website **Exceldemy.com** and give your suggestions in the comment box.