The tutorial explains how to add and where to find Solver in different Excel versions, from 2016 to 2003. Step-by-step examples show how to use Excel Solver to find optimal solutions for linear programming and other kinds of problems.
Everyone knows that Microsoft Excel contains a lot of useful functions and powerful tools that can save you hours of calculations. But did you know that it also has a tool that can help you find optimal solutions for decision problems?
In this tutorial, we are going to cover all essential aspects of the Excel Solver add-in and provide a step-by-step guide on how to use it most effectively.
Excel Solver belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models.
The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a linear programming solver. Apart from that, it can handle smooth nonlinear and non-smooth problems. Please see Excel Solver algorithms for more details.
While Solver can't crack every possible problem, it is really helpful when dealing with all kinds of optimization problems where you need to make the best decision. For example, it can help you maximize the return of investment, choose the optimal budget for your advertising campaign, make the best work schedule for your employees, minimize the delivery costs, and so on.
The Solver add-in is included with all versions of Microsoft Excel beginning with 2003, but it is not enabled by default.
To add Solver to your Excel, perform the following steps:
To get Solver on Excel 2003, go to the Tools menu, and click Add-Ins. In the Add-Ins available list, check the Solver Add-in box, and click OK.
Note. If Excel displays a message that the Solver Add-in is not currently installed on your computer, click Yes to install it.
In the modern versions of Excel, the Solver button appears on the Data tab, in the Analysis group:
After the Solver Add-in is loaded to Excel 2003, its command is added to the Tools menu:
Now that you know where to find Solver in Excel, open a new worksheet and let's get started!
Note. The examples discussed in this tutorial use Solver in Excel 2013. If you have another Excel version, the screenshots may not match your version exactly, although the Solver functionality is basically the same.
Before running the Excel Solver add-in, formulate the model you want to solve in a worksheet. In this example, let's find a solution for the following simple optimization problem.
Problem. Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months.
Goal: Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.
For this task, I've created the following model:
And now, let's see how Excel Solver can find a solution for this problem.
On the Data tab, in the Analysis group, click the Solver button.
The Solver Parameters window will open where you have to set up the 3 primary components:
Exactly what does Excel Solver do with the above parameters? It finds the optimal value (maximum, minimum or specified) for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells.
The Objective cell (Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.
In this example, the objective cell is B7, which calculates the payment term using the formula
=B3/(B4*B5) and the result of the formula should be equal to 12:
Variable cells (Changing cells or Adjustable cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.
In this example, we have a couple of cells whose values can be changed:
Tip. If the variable cells or ranges in your model are non-adjacent, select the first cell or range, and then press and hold the Ctrl key while selecting other cells and/or ranges. Or, type the ranges manually, separated with commas.
The Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.
To add a constraint(s), do the following:
Excel Solver allows specifying the following relationships between the referenced cell and the constraint.
Note. The int, bin, and dif relationships can only be used for constraints on Variable cells.
To edit or delete an existing constraint do the following:
In this example, the constraints are:
After you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem.
Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours.
When Solver has finished processing, it will display the Solver Results dialog window, where you select Keep the Solver Solution and click OK:
The Solver Result window will close and the solution will appear on the worksheet right away.
In this example, $66.67 appears in cell B5, which is the minimal cost per service that will let you pay for the new equipment in 12 months, provided there are at least 50 clients per month:
Now that you've got the basic idea of how to use Solver in Excel, let's have a closer look at a couple more examples that might help you gain more understanding.
Below you will find two more examples of using the Excel Solver addin. First, we will find a solution for a well-known puzzle, and then solve a real-life linear programming problem.
I believe everyone is familiar with "magic square" puzzles where you have to put a set of numbers in a square so that all rows, columns and diagonals add up to a certain number.
For instance, do you know a solution for the 3x3 square containing numbers from 1 to 9 where each row, column and diagonal adds up to 15?
It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Our part of the job is to properly define the problem.
To begin with, enter the numbers from 1 to 9 in a table consisting of 3 rows and 3 columns. The Excel Solver does not actually need those numbers, but they will help us visualize the problem. What the Excel Solver add-in really needs are the SUM formulas that total each row, column and 2 diagonals:
With all the formulas in place, run Solver and set up the following parameters:
Finally, click the Solve button, and the solution is there!
This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.
Problem: You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.
Goal: Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.
Here is how our transportation optimization problem looks like:
To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:
The next thing for you to do is to calculate the total quantity shipped from each warehouse (G7:G8), and the total goods received by each customer (B9:E9). You can do this with simple Sum formulas demonstrated in the below screenshot. Also, insert the SUMPRODUCT formula in C12 to calculate the total cost of shipping:
To make our transportation optimization model easier to understand, create the following named ranges:
|Range name||Cells||Solver parameter|
The last thing left for you to do is configure the Excel Solver parameters:
Please pay attention that we've chosen the Simplex LP solving method in this example because we are dealing with the linear programming problem. If you are not sure what kind of problem yours is, you can leave the default GRG Nonlinear solving method. For more information, please see Excel Solver algorithms.
Click the Solve button at the bottom of the Solver Parameters window, and you will get your answer. In this example, the Excel Solver add-in calculated the optimal quantity of goods to deliver to each customer from each warehouse with the minimal total cost of shipping:
When solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later.
For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost. At that, you may want to save the most probable scenario you've already calculated and restore it at any moment.
Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below.
To save the Excel Solver scenario, perform the following steps:
At the same time, the Solver Parameters window will show up where you can change your constraints and try different "what if" options.
When you decide to restore the saved scenario, do the following:
When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:
To change how Solver finds a solution, click the Options button in the Solver Parameters dialog box, and configure any or all options on the GRG Nonlinear, All Methods, and Evolutionary tabs.
This is how you can use Solver in Excel to find the best solutions for your decision problems. And now, you may want to download the Excel Solver examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week.
Table of contents