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

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.
Go to the file tab in Excel.
  1. Select options at the bottom of the left-hand sidebar.
Select options from the file tab.
  1. Select Add-ins from the Excel options window.
Select Add-ins from Excel options.
  1. Then in the Manage box, select excel add-ins and click “Go…”.
  1. Select the Solver Add-in check box from the Add-ins available box. Then, click OK.
Select the Solver Add-in check box from the Add-ins available box.

Now the Excel Solver Add-ins are loaded to Microsoft excel πŸ‘Œ

Now, go to the Data tab.

To check view the Excel Solver button - Go to the data tab in the Excel file.

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

Excel Solver button in the analysis group of the Data tab.

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

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

The objective cell must have a formula.

Kasper Langmann, Microsoft Office Specialist

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.
Solver parameters dialog box - Set objectives

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.

Solver parameters window - By changing variable cells

Subject to the constraints

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

We can add constraints in this constraint box.

Solver parameters window - Subject to constraints

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.

Do you want to delete or change an existing constraint? πŸ‘‡πŸ»

Select that constraint from the Excel Solver parameters dialog box.

Then click the change button to change the existing constraint.

Or click the delete button to delete that.

Kasper Langmann, Microsoft Office Specialist

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.
Excel Solver parameters - Solving methods

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.

Data set - Excel solver example

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.

Excel Solver - Setting objective cell reference to value of $900
  1. 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.

Excel Solver - Selecting variable cells.
  1. 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.

Excel Solver - Adding maximum amounts as constraints
Excel Solver - Set the quantity as an integer.

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

Solver displays all constraints for possible solutions
  1. Select the “Simplex LP” as we are solving a linear solver problem and click the “Solve” button at the bottom of the window.
Excel Solver - Selecting the solving method
  1. 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”.

Excel Solver - Last step

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

Excel Solver - Results

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

When you need to change only one input to get the desired outcome of a formula, you can easily use Excel’s inbuilt feature Goal Seek.

We have to specify the formula cell, the target value for the formula cell, and the cell to change in order to get the target value.

Only 200 decision variables are currently supported by Excel solver.

This restriction applies to both linear and nonlinear models.

Another problem is that we cannot ensure that we will obtain the solver model’s optimal solution if it is not linear.