Ever wish you had an easier way to analyze spreadsheet data and pinpoint the most effective solution? Download our free practice file and discover how Excel’s Solver add-in can unlock more efficient problem-solving. This easy-to-follow guide will walk you through a simple example to reveal Solver’s power, from setting up your data to finding the optimal solution.

## Knowledge You’ll Gain

*Understanding Solver:*Grasp the fundamentals of Excel Solver and its capabilities for problem-solving.*Installing Solver:*Learn how to get the add-on if it’s not already installed.*Setting Up Solver:*Learn how to input your data, define constraints, and set objectives.*Learning Solving Methods:*Discover the different solving methods available (GRG Nonlinear, Simplex LP, and Evolutionary)*Applying Solver to a Scenario*: See an example of how Solver can be used.

## What is Excel Solver?

The Excel Solver add-in is designed to do more complex **“what-if” calculations** using different solving methods. The main advantage is Solver allows you to work with variables and constraints to find an optimal solution.

If you recall my Excel Goal Seek tutorial, my total vote count increased. That was the problem. With this enhanced solving method, I can ask Excel Solver to tell me how the YES/NO vote count needed to change to win an election *while not altering the total vote count*.

To solve this problem, you need to define certain cell types. I’ve added background colors and callouts to my example. I find adding the color helps me later when I enter variables and values into the Solver Parameters dialog box.

[A] **Objective Cell** – A single cell on the active worksheet that represents my goal. Currently, the value is 63.90%. I want to change this value to the required winning percentage.

[B] **Variable Cell** – This can be one or more cells that Solver will change to achieve the desired objective. It can be a range of cells or nonadjacent cells. Solver will run through its calculations by adjusting the YES/NO counts until it finds the optimal solution. You’re allowed up to 200 variable cells.

In my example, I have 2 adjacent values. However, you might have a problem where your variables are not next to each other. In this case, you can hold down your `CTRL` key and click or separate the cells by a comma.

[C] **Constraint Cell** – This is an optional but essential cell. As the name suggests, it limits the solution to specific criteria. Your spreadsheet can have more than one constraint. In our case, the **TOTAL **vote count has to stay the same at 7008. No stuffing the ballot box with additional **YES **votes. Other constraint types might include integers or minimum values.

## Solver Parameters: An Overview

We’ll use these cell references in the **Solver Parameters **dialog, as shown below. Let’s use my election results as an Excel Solver example. Don’t worry as I’ll explain all of this and then provide the steps for the practice sheet.

The first field is the **Set Objective**. Once you click in the text box, click your desired Excel cell. It will be carried over as an absolute reference with the **$** anchors. In earlier versions of Solver, Excel called this field the **target cell**. So our value is $D$4.

Underneath, you’ll see a **To:** line with three radio buttons. In my scenario, I want to test for a **Value Of** 66.7% which is the required winning percentage. However, I can’t type a % sign in this field, so I’ll use the decimal value. Once Solver completes its iterations this new value will replace the original. That’s why I’ve shown both in green.

Now that we’ve told Excel our objective is to get 66.7% of the YES votes, I need to define my variables and which cells can be changed. So, again, I clicked in the** By Changing Variable Cells:** field and highlighted cells C4:C5.

The last item I defined was my constraint. This is done by clicking the **Add **button on the lower right side. Looking under the **Subject to the Constraints: **section, you’ll see I’m telling Excel to keep the total vote count at 7008.

I also kept the default Solving Method of **GRG Nonlinear**. However, there are more complex methods using linear programming solutions.

## How to Install Excel Solver

I’m unsure why Solver in Excel isn’t installed by default. It’s an add-on.

- Open Microsoft Excel.
- From the
**File**menu, select**Options**. - In the
**Excel Options**dialog box, click**Add-ins**from the left menu. - Look down to the bottom right side for a field called
**Manage**: - Select
**Excel Add-ins**from the drop-down list. - Click the
**Go…**button. - From the
**Add-ins**dialog box, check the box for**Solver Add-in**. - Click
**OK**.

If you go to your **Data **tab, you should see **Solver **in the **Analyze **section.

## How to Use Solver in Excel

The steps below were done using Microsoft 365 and the practice spreadsheet below. The spreadsheet has two versions of the election results, so you can use Solver on one and view the original results. Once you learn the tool, you can skip adding a second version.

- Download and open the practice file.
- You may need to click the
**Enable Editing**button on the**Protected View**banner. - Click cell
**G3**. There is no magic to this cell reference except that it opens Solver closer to my data. - Click the
**Data**tab. - From the
**Analyze**group, click**Solver**. - The
**Solver Parameters**dialog box appears. You can reposition it on your screen.

- Click in the
**Set Objective**box and then click cell D4. - Toggle the
**Value Of:**radio button. - Replace the “
**0**” value with**0.667**. - Click in the
**By Changing Variable Cells:**field. - Highlight cells
**C4:C5**. Excel will convert these to an absolute reference.

- Move to the
**Constraints**area and click the**Add**button on the right. The**Add Constraint**dialog opens. - Click in the
**Cell Reference**field. - Click cell
**C6**. Excel will automatically populate the field. - Move to the next field and select
**=**from the drop-down menu. - In the
**Constraint:**field type 7008 since we want to match the total votes count.

- Click the
**OK**button since we only have one constraint. - Click the
**Solve**button. Excel will attempt to find the optimal solution. - The
**Solver Results**dialog opens, and your spreadsheet values in**C4:C5**should’ve changed. Also, the**Total %**has increased to our target value of 66.70%.

- Click
**OK**to accept the new solution.

If you do the math, you can see this ballot measure could’ve had a different outcome if 196 people had voted “yes” instead of “no.” Or if more people voted.

## Exploring More Solver Features

The analysis add-in can do some sophisticated optimization analysis using the other radio button options like **Max **and **Min**. Microsoft has a tutorial on using Solver to determine the optimal product mix. It’s much more detailed than this article and includes using the **SUMPRODUCT **function and a linear solving method.

As you learn how to use Solver, you’ll discover more options than this simple example. For example, the Excel Solver function allows you also to load and save optimization scenarios. It also has a report section that provides additional details.

## Test Your Knowledge

Below are 4 questions regarding the article you just read. Read each question and see if you know the answer. Then click the green marker to see the answer.

## Q: What is the purpose of Excel Solver?

Answer: To find the optimal solution to a problem by adjusting variables given a set of constraints.

## Q: Name 2 essential components to set up Solver?

Answer: Objective cell and Variable cells.

## Q: Do the variable cells have to be adjacent?

Answer: No

## Q: True or False: Solver requires you to set constraints?

Answer: False. Constraints are optional and you can have multiple constraints.