Solver in Excel

What Is Solver In Excel?

Solver in Excel is a special tool with flexible options and features to solve linear and non-linear programming problems. So, solver in excel is also called ‘Linear Programming Solver.’ Solver helps users change the values of multiple variables and provides desired data based on the specified criteria and conditions.

Before learning about solver, consider the below table that shows two variables, X and Y, in column A. The total of the two variables must be equal to 100. Also, the variables must satisfy the two conditions.

The value of the X variable should be greater than or equal to 60 (>= 60)
And, the value of the Y variable should be lesser than or equal to 30 (<=30)

So, we need to use the following steps to find the values of the two variables using solver in excel.

First, type the SUM function in cell B4.
Next, select the Solver option from the Analyze group.
Finally, click OK to see the values of the two variables.

What Is Solver In Excel?

Solver is a special Excel tool with many features used to solve linear and non-linear programming problems.
The Solver in Excel is hidden by default. We can add it using Home -> Options -> Add-ins -> Solver Add-ins.
One should first create a problem model to apply solver in Excel.
The conditions in solver in excel can be greater than or equal to (>=), lesser than or equal to (<=), equal to (=).
One should know statistical methods like GRG Nonlinear, Evolutionary, and Simplex LP concepts to apply solver in excel while solving related problems.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How To Add Solver In Excel?

The Solver feature was introduced in Excel in the year 1991. Even though it is a built-in tool by default, it is always hidden.

The steps to enable or add Solver in Excel are as follows:

Go to the File tab.
Click on Options.
The Excel Options window appears.

Select Add-ins.
Choose Solver Add-in from the Add-ins tab.
Click on Go.
The Add-ins window pops up.
Select Solver Add-in option from the Add-ins available: dialog box.
Click OK.
We can see Solver option under the Analyze group in the Data tab.

Similarly, we can enable or add solver in excel.

Please Note: After we click OK, excel may hang for 5 seconds. Meanwhile, the application takes this time to add Solver option under the Data tab.

Excel Solver Terminologies

Let us understand the terminologies used while working with solver in excel with the following steps.

Go to the Data tab.
Choose the Solver option.
The window, Solver Parameters appears.

Terminologies of Solver Parameter window

Let us learn all the six terminologies to use solver in excel function effectively.

Set Objective: We should select the cell reference where we want to display the required value in this dialog box.

To: This feature has three options: Max, Min, or Value Of.

Max: Select when the maximum value is 100.
Min: Choose when the maximum value is lesser or equal to 80.
Value Of: Click to find exact values which is equal to the required value.

By Changing Variable Cells: In this dialog box, we should add the excel cell references where we wish to display the obtained values in excel.

Add: Once the variable cells are selected, we can add constraints or conditions to those variables using the add button.

Subject to the Constraints: It will display all the conditions applied to the changing variable cells.

Select a Solving Method: In this dialog box, we can choose the type of method to solve the problem. Therefore, we can choose any of the three methods (GRN Nonlinear, Simplex LP, and Evolutionary).

Types Of Solving Method In Solver

The solver in excel helps users solve excel problems in three methods. They are:

GRG Nonlinear: This method stands for Generalized Reduced Gradient Nonlinear algorithm. We can select this method to solve smooth non-linear problems in excel.
Simplex LP: This method is used to solve linear problems in excel.
Evolutionary: We can select this method to solve non-smooth problems.

How To Use Solver In Excel?

Let us understand the method of using solver in excel with an example.

Consider the below table with variables in column A and values in column B. The total of the two variables must be equal to 550 and be displayed in cell B4. In addition, the two variables must satisfy two conditions.

They are:

Value of the variable A should be greater than or equal to 250 (>= 250)
Value of the variable B should be greater than or equal to 300 (>=300)

We need to use the following steps to find the values of the two variables using solver in Excel.

The steps to enable or add solver in Excel are as follows:

Step 1: First, enter the SUM excel function in cell B4.

Then, add the values of the two variables.

Step 2: Next, select the Solver option under the Data tab.

Step 3: In some time, the Solver Parameters dialog box appears.

Here, we should select the cell reference where we want to display the required value in the Set Objective: dialog box.

