Excel Solver is a tool, also referred to as Data Analysis add-in, used to find the optimal solution in the decision-making process mostly for business and engineering related problems. For example, we can find the optimal advertising campaign with regard to budget to maximize profitability. We will learn how to use Solver to find the optimal solution by adding Solver to Excel.

*Figure 1. Solver Examples Optimization*

**How to Add Solver to Excel**

Excel Solver add-in is available in all the Excel versions but it is not included by default. Therefore we have to add it manually in our Excel version by going through the following steps.

- In Excel 2007, go to the
**Microsoft Office**button and then click on Excel**Options**. But in Excel 2010 and later versions, go to**File**and then click**Options.** - On the left sidebar of the Excel
**Options**, select**Add-in**>**Manage**box and select**Excel Add-in**and press**Go**button.

* Figure 2. Add-in Options*

- From the available
**Add-ins**dialog, select**Solver****Add-in**and click**OK**button. After getting installed the**Solver**Add-in is available in the**Analysis**section of**Data**tab.

* Figure 3. Adding Solver to Excel*

**How to Use Solver in Excel**

We need to set up the data in the worksheet before using the Solver function to find the optimal solution. Suppose we need to run an advertising campaign for a defined budget of $45000 for a defined time frame of 10 weeks. We have planned to run 100 adds per week and we need to calculate the minimum cost per add using Microsoft.Solver.

* Figure 4. Data Formulation*

As we have set up the data as per our problem description, now we need to run the Solver by clicking **Data tab > Solver in Analysis section**. The Solver parameters window will open having the following sections;

**Set Objective**

We first need to set the object by choosing the objective cell C6 which calculates the payment terms having the formula **=C3/(C4*C5)** in terms of the number of weeks to run an advertisement for. Then we need to choose the optimal objective of maximum, minimum or a set value to achieve. We’ll use 10 weeks in our example.

* Figure 5. Setting Objective Parameters*

**Variable Cells**

In this section, we need to select the variable cells in our problem model having data to change to meet our set objective We have cells C4 and C5 contain the variable data of adds per week and cost per add. If these cells are non-adjacent then manually enter the cells references separated by commas.

* Figure 6. Selecting the Variable Cells*

**Setting Up Constraints**

Solver constraints are limitations or conditions applicable to the problem’s solution. In the Subject to Constraints section of the parameters window, click on **Add** button to add the constraints.

* Figure 7. Solver Constraints Parameters*

In the **Add Constraints** dialog, enter the constraints and press **Add** button. When we are done with adding all the constraints then we press the **OK** button. Like, we have added the constraints for the **total budget amount** and total **adds per week**.

* Figure 8. Adding Constraints*

After setting up the parameters, we need to click on the **Solve** button.

* Figure 9. Run Solver*

When Solver solves the problem a **Solver Results** window appears where we need to select **Keep Solver Solution** option and press **OK** button.

* Figure 10. Solver Example Problems*

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment