How to Load and Use Excel’s
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 an Excel ‘Solver’ example problem so you’ll have a better grasp of it by the end of this article.

Kasper Langmann, Co-founder of Spreadsheeto

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

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

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.

Kasper Langmann, Co-founder of Spreadsheeto

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

file from the tab list

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

select options from the file tab

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

Then, click the ‘Go’ box.

the solver add-in on Excel's options

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

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

how to load the solver add-in

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

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.

solver icon on analyze group

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
solver default parameters

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.

cell reference, relationship, and constraint on the add constraint window

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:

data over for excel solver problem

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).
using sumproduct on the solver problem

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.

correct solver parameters for the 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’.

keep solver solution on solver results window

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

using the solver to solve a problem

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

Kasper Langmann, Co-founder of Spreadsheeto