A while back, I wrote a tutorial about a useful feature in Microsoft Excel called Goal Seek. It allows you to solve simple “what-if” scenarios. However, it has limitations. Fortunately, Microsoft has a more robust data analysis tool. You can even add a constraint to your equation. In this tutorial, I’ll show you how to find and use the Excel Solver add-in. (Includes practice file.)
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 sheet 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. The 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. The current values are from the election results.
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. No stuffing the ballot box with additional YES votes. Other constraint types might include integers or minimum values.
The Solver Parameters Dialog Box
I can use these cell references in the Solver Parameters dialog, as shown below. Let’s use my election results as an Excel Solver example.
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 $T$26.
Underneath, you’ll see a To: line with three radio buttons. In my scenario, I want to test for a Value Of 66.7%. However, I can’t type a % sign in this field, so I’ll use the decimal value.
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 C21:C22.
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 Excel Solver
The steps below were done using Microsoft 365 (Previously called Office 365) and the sample spreadsheet. 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.
Taking It To The Next Level
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.