How to use Solver in Excel 2022

An optimization tool used to determine the desired outcome by changing a model's assumptions

Table of Contents Show

How to Use Excel Solver – Example
Graph Solution
Download Template
Solver vs Goal Seek
Access CFI’s Excel Shortcuts Cheat Sheet
Additional Resources
Article Sources

Excel Solver[1] is an optimization tool that can be used to determine how the desired outcome can be achieved by changing the assumptions in a model. It is a type of what-if analysis and is particularly useful when trying to determine the “best” outcome, given a set of more than two assumptions. Learn with video instruction in CFI’s Advanced Excel Course.

How to Use Excel Solver – Example

The best example of how to use Excel solver is by graphing a situation where there is a non-linear relationship between, for example, the number of salespeople in a company and the profit that they generate.

There is a diminishing return on salespeople, so we want to figure out what the optimal number of people to hire is. Put another way, we want to figure out how many salespeople we should hire to get the maximum amount of profit.

Step 1: Ensure the Solver Add-In is Installed

The first step is to make sure you have Solver installed in your Excel file. To do this, go to File -> Options -> Add-Ins -> Manage Excel Add-Ins[2]. When the dialogue box appears, make sure the box is ticked, as shown below.

Step 2: Build a Model

Here, we’ve created an example where there is a non-linear relationship between the number of salespeople in a company and its profit. This is because as more salespeople are added, the less effective overall they become. Imagine a very niche market that only has a certain number of customers.

As you add more salespeople initially, you generate a lot more revenue, but at some point, additional salespeople run out of new people to prospect and they become dead weight for the company. The point of this model is to show an example that can’t easily be calculated using Goal Seek or some other solution.

Learn with video instruction in CFI’s Advanced Excel Course.

Step 3: Use the Ribbon to Launch Excel Solver

The Excel Solver function is located on the Data Ribbon and the keyboard shortcut on Windows is Alt, A, Y21. Once the dialog box appears you will notice several options you can work with.

Set the “Objective” cell. In our case, this is Profit.
Set it “To” something (Max, Min, or a specific value). In our case, we want the Max Profit.
Select the Cell(s) you want to change in order to find the solution. In our case, we want to select the number of salespeople cell.
Add constraints. If you want to set a constraint (e.g., a cell must be > or < some number), you can add this in. In our case, there are no constraints.
Click “Solve.”
Decide if you want to keep the solution in the cells or restore the original values.

Graph Solution

In this example, we’ve also shown how you could use a graph to get the solution, and this really helps illustrate the relationship between the number of salespeople and the profit of the business. Using both Excel Solver and the graph together really instills confidence in our analysis.

Download Template

Download the Excel file used in this example to play around with it. Try building your own from scratch too!

Solver vs Goal Seek

Both tools are extremely useful when performing financial modeling and analysis in Excel. The main difference is:

Goal Seek only allows you to change one variable, while Solver allows you to change many variables at once.
Solver allows you to find a Max or Min solution (optimization) while Goal Seek forces you to choose a specific outcome (not necessarily the optimal solution).

Access CFI’s Excel Shortcuts Cheat Sheet

Use the form below to download CFI’s free Excel Shortcuts Cheat Sheet for Mac and PCs.

Additional Resources

Thank you for reading CFI’s guide to optimization problems in Excel and using Excel Solver. To continue learning and advancing your career as a world-class financial analyst, these additional CFI resources will be very helpful:

Article Sources

To let the Excel Solver know which cells on the worksheet represent the decision variables, constraints and objective function, we click Solver button on the Excel Data tab, or the Premium Solver button on the Add-Ins tab, which displays the Solver Parameters dialog. In the Set Objective (or Set Target Cell) edit box, we type or click on cell F5, the objective function. In the By Changing Variable Cells edit box, we type B4:E4 or select these cells with the mouse. (Click on the image to see it full-size.)

To add the constraints, we click on the Add button in the Solver Parameters dialog and select cells F8:F11 in the Cell Reference edit box (the left hand side), and select cells G8:G11 in the Constraint edit box (the right hand side); the default relation <= is OK. (Click on the image to see it full-size.)

We choose the Add button again (either from the Add Constraint dialog above, or from the main Solver Parameters dialog) to define the non-negativity constraint on the decision variables. (Alternatively, we can check the Make Unconstrainted Variables Non-Negative option in the Solver Parameters dialog.)

When we've completely entered the problem, the Solver Parameters dialog appears as shown below. This is the Excel Solver dialog from Excel 2010; the Solver in earlier versions of Excel have similar elements. Frontline's Premium Solver products can emulate either style, and they also offer a new Ribbon-based user interface. (Click on the image to see it full-size.)

Finding and Using the Solution

To find the optimal solution, we simply click on the Solve button. After a moment, the Excel Solver returns the optimal solution in cells B4 through E4. This means that we should build 23 pallets of Tahoe panels, 15 pallets of Pacific panels, 39 pallets of Savannah panels, and 0 pallets of Aspen panels. This results in a total profit of $58,800 (shown in cell F5). (Click on the image to see it full-size.)

The message "Solver found a solution" appears in the Solver Results dialog, as shown above. (Click on the image to see it full size). We now click on "Answer" in the Reports list box to produce an Answer Report, and click OK to keep the optimal solution values in cells B4:E4.

After a moment, the Solver creates another worksheet containing an Answer Report, like the one below, and inserts it to the left of the problem worksheet in the Excel workbook. (Click on the image to see it full-size.)

This report shows the original and final values of the objective function and the decision variables, as well as the status of each constraint at the optimal solution. Notice that the constraints on glue, pressing, and pine chips are binding and have a slack value of 0. The optimal solution would use up all of these resources; however, there were 28,000 pounds of oak chips left over. If we could obtain additional glue, pressing capacity, or pine chips we could further increase total profits, but extra oak chips would not help in the short run.

There's a whole world beyond Excel Solver: Upgrade to Analytic Solver Basic and get greater solving power, wizards and Guided Mode, over 100 example models, extensive Help and user guides, access to our Solver.Academy courses, expert tech support -- plus Monte Carlo simulation and risk analysis, decision trees, data mining, text mining and machine learning, and more! It's an incredible offer, and you can try it for free.
Learn more about Analytic Solver Click Here

Learning More

If you've gotten to this point, congratulations! You've successfully set up and solved a simple optimization problem using Microsoft Excel. If you'd like, you can see how to set up and solve the same Product Mix problem using Risk Solver Platform in Excel or using a Visual Basic .NET program that calls Frontline's Solver Platform SDK. If you haven't yet read the other parts of the tutorial, you may want to return to the Tutorial Start and read the overviews "What are Solvers Good For?", "How Do I Define a Model?", "What Kind of Solution Can I Expect?" and "What Makes a Model Hard to Solve?"

This was an example of a linear programming problem. Other types of optimization problems may involve quadratic programming, mixed-integer programming, constraint programming, smooth nonlinear optimization, and nonsmooth optimization. To learn more, click Optimization Problem Types. For a more advanced explanation of linearity and sparsity in optimization problems, continue with our Advanced Tutorial.