**How to Load and Use Excel’s**

“Solver” Add-In (Fast & Easy)

**How to Load and Use Excel’s**

“Solver” Add-In (Fast & Easy)

“Solver” Add-In (Fast & Easy)

*Written by co-founder Kasper Langmann, Microsoft Office Specialist.*

Excel’s ‘Solver’ add-in is a powerful tool for **what-if analysis**, **optimization**, and **equation solving**.

But truth be told: *knowing how to use the ‘Solver’ tool doesn’t come naturally*.

Unlike some of the tools, ‘Solver’ isn’t that easy to operate.

That’s why in this tutorial, we’ll walk you through the

steps in using Excel’s ‘Solver’ add-in. We’ll have anExcel ‘Solver’ example problemso you’ll have a better grasp of it by the end of this article.

Rest assured, we’ll make learning easy for you. All you have to do is follow the steps defined below and you’re set. 😊

**What is the Excel ‘Solver’ add-in?**

The ‘Solver’ add-in is an **Excel optimization and equation solving tool **commonly used in *solving various business, programming, and engineering problems*.

In simple terms, you can use this tool to

determine the best value (maximum or minimum) of a cell by changing other cells with strict consideration to set constraints and limits.

‘Solver’ is also known as *“Linear Programming Solver” *because of its use in *solving linear programming problems*. In addition, this tool can also solve *smooth non-linear *and *non-smooth problems*.

Of course, the tool isn’t perfect. It can’t solve all possible problems.

However, when it comes to optimization-related scenarios, the Excel ‘Solver’ tool is one of the best tools you can use.

**How to load the ‘Solver’ add-in?**

The same with Excel’s Analysis ToolPak, you have to load the tool before you can use it.

Start by **clicking ***‘File’*** from the tab list**.

Then, **click ***‘Options’ ***at the bottom of the left-hand sidebar**.

On the **‘Add-ins’ **category, **click ***‘Solver Add-in’ ***under the **** ‘Inactive Application Add-ins’**.

Then, **click the ***‘Go’ ***box**.

After that, the ‘Add-ins’ window will open.

**Check the ***‘Solver Add-in’ ***option **and **click **** ‘OK’**.

To check if the tool is live, **click ***‘Data’ ***from the tab list**.

On the rightmost side of the Ribbon, you’ll notice a new group called **‘Analyze’**.

**Click **** ‘Solver’ **to open the tool.

**How to use the ‘Solver’?**

To be able to use ‘Solver’ more efficiently, let’s have an orientation first about the different parts and sections of the ‘Solver’ window:

- Set Objective
- By Changing Variable Cells
- Subject to the Constraints
- Select a Solving Method

**Set Objective**

This is where you refer to the cell which contains **the formula of the objective or goal **of the set. In earlier Excel versions, the objective cell is also known as the **‘Target’ cell**.

You can set the objective into three (3) functions:

- Max
- Min
- Value Of

**By Changing Variable Cells**

Variable cells refer to the cells that can be **changed to complete the objective**. Previously, they’re known as **‘Changing’ **or **‘Adjustable’ **cells. As of now, Excel allows only up to *200 variable cells*.

**Subject to the Constraints**

Excel ‘Solver’ constraints are the **restrictions, limits, or conditions you set **that must be met in solving the problem.

Once you **click the ***‘Add’ ***button**, another window pops up where you can enter the **‘Cell Reference’**, the **‘Constraint’**, as well as their **relationship **towards each other.

There are six (6) types of relationship you can set:

- Less than or equal to
- Equal to
- More than or equal to
- Int (integer; the referenced cell should be an integer)
- Bin (binary; the referenced cell should only be 0 or 1)
- Dif (different; the referenced cell or cells should have different values)

**Select a Solving Method**

Excel lets you choose from three (3) solving methods or algorithms:

**1. GRG Nonlinear**

The Generalized Reduced Gradient Nonlinear method solves *smooth nonlinear problems*.

**2. Simplex LP**

Simplex LP solves *linear programming problems*.

**3. Evolutionary**

The ‘Evolutionary’ method solves* hard optimization problems*. If the problem has non-smooth or discontinuous functions, making it hard to figure out if the direction of the function is increasing or decreasing, use this method.

**Problem: Maximizing Total Profit**

Now that you’re familiar with them, let’s try and use them on a problem:

*Assuming you have this business where you have to decide how many units to order to maximize the total profit.*

Here’s your data set:

First, let’s define the problem by answering three (3) questions:

**What decisions are to be made?**

The decision we have to make in this problem is to determine how many units to order to maximize the total profit given the information and constraints.

The cells containing these kinds of information are the variable cells, **B3:D3**.

**What are the constraints?**

The constraints are the following:

- The overall capital is $1,000. Naturally, our spend shouldn’t exceed that amount.
**(E5 <= G5)** - Also, our inventory space is only 1,100. Given that, the units ordered shouldn’t exceed that amount.
**(E6 <= G6)**

**What is the goal?**

The goal is to maximize the total profit which is in B8.

Now, let’s define the formulas we’re using.

- The capital spent
**(E5)**was taken from the sumproduct of ‘Units to order’**(B3:D3)**and ‘Unit cost’**(B5:D5).** - The inventory space spent
**(E6)**was from the sumproduct of ‘Units to order’**(B3:D3)**and ‘Inventory Space’**(B6:D6).** - The ‘Total Profit’ was the sumproduct of ‘Units to order’
**(B3:D3)**and ‘Unit cost’**(B4:D4)**.

This time, let’s solve the problem.

Here are the parameters:

- Set Objective:
**B8** - By Changing Variable Cells:
**B3:D3** - Subject to the Constraints:
**E5 <= G5, E6 <= G6, B3:D3 = integer**

The ‘Units to order’ is constrained to **‘integer’ **only so we would be able to *get whole numbers *since, of course, you can’t order half or a fourth of a unit. 😊

Also, set the solving method to **Simplex LP **since we’re dealing with a *linear programming problem*.

Once you have set the correct parameters, **click the ***‘Solve’ ***button **at the bottom of the window.

After the tool solves the problem, there’s a **‘Solver Results’ **window that opens and asks you if you would either **‘Keep Solver Solution’ **or **‘Restore Original Values’**.

**Hit **** ‘OK’ **if you like to keep the solution.

To maximize your profit, you have to *order 333 units of ‘Item 3’ and a unit of ‘Item 2’*.

**Conclusion**

Learning how to use the ‘Solver’ would be a great time saver and could save you from a lot of headaches.

Don’t worry too much if you’re having difficulty with it at first. With practice, you’ll be able to master it in no time. 😊