problem solving in ms excel

Ablebits blog
Financial functions

How to use Solver in Excel with examples

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.

What is Excel Solver?

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.

How to add Solver to Excel

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:

In Excel 2010 - Excel 365, click File > Options . In Excel 2007, click the Microsoft Office button, and then click Excel Options .

Open the Excel Options dialog to get to the Excel Add-ins list.

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 .

Where is Solver in Excel?

Where is Solver in Excel 2003?

Now that you know where to find Solver in Excel, open a new worksheet and let's get started!

How to use Solver in Excel

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.

And now, let's see how Excel Solver can find a solution for this problem.

1. Run Excel Solver

2. define the problem.

The Solver Parameters window will open where you have to set up the 3 primary components:

Objective cell

Variable cells

Constraints.

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.

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:

Projected clients per month (B4) that should be less than or equal to 50; and
Cost per service (B5) that we want Excel Solver to calculate.

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:

Click the Add button right to the " Subject to the Constraints " box.

In the Constraint window, enter a constraint.
Click the Add button to add the constraint to the list.

Click the Add button to add the constraint to the list.

Continue entering other constraints.
After you have entered the final constraint, click OK to return to the main Solver Parameters window.

Excel Solver allows specifying the following relationships between the referenced cell and the constraint.

Less than or equal to , equal to , and greater than or equal to . You set these relationships by selecting a cell in the Cell Reference box, choosing one of the following signs: <= , =, or >= , and then typing a number, cell reference / cell name, or formula in the Constraint box (please see the above screenshot).
Integer . If the referenced cell must be an integer, select int , and the word integer will appear in the Constraint box.
Different values . If each cell in the referenced range must contain a different value, select dif , and the word AllDifferent will appear in the Constraint box.
Binary . If you want to limit a referenced cell either to 0 or 1, select bin , and the word binary will appear in the Constraint box.

To edit or delete an existing constraint do the following:

In the Solver Parameters dialog box, click the constraint.
To modify the selected constraint, click Change and make the changes you want.
To delete the constraint, click the Delete button.

In this example, the constraints are:

B3=40000 - cost of the new equipment is $40,000.
B4<=50 - the number of projected patients per month in under 50.

3. Solve the problem

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.

The Solver Result window will close and the solution will appear on the worksheet right away.

If the Excel Solver has been processing a certain problem for too long, you can interrupt the process by pressing the Esc key. Excel will recalculate the worksheet with the last values found for the Variable cells.
To get more details about the solved problem, click a report type in the Reports box, and then click OK . The report will be created on a new worksheet:

Excel Solver examples

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.

Excel Solver example 1 (magic square)

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.

With all the formulas in place, run Solver and set up the following parameters:

Set Objective . In this example, we don't need to set any objective, so leave this box empty.
Variable Cells . We want to populate numbers in cells B2 to D4, so select the range B2:D4.
$B$2:$D$4 = AllDifferent - all of the Variable cells should contain different values.
$B$2:$D$4 = integer - all of the Variable cells should be integers.
$B$5:$D$5 = 15 - the sum of values in each column should equal 15.
$E$2:$E$4 = 15 - the sum of values in each row should equal 15.
$B$7:$B$8 = 15 - the sum of both diagonals should equal 15.

Excel Solver example 2 (linear programming problem)

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.

Source data

Formulating the model

To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:

What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are Variable cells (B7:E8).
What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are Constrained cells .
What is the goal? The minimal total cost of shipping. And this is our Objective cell (C12).

Formulating the model using Excel formulas

To make our transportation optimization model easier to understand, create the following named ranges:

The last thing left for you to do is configure the Excel Solver parameters:

Objective: Shipping_cost set to Min
Variable cells: Products_shipped
Constraints: Total_received = Ordered and Total_shipped <= Available

How to save and load Excel Solver scenarios

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.

Saving the model

To save the Excel Solver scenario, perform the following steps:

Open the worksheet with the calculated model and run the Excel Solver.

Excel will save your current model, which may look something similar to this:

The current Excel Solver scenario is saved.

Loading the saved model

When you decide to restore the saved scenario, do the following:

In the Solver Parameters window, click the Load/Save button.

Select the range of cells containing the saved model and click Load.

This will open the main Excel Solver window with the parameters of the previously saved model. All you need to do is to click the Solve button to re-calculate it.

Excel Solver algorithms

When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:

GRG Nonlinear. Generalized Reduced Gradient Nonlinear algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables. More details can be found here .
LP Simplex . The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig. It is used for solving so called Linear Programming problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized. For more information, please check out this page .
Evolutionary . It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing. For more information, please see this page .

