Full Guide to Solver in Excel: How to Use + Install (2024)

We make many assumptions when creating financial models in Microsoft Excel.

These assumptions also have limitations 🚧

If you want to find optimal solutions from a model, you must change these assumptions.

It will take hours to get the answer if you try to change these assumptions manually ⏳

That’s where the Excel Solver feature comes in handy!

In this lesson, I will explain this complex Excel feature in a very easy manner.

You can download the workbook and follow along with me.

What is the Solver add-in?

The Solver add-in is an Excel optimization tool.

You can use the Excel solver to perform a what-if analysis with multiple variables in your model.

It will find an optimal solution for a formula by changing the related variables.

We can add constraints on how much one variable can vary when we change the others.

The solver is useful for Linear programming problems in business, programming, and engineering.

Because of this, Solver is also known as a “Linear Programming Solver.”

However, you can solve both non-smooth or continuous functions as well as smooth non-linear issues with the Excel Solver.

How to add the Solver add-in

The Solver add-in, like the Analysis ToolPak of Excel, is a Microsoft Excel add-in program.

As a result, it is not immediately available in Excel by default.

We must install it 🤔

Let’s look at how to add the Solver add-in to our Excel workbook.

1. Go to the file tab in your Excel file.

2. Select options at the bottom of the left-hand sidebar.

3. Select Add-ins from the Excel options window.

4. Then in the Manage box, select excel add-ins and click “Go…”.

5. Select the Solver Add-in check box from the Add-ins available box. Then, click OK.

Now the Excel Solver Add-ins are loaded to Microsoft excel 👌

Now, go to the Data tab.

You can see the Excel solver command in the analysis group of the Data tab.

You can click Solver to open the Solver parameters dialog box.

How to use Solver in Excel

It is important to learn different parts of the Solver parameter dialog box before we use it 🧑🏻‍🎓

Set Objective

The set objective is the first input for the Excel Solver parameter dialog box.

In the set objective box, we have to select the cell reference for the objective cell.

Then you can set the objective cell to one of the following.

• Max
• Min
• Value of – If you select “Value of”, you must specify the target value in the box.

By changing variable cells

In this “by changing variable cells”, you have to select all the variable cells to get the optimal solution.

The variable cells are directly or indirectly related to the target cell.

Currently, you can include up to 200 variable cells for the Excel solver function.

If the variable cells are non-adjacent cells, you have to separate them with commas.

Subject to the constraints

Excel Solver can find the optimal solution within constraints or limits.

We can add constraints in this constraint box.

To add constraints, click add button.

Then “Add constraint” box will pop up.

You can select the variable cell for the cell reference box and add the limit in the constraint box.

You must specify the relationship between the cell reference box and the constraint box also.

Select a solving method

In Excel solver, we can select one of the solving methods from the following 3.

• GRG non-linear – For solver problems that are smooth nonlinear.
• Simplex LP – For linear programming problems.
• Evolutionary – For solver problems that are non-smooth.

Now you are familiar with the Excel solver add-in options window 💪🏻

Let’s use Excel Solver to find a solution for a linear programming model.

Linear programming with Solver add-in

In the below example, we have to find how many units we have to sell in order to get the desired outcome of profit.

Let’s learn step by step how to use the Excel Solver solution to our linear programming problem.

1. Go to the data tab and click solver to open the Excel Solver.
2. Select the objective cell reference.

Our goal is to set the total profit in cell B7 to $900.

So, B7 is the objective cell.

As we need to match the value of the objective cell to $900, we have to select the option of “Value of”.

Then type 900, next to the “value of” option.

3. Select the variable cells.

The variable for the target value is the quantity for each cell.

So, in this case, the variable cells are B2, C2, and D2.

4. Click the “Add” tab to add constraints for each variable.

Choose each product’s quantity cell as a cell reference.

Then choose the referenced cell’s maximum amount as the constraint.

Next, choose the link between the referred cell.

Here we select “less than or equal sign” because the constraint is maximum quantity.

Again, we need the quantities as whole numbers.

So, we set another constraint for each variable cell as an Integer.

After entering all constraints, the solver displays all constraints for the particular solution.

5. Select the “Simplex LP” as we are solving a linear solver problem and click the “Solve” button at the bottom of the window.

6. Then, you can see the Solver results dialog box with options to “keep solver solution” and to “restore original values”.

Select the “keep solver solution” and click “OK”.

To get a total profit of $900, we have to sell 149 in vanilla flavour, 114 in chocolate flavour, and 75 in banana flavour.

That’s it – Now what?

Now you have learned Excel Solver a hidden gem of an Excel 💎

You can use this to solve many problems in financial models.

But, before you use Excel Solver for your model, you must make sure all your functions such as IF, SUMIF, and VLOOKUP are correctly applied in the model.

Otherwise, the Solver will take hours to get the solution and may give incorrect results.

Click here to access my free 30-minute online course where you can learn about IF, SUMIF, and VLOOKUP if you haven’t already.

Other resources

If you just want to find out the input value of one cell to get the desired result for the output, use Goal Seek.

And please read our articles on the 7 Best Excel Add-Ins, and How To Add The Analysis ToolPak in Excel to learn more about add-ins.

Frequently asked questions