If you want to find the lowest and highest numbers possible to answer a mathematical problem, you’ll need to install and use the Solver add-in. Here’s how to install and use Solver in Microsoft Excel.
What is Solver for Excel?
Solver is a third-party add-in, but Microsoft includes it with Excel (although it’s disabled by default). It offers what-if analysis to help you determine the variables you need to answer a mathematical problem. For instance, what is the minimum number of sales you’d need to make to cover the cost of an expensive piece of business equipment? This problem has three parts to it—a target value, variables that it can change to reach that value, and constraints that Solver has to work in. These three elements are used by the Solver add-in to work out the sales you’d need to cover the cost of that equipment. This makes Solver a more advanced tool than Excel’s own Goal Seek feature.
How to Enable Solver in Excel
As we’ve mentioned, Solver is included with Excel as a third-party add-in, but you’ll need to enable it first to use it. To do that, open Excel and press File > Options to open the Excel Options menu.
In the Excel Options window, press the Add-ins tab to view the settings for Excel add-ins.
In the Add-ins tab, you’ll see a list of available Excel add-ins. Select Excel add-ins from the Manage drop-down menu at the bottom of the window, then press the Go button.
In the Add-ins window, press the checkbox next to the Solver Add-in option, then press OK to confirm.
Once you click OK, the Solver add-in will be enabled, ready for you to begin using it.
Using Solver in Microsoft Excel
The Solver add-in will be available for you to use as soon as it’s enabled. To start, you’ll need an Excel spreadsheet will appropriate data to allow you to use Solver. To show you how to use Solver, we’ll be using an example mathematical problem. Following on from our earlier suggestion, a spreadsheet exists to show the cost of an expensive piece of equipment. To pay for this equipment, a business needs to sell a certain number of products to pay for the equipment. For this query, several variables could change to meet the target. You could use Solver to determine the product cost to pay for the equipment, based on a set number of products. Alternatively, if you’ve set the price, you could determine the number of sales you’d need to break even—this is the problem that we’ll be attempting to solve using Solver.
Launching Solver in Excel
To use Solver to solve this kind of query, press the Data tab on the Excel ribbon bar.
In the Analyze section, press the Solver option.
This will load the Solver Parameters window. From here, you can set up your Solver query.
Choosing Solver Parameters
First, you need to select the Set Objective cell. For this scenario, we want the revenue in cell B6 to match the equipment cost in cell B1 to break even. From this, we can determine the number of sales we’d need to make.
The To figure lets you find the minimum (Min) or maximum (Max) possible value to meet a target, or you can set a manual figure in the Value Of box. The best option for our test query would be the Min option. This is because we want to find the minimum number of sales to reach our break-even target. If you wanted to do better than this (for instance, to make a profit), you could set a custom revenue target in the Value Of box instead. The price is remaining the same, so the number of sales in cell B5 is the variable cell. This is the value that needs to increase. You’ll need to select this in the By Changing Variable Cells option box.
You’ll need to set constraints next. These are the tests that Solver will use to help pick its final value. If you have complex criteria, you can set multiple constraints for Solver to work in. For this query, we’re looking for a revenue number that’s greater than, or equal to, the initial equipment cost. To add a constraint, press the Add button.
Use the Add Constraint window to identify your criteria. For this example, cell B6 (the target revenue figure) needs to be greater than, or equal to, the equipment cost in cell B1. Once you’ve selected your constraint criteria, press the OK or Add buttons.
Before you can run your Solver query, you need to confirm the solving method for Solver to use. By default, this is set to the GRG Nonlinear option, but there are other solving methods available. Once you’re ready to run the Solver query, press the Solve button.
Running a Solver Query
Once you press Solve, Excel will attempt to run your Solver query. A results window will appear to show you whether or not the query was successful. For our example, Solver found that the minimum number of sales required to match the equipment cost (and thus break even) was 4800. You can select the Keep Solver Solution option if you’re happy with the changes Solver has made, or Restore Original Values if you’re not. To return to the Solver Parameters window and make changes to your query, press the Return to Solver Parameters Dialog checkbox.
Click OK to close the results window to finish.
Working with Excel Data
The Excel Solver add-in takes a complex idea and makes it a possibility for millions of Excel users. It’s a niche feature, however, and you may be looking to use Excel for much simpler calculations. You can use Excel to calculate percentage changes or, if you’re working with a lot of data, you can cross-reference cells across multiple Excel worksheets. You can even insert Excel data into PowerPoint if you’re looking for other ways to use your data.
