Microsoft Excel comes with several features that make it easy to perform calculations and solve equations, helping save time and effort. Solver is one such feature that lets you solve problems and is pretty similar to the Goal Seek feature.

How to Use Goal Seek in Excel
Goal Seek is one of Excel’s built-in What-If Analysis tool that helps you find the input value in a formula to get a desired result.
All Things HowRaj Kumar

Generally, it is used for 'What-if' analysis to find the value of a cell when there is more than one constraint. You can use it to solve for a particular number, a minimum value, or the maximum value of a number. While it cannot solve every problem, it is a great tool for situations when you are dealing with optimization problems and need to find the most suitable decision.

Solver works by changing the value of certain cells, called decision variables, in a spreadsheet to determine the maximum or minimum value of a cell, called the objective cell. And it does this while satisfying certain constraints, depending on the problem. You can use Solver for linear and non-linear programming, integer programming, and goal-seeking.

Some examples where Solver can help you include minimizing delivery costs, finding the ideal work schedule for workers, determining the optimal budget for an ad campaign, or maximizing the return on your investment, among others.

Adding Solver to Excel

First, you need to load Solver since it is an add-in and not activated by default, unlike Goal Seek. However, it is present within Excel so enabling it isn't very complicated.

  1. Click on the File menu at the top and then on 'Options'.
  1. Then click on 'Add-ins' on the left in the Options dialog box.
  1. Now, click on the 'Manage' dropdown menu at the bottom and select 'Excel Add-Ins'. Then click on the 'Go' button.
  1. In the dialog box that appears, click on the 'Solver add-in' checkbox to select it and click on the 'OK' button.
  1. Solver should now appear in Excel when you click on the 'Data' tab.

Components of Solver

Solver requires three primary components to be set up before it can find the optimal value for any problem. These include:

  • Objective Cell - The objective contains the formula for the goal or objective of the problem. This can be to minimize, maximize, or achieve a target value.
  • Variable Cells - Variable cells contain the variables to be changed to reach the goal or objective. You can specify up to 200 variable cells in Solver.
  • Constraints - Constraints are the limitations within which Solver has to achieve the target value. These are the conditions that it should meet when finding the required value.

Using Solver

Once you've added Solver to Excel, you can start using it. Here, we will use Solver to find out the profit of a pallet manufacturing company using the known resource values like the resources required per pallet and the availability of different pallet types.

  1. In this example, the cells B3 through E3 display the names of the types of pallets the company has to manufacture. The row below displays the number of pallets to be manufactured for each type, which is currently zero. Then there is the profit for different pallet types in the next row. We have to find the number of pallets to be built for each pallet type and the total profit, which will appear in Cell F5. The constraints here are resources available, which determine the number of pallets the company can manufacture.
  1. Click on 'Solver' from the top right and the Solver dialog box will appear. Type in a name or cell reference for the objective cell. Keep in mind that it should contain a formula. In this case, it will be Cell F5 which is the objective function and should give you the total profit for all pallet types combined together, taking into account the resources available and the pallets to be made.
  1. Next, for the 'By Changing Variable Cells' field, select the B4:E4 cells using your mouse or type the cell name inside. These are the cells that will show the number of pallets for each pallet type and have their current values as zero. These will be changed when you run Solver.
  1. Now, click on the 'Add' button on the right to add constraints. Solver will calculate the number of pallets that the company can build by determining how much glue, pressing, pine chips, and oak chips are available. When you run Solver, you will see the values in the 'Used' column – which are currently zero — change.
  1. Type F8:F11 in the 'Cell Reference' field, which is the 'Used' column, and G8:G11 which is the 'Available' column in the Constraint field. The relation should be <= which is selected by default, indicating that the values in the Used column will always be less than or equal to those in the Available column.
  1. Now, click on the 'Add' button in the 'Add Constraint' dialog box again and then close it. You will also see that the 'Make Unconstrained Variables Non-Negative' option is enabled by default in the Solver Parameters dialog. This ensures that other variables will not be negative even if they are not set as constraints.
  1. The Solver Parameters dialog box will appear once you've completely entered all the problem variables and constraints. Click on the 'Solve' button and wait for Excel to provide you with an output.
  1. Once the results appear, you will see the Solver Results dialog box in the cells from B4 through E4. Remember that Solver will change the values of your data, so if you do not want to keep the solution, you can click on the 'Restore Original Values' option. Once you've decided whether to keep the solution or the original values, check that the 'Answer' option is selected on the right and click on the 'OK' button to close the dialog box.
  1. If you choose to keep the solution, you will see that the data in your spreadsheet has changed to reflect that when you close the Solver dialog box. The company needs to make 23 Tahoe pallets, 15 Pacific pallets, 39 Savannah pallets, and none of Aspen, which will all appear in the Pallets row from B4 through D4. The total profit cell will also change from zero to $58,800.
Note: If you want, you can generate an Answer Report from the Solver Results dialog box by clicking on 'Answer' under the Reports section on the right. Excel will generate a new Answer worksheet and attach it to your existing one.

Things to know

  • Just like Excel's Goal Seek feature, Solver requires you to already have set up the formulas you need for the feature to work correctly.
  • You can choose how you want Solver to solve problems by clicking on the 'Options' button in the Solver Parameters dialog box. Here you can type in values for 'All Methods', 'GRG Nonlinear', and 'Evolutionary'.
  • Solver also lets you save and load models for future reference or use. When loading models, you need to enter the reference for the complete range of cells containing the values for which the problem needs to be solved.
  • It is a good idea to use Solver on a copy of your data since it changes the original data once you run it. You may not be able to recover that data once it has been changed.