This is how you can use Solver in Excel to find the best solutions for your decision problems. At the end of this post, you can download the sample workbook with all the 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.

Practice workbook for download

How to Use Microsoft Excel Solver – A Comprehensive Guide

Microsoft Excel Solver is a powerful tool that can help you solve complex problems by finding the optimal solution. It’s like having a personal assistant that can crunch numbers and find the best answer to your question. Whether you’re trying to figure out the most cost-effective way to ship products or how to allocate resources in a project, Solver can help. All you need is a basic understanding of Excel and a clear idea of the problem you want to solve.

Step by Step Tutorial: How to Use Microsoft Excel Solver

Before we dive into the steps, let’s briefly talk about what we’re going to achieve. We’ll set up a problem in Excel, define our objective, set constraints, and let Solver find the best solution. Ready? Let’s go!

Step 1: Open Solver

Open Solver from the Data tab in Excel.

Solver is not displayed by default in Excel. If you don’t see it, you may need to enable it from the ‘Add-ins’ options.

Step 2: Set Your Objective

Select the cell with the formula you want to optimize.

This is what Solver will work towards improving, whether it’s maximizing profit, minimizing cost, or achieving a specific value.

Step 3: Define the Variable Cells

Choose the cells that Solver can change to reach the objective.

These are usually the inputs or decision variables of your problem that you’re willing to adjust.

Step 4: Add Constraints

Click ‘Add’ to set constraints for the variable cells.

Constraints are the rules that your solution must follow, like not exceeding a budget or ensuring a minimum production quantity.

Step 5: Solve the Problem

Press the ‘Solve’ button and let Solver do its magic.

Solver will run various calculations to find the optimal solution within your set constraints.

After completing these steps, Solver will present you with a solution. If it finds an optimal solution, it will ask if you want to keep the new values or restore the original ones. If no solution is found, it will let you know that as well.

Tips for Using Microsoft Excel Solver

Always start with a clear understanding of the problem you want to solve.
Make sure your objective function and constraints are correctly set up in Excel.
Use the ‘Evolutionary’ solving method for problems that are not linear.
If Solver is taking too long to find a solution, try simplifying the problem or constraints.
Remember to save your work before running Solver, just in case.

Frequently Asked Questions

What is microsoft excel solver used for.

Solver is used to find the optimal solution for complex problems in Excel, such as resource allocation, budgeting, and scheduling.

Can Solver handle non-linear problems?

Yes, by using the ‘Evolutionary’ solving method, Solver can handle non-linear problems.

Is Solver available in all versions of Excel?

Solver is available in most versions of Excel, but it might need to be enabled from the ‘Add-ins’ menu.

What do I do if Solver can’t find a solution?

Try checking your constraints and objective function for errors, or consider simplifying the problem.

Can Solver handle multiple objectives?

No, Solver is designed to handle one objective at a time. However, you can run Solver multiple times with different objectives.

Open Solver from the Data tab.
Set your objective by selecting the target cell.
Define the variable cells.
Add constraints for the variable cells.
Solve the problem.

In essence, Microsoft Excel Solver is like a Swiss Army knife for problem-solving. It’s an indispensable tool that can take your Excel skills to a whole new level, helping you make data-driven decisions efficiently. With the step-by-step tutorial you’ve just gone through, you should now feel confident in tackling problems that seemed daunting before. Remember, like any tool, practice makes perfect. So, why not challenge yourself with a real-world problem you’ve been wrestling with and let Solver come to the rescue? And if you ever find yourself in a tough spot, just come back to this article for a quick refresher on how to use Microsoft Excel Solver. Happy solving!

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Read his full bio here.

How to Use Solver in Microsoft Excel

Last Updated: July 28, 2022 Tested

Enabling Solver

Analyzing and solving.

This article was co-authored by wikiHow staff writer, Jack Lloyd . Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. The wikiHow Tech Team also followed the article's instructions and verified that they work. This article has been viewed 616,209 times. Learn more...

This wikiHow teaches you how to use Microsoft Excel's Solver tool, which allows you to alter different variables in a spreadsheet in order to achieve a desired solution. You can use Solver in both Windows and Mac versions of Excel, though you'll have to enable Solver before you can begin using it.

Solver comes pre-installed with both Windows and Mac versions of Excel, but you'll have to enable it manually.

If you have an existing Excel file you'd like to use Solver with, you can open it instead of creating a new file.

On a Mac, click Tools instead, then skip the next step.

On a Mac, click Excel Add-ins in the Tools menu.

On a Mac, this window will open after clicking Excel Add-ins in the Tools menu.

For example, you might create a spreadsheet documenting your various expenses over the course of a month with the output cell resulting in your money left over.
You can't use solver on a spreadsheet which doesn't have solvable data (i.e., your data has to have equations).

For example, if you're creating a budget where the end goal is your monthly income, you would click the final "Income" cell.

For example, if your goal is to have $200 at the end of the month, you would type 200 into the text box.
You can also check either the "Max" or "Min" box in order to prompt Solver to determine the absolute maximum or minimum value.
Once you've set a goal, Solver will attempt to meet that goal by adjusting other variables in your spreadsheet.

Click the cell (or select the cells) for which the constraint applies.
Select a type of constraint from the middle drop-down menu.
Enter the constraint's number (e.g., a maximum or minimum).

If you do like your Solver's results, you can apply them to your spreadsheet by checking the "Keep Solver Solution" box and then clicking OK .

Expert Q&A

Solver is best used for problems such as scheduling employees, determining the lowest price for which you can sell items while meeting a financial goal, and budgeting. Thanks Helpful 1 Not Helpful 0

Solver cannot be used in spreadsheets in which there is no "output" or actual solution. For example, you can't apply solver to a spreadsheet which has no equations. Thanks Helpful 1 Not Helpful 1

↑ https://support.office.com/en-us/article/Load-the-Solver-Add-in-in-Excel-612926fc-d53b-46b4-872c-e24772f078ca#OfficeVersion=Windows
↑ https://www.youtube.com/watch?v=dRm5MEoA3OI

About This Article

1. Enable Solver in the "Add-ins" section of your Excel preferences if necessary. 2. Open a spreadsheet with data you want to analyze. 3. Click Data , then click Solver . 4. Select a cell to use from the "Set Objective" field. 5. Check the "Value Of" box, then enter a desired value. 6. Click Solve . Did this summary help you? Yes No

Send fan mail to authors

Is this article up to date?

Featured Articles

Watch Articles

Terms of Use
Privacy Policy
Do Not Sell or Share My Info
Not Selling Info

wikiHow Tech Help Pro:

Level up your tech skills and stay ahead of the curve

#1 Excel tutorial on the net

Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model

Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Load the Solver Add-in

To load the solver add-in, execute the following steps.

1. On the File tab, click Options.

2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

4. You can find the Solver on the Data tab, in the Analyze group.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution , execute the following steps.

1. On the Data tab, in the Analyze group, click Solver .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter OrderSize for the Changing Variable Cells.

5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available. Try it yourself. Download the Excel file and find the optimal solution.

1/8 Completed! Learn much more about the solver > Go to Next Chapter: Analysis ToolPak

Learn more, it's easy

Transportation Problem
Assignment Problem
Shortest Path Problem
Maximum Flow Problem
Capital Investment
Sensitivity Analysis
System of Linear Equations

Download Excel File

solver.xlsx

Next Chapter

Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

300 Examples

IMAGES

Assignment Problem solving by using Excel Solver
Solve the Problem Excel
Excel problem
Approaching problem-solving in Ms Excel
How to Use Solver in Excel: A Step-by-Step Guide with Examples
Solving Business Problems Excel Solver

VIDEO

Solving LP Problem using EXCEL
Businsess Problem Solving
computer ms-Excel question solving#computer #solving #short
computer 💻 MS Excel question solving#shorts #viral #computer #msexcel #shortvideo#education
MS Excel problem solution
Solving Excel’s BIGGEST Problem

COMMENTS

Excel Solver tutorial with step-by-step examples
To add Solver to your Excel, perform the following steps: In Excel 2010 - Excel 365, click File > Options. In Excel 2007, click the Microsoft Office button, and then click Excel Options. In the Excel Options dialog, click Add-Ins on the left sidebar, make sure Excel Add-ins is selected in the Manage box at the bottom of the window, and click Go ...
How to Use Microsoft Excel Solver
Step 5: Solve the Problem. Press the ‘Solve’ button and let Solver do its magic. Solver will run various calculations to find the optimal solution within your set constraints. After completing these steps, Solver will present you with a solution. If it finds an optimal solution, it will ask if you want to keep the new values or restore the ...
How to Use Solver in Microsoft Excel (with Pictures)
The Excel Solver is an add-in tool in Microsoft Excel that helps you find an optimal solution for complex problems involving mathematical equations and constraints. It is primarily used for optimization and solving linear programming problems. Below we are describing definitions for the terms: Objective Cell: It is simply a single cell with ...
Solver in Excel (In Easy Steps)
To load the solver add-in, execute the following steps. 1. On the File tab, click Options. 2. Under Add-ins, select Solver Add-in and click on the Go button. 3. Check Solver Add-in and click OK. 4. You can find the Solver on the Data tab, in the Analyze group.