So, in this example, select the cell reference (B4) in the Set Objective: dialog box.

Step 4: In the To: section, we can select any required option. Since we want to display the exact value (550 in cell B4), we should choose Value of: and enter 550 in the dialog box.

Step 5: Then, in the By Changing Variable Cells: dialog box, we should enter the cell references where we wish to display the results obtained using solver in excel.

Therefore, we should select cells B2 and B3 in By Changing Variable Cells: dialog box.

Step 6: Now, we need to add the conditions (criteria) in the Subject to the Constraints dialog box. In this example, we have two conditions. So, click on the Add button.

Step 7: The Add Constraint window pops up.

Here, choose cell B2 in the Cell Reference: box and set the constraint as >= 250.

Step 8: After entering the first variable constraint, click on the Add option to add additional restraints.

So, choose cell B3 and set the constraint as >= 300

Click OK.

Step 9: Again, the Solver Parameters window appears with the added constraints.

Click on the Solve button.

Clearly, we can see that Excel has calculated the values of the variables such that the sum of the values is equal to 550, automatically.

Please Note: Once we click on Solve, excel may hang for 5 seconds since the application takes this time to calculate the values.

Then, the Solver Results window pops up. We can select Keep Solver Solution if there are no changes or Restore Original Values if there are changes.

Click OK to close the window.

Thus, we can find values using solver in Excel.

Solver Excel Examples

Let us look at the following examples to understand solver in Excel.

Example 1: Find The Best Selling Price To Achieve The Targeted Sales

Mr. Smith is a pen manufacturer. His factory has a capacity of producing 300 pens per day, and his daily expenses are $900. He wants to make a profit of $300 per day. So, he has to generate a revenue of $1,200 to achieve his goals.

In the table,

Column A shows the particulars
Column B shows the values
Cell B5 displays the total cost derived by multiplying cells B2*B3
Cell B6 displays the total profit derived by subtracting Total sales and cost, i.e., B5 – B4

Therefore, Mr. Smith needs to find the ideal selling price in cell B3 to get the expected total sales ($1,200).

The conditions are:

The number of pens should be <=300
The selling price should be <=10

We need to use the following steps to find values using solver in excel.

Step 1: To begin with, click on the Solver option from the Data tab.

Step 2: The Solver Parameters dialog box pops up.

Here, we should select the cell reference where we want to display the required value in the Set Objective: dialog box.

So, in this example, select the cell reference (B5) in the Set Objective: dialog box.

Step 3: Similarly, in the To: section, we can select the required option.

In this example, we use solver in excel to find the exact value (1200). So, let us select Value of and enter 1200 in the dialog box.

Step 4: In addition, we need to change the no. of pens and selling price to obtain the total sales of $1200.

So, we should select cells B2 and B3 in By Changing Variable Cells: dialog box.

Step 5: Also, we need to add the conditions (criteria) in the Subject to the Constraints dialog box. In this example, we have two conditions.

Cell B2 = Number of pens should be <=300 units
Cell B3 = Selling price should be <=10 per pen.

So, click on the Add button and enter the constraints in the Add Constraint window.

The added constraints appear in the Subject to the Constraints: dialog box.

Step 6: Finally, click on Solve.

The Solver in Excel returns the value, which is the optimum combination of the number of pens and selling price.

The values clearly show that the selling price of one pen should be $4. With this price, Mr. Smith can achieve a total profit of $1,200 with a targeted profit of $300 per day.

Thus, we can use solver in excel to find the best selling price to achieve the targeted sales.

Example #2: Create Magic Square By Using Solver

Now, we will try to create a magic 3×3 square puzzle where all the numbers will be different in all the cells. Also, the total should be equal to 15 (when calculated horizontally and vertically).

The conditions are:

Cells B2:D4 = all the numbers should be different.
Cells B5:D5 = Sum (Total) should be equal to 15.
Cells B2:E4 = Sum (Total) should be equal to 15.

We need to use the following steps to find values using solver in excel.

Step 1: First, select the Solver option from the Analyze group in the Data tab .

Step 2: The dialog box, Solver Parameters pops up.

