Data Analysis [Part 5 of 5] – Using Solver in Excel

This is the fifth and final article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use Solver in Excel.

Download Example File

Watch Video – Using Solver in Excel

Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.

How to Find Solver Addin in Excel

Solver add-in is disabled in Excel by default. Here are the steps to enable it:

Here are the steps to enable it:

  • Go to File –> Options.
  • In the Excel Options Dialogue Box, select Add-in the left pane.
  • In the right pane, at the bottom, select Excel Add-ins from the drop down and click on Go..Solver in Excel - Add Solver Add in
  • In the Add-ins dialogue box, you will see a list of available Add-ins. Select Solver Add-in and click OK.Solver in Excel - Add Solver Add in and Click GO
  • This will enable the Solver Add-in. It will now be available in the Data Tab under Analysis group.
Using Solver in Excel – Example

Solver gives you the desired result when you mention the dependent variables and the conditions/constraints.

For example, suppose I have a data set as shown below.

Solver in Excel - Data

This example has manufacturing data for 3 widgets – Quantity, Price per Widget, and Overall Profit.

Objective: To get the maximum profit.

If you have an idea about manufacturing, you’d know you need to optimize the production to get the best output. While in theory you can manufacture unlimited quantities of the highest profit widget, there are always a lot of constraints under which you need to optimize the production.

Constraints

Here are a couple of constraints that you need to consider while trying to maximize the profit.

  • At least 100 Quantity of Widget A should be made.
  • At least 20 Quantity of Widget B should be made.
  • At least 50 Quantity of Widget C should be made.
  • A total of 350 widgets should be made.

This is a typical manufacturing optimization issue and you can be easily answer it using Solver in Excel.

Steps to Use Solver in Excel
  • Once you have the solver add-in activated (as explained above in this article), Go to Data –> Analysis –> Solver.Solver in Excel - Ribbon
  • In the Solver Parameter dialogue box, use the following:Solver in Excel - Solver Dialogue Box
    1. Set Objective: $D$5 (this is the cell which has the desired value – in this case, it is overall profit).
    2. To: Max (since we want the maximum profit).
    3. By Changing Variable Cells: $B$2:$B$4 (variables that we want to optimize – in this case, it’s the quantity).
    4. Subject to the Constraints:
      • Here you need to specify the constraints. To add a constraint, click on Add. In the Add Constraint dialogue box, specify the Cell Reference, the condition and the Constraint Value (as shown below):Solver in Excel - Add a Constraint
      • Repeat this process for all the constraints.
    5. Select a Solving Method: Select Simplex LP.
    6. Click Solve
      • In case solver finds a solution, this will open the Solver Result dialogue box. You can choose to keep solver solution (which you can see in your data set), or choose to revert back to the original values.
        • You can also Save this as one of the scenarios, that can be used in the Scenario Manager.
        • Along with this, you can also choose to create reports: Answer, Sensitivity, and Limits. Just select it and click OK. This will create different tabs with details one each for Answer, Sensitivity, and Limits (if you select only one or two, then that many tabs are created).Solver in Excel - Create Summary

With this article, I have tried to introduce you to Solver. There is much more that can be done, and if you are into statistics, I would recommend you go and read more about it. Here are a couple of good articles that I could find online:

Try it Yourself.. Download the File
Download File Pic

You May Also Like the Following Excel Tutorials:

  • Asif Hafeez says:

    Nice article & I agree with Mark. Sumit can we find some practice exercises in Excel format for Solver as well as for Data Analysis.

    • Sumit Bansal says:

      Thanks Asif. Glad you like the article. I have provided one sample file for download in this article. I will create some more example files and share with you and the others.

  • Mark says:

    Good Series of Articles. Solver is something not many people use, but I can see it can be useful in many cases.

  • >