Since, we are not setting any cell value in this example, we have to ignore the Set Objective dialog box.

Step 3: Next, choose the cell range B2:B4 in By Changing Variable Cells: dialog box as we are trying to change the values in that cell range.

Step 4: Now, we need to add criteria to obtain the matrix value 15. So, click on the Add button and add the criteria for cell B2:D4 in the Add Constraint window.

Step 5: Similarly, add the constraint in the Change Constraint window. Choose B5:D5 in the Cell Reference dialog box and type 15 in the Constraint box.

Step 6: Again, click on Add. Choose E2:E4 in the Cell Reference dialog box and type 15 in the Constraint box.

Step 7: Click OK. The Solver Parameters window appears with the added constraints in the Subject to the Constraints dialog box.

Step 8: Select Solve.

We can see that Excel has automatically calculated the values of the variables.

Meanwhile, Excel may hang for 10 – 15 seconds as soon as we select the Solve option. The application takes this time to calculate the values.

From the table, we can see that the values are different.

But, the total of the values (both horizontally and vertically) are equal to 15.

Thus, users can easily find values using solver in excel.

Applications Of Excel Solver

Excel Solver helps solve linear programming problem statements. Apart from this, we use solver in excel to solve:

Business Problems:

Stakeholders have to make important decisions in business. In addition, analysts are required to do predictive analytics. In such times, they can effectively use the solver in Excel. It eases their work by solving the problems based on the given criteria or constraints.

Personal Life Goals:

We can use solver model in excel even in our personal lives. If we plan to get a house loan, we can calculate the affordable EMI amount based on our earnings effectively.

Likewise, we can utilize the solver in Excel to solve complicated problems in our business and personal lives.

Important Things To Note

The Solver in Excel can be grouped under What-if Analysis.
It is mostly used in the investment and business sectors.
It is available in the Data tab -> Analyze.
The conditions (criteria) can be entered in the To: dialog box. It has three options.
We should enter a maximum value of 100 in Max: and in the Min: dialog box, the maximum value that can be entered is 80.
The Value Of box is used to find values equal to the required value.

Frequently Asked Questions

Where is Solver in Excel?

Solver in excel is a hidden tool. But, we can add the feature using the following steps:

Step 1: First, Go to the File tab.

Step 2: Next, click on Options.

Step 3: The Excel Options window appears.

Select Add-ins.

Step 4: Then, select the Solver Add-in and click on Go.

Step 5: The Add-ins window pops up.

Choose Solver Add-in.

Step 6: Click OK.

Clearly, we can see Solver option under the Analyze group in the Data tab. Therefore, we can enable or add solver in excel.

Please Note: Once we click OK, excel may hang for 5 seconds. During this time, the application adds the Solver option under the Data tab.

How to use Solver in Excel?

Using solver in excel is easy. Let us look at the following example to learn how to use it.

Consider the below table with variables X, Y, and Z in column A. The total of the three variables must be equal to 120. In addition, the variables must satisfy three conditions.

They are:

1. Value of X variable should be greater than or equal to 50 (>= 40)
2. Value of the Y variable should be greater than or equal to 40 (>= 40)
3. Value of the Z variable should be lesser than or equal to 30 (<=30)

Faq 2

We need to use the following steps to find the values of the three variables using solver in excel.

Step 1: First, add the data to the spreadsheet.

Step 2: Next, enter the SUM function in cell B5 to add the values of the three variables.

Step 3: Then, go to the Data tab.

Step 4: Click on the Solver option from the Analyze group.

Step 5: The Solver Parameters dialog box pops up.

Enter the data in the dialog box.

Click OK to see the values of the three variables.

Faq 2.1

Thus, we can obtain the values using solver in excel.

Why use Solver in Excel?

Solver in excel can be used to solve complex business models and to find the required variable values based on the constraints added.

Can Solver have multiple objectives in Excel?

Yes, the solver can be used to set multiple objectives in excel.

Download Template

This article must be helpful to understand Solver in Excel, with its examples. You can download the template here to use it instantly.

Solver in Excel Download

Solver in Excel

What Is Solver In Excel?

Table of contents

Key Takeaways