Grab our practice workbook 👉 HERE and follow along:
What is Excel Solver?
Imagine you want to maximize your profit by adjusting prices and costs while keeping prices and costs within specific limits. Or, you need to allocate your remaining budget across multiple projects with certain spending constraints. Excel Solver can help you achieve these goals efficiently.
Excel Solver is a powerful tool in Microsoft Excel that helps you find the best solutions by adjusting multiple inputs. Use Solver in Excel to maximize or minimize outcomes like profit, cost, or efficiency.
Key Features of Excel Solver
- Optimization: Solver helps you find the best values for your inputs to get the desired results. Increase profits, reduce costs, or meet specific targets easily.
- Multiple Variables: Adjust several inputs at once, making Solver perfect for complex problems.
- Constraints: Set limits for your inputs, such as keeping prices within a range or ensuring numbers are whole.
- Solving Methods: Choose from different methods to solve your problem:
- GRG Nonlinear: Best for nonlinear problems, finding a local best solution.
- Simplex LP: Ideal for linear problems.
- Evolutionary: Great for complex problems, finding a global best solution.
How to Add Solver to Excel
Often our students ask: Where is Solver in Excel?
Did you know the Microsoft Solver add-in is included with all versions of Excel since 2003, but it’s not enabled by default? Here’s how you can quickly activate it:
- Open Excel: Start by opening your Excel application.
- Go to File: Click on the “File” tab in the top left corner.
- Select Options: Choose “Options” from the drop-down menu.
- Choose Add-ins: In the Excel Options window, click on “Add-ins”.
- Manage Add-ins: At the bottom of this window, find the “Manage” box. Select “Excel Add-ins” and click “Go”.
- 6. Enable Solver: Check the box next to “Solver Add-in”.
- 7. Confirm: Click “OK” to activate Solver.
Now, you can find Solver under the “Data” tab in the “Analyze” section.
How to Use Solver in Excel – Example
Goal Seek is great, but it only changes one input variable. For more complex problems with multiple variables, Solver is the tool you need.
Here’s a simple example:
You want to achieve a target income of 2,000 by adjusting the units sold and the price per unit.
Variables
- Units Sold: Input variable.
- Price per Unit: Input variable.
- Revenue: Calculated as Units Sold * Price per Unit
Cell B5 = B3*B4- Cost per unit: A fixed number.
- Total Costs: Calculated as Units Sold * Cost per Unit.
Cell B9 = B3*B8- Income: Calculated as Revenue – Total Costs.
Cell B11 = B5-B9Constraints
- Units sold must be a whole number.
- Price per unit should be between 3 and 4.
Using Solver in Excel
When you open the Solver Parameters window, you need to set up three primary components: the objective cell, variable cells, and constraints. Here’s how to do it:
Set the Objective
Objective cell: This is the cell that contains the formula you want to optimize. For example, if you want to maximize profit or minimize costs, select the cell with that calculation.
In our example, the objective cell is $B$11, which calculates the income.
- Go to the Data tab, find the Analyze section, and click Solver. Set the objective cell ($B$11) to a value of 2,000.
Select Variable Cells
These are the cells that Solver will change to achieve the objective. Highlight the cells you want to adjust.
- For our example, these are the units sold and the price per unit, so we select $B$3:$B$4 and $B$8.
Add Constraints
Constraints are the limits you set for your variable cells. Click “Add” in the Solver Parameters window to specify these limits. For our example, set the units sold to be a whole number (integer) and the price per unit to be between 3 and 4.
- Click “Add” to set limits. Specify that units sold must be an integer and the price per unit should be between 3 and 4.
- Choose Solving Method: Select the appropriate solving method (e.g., GRG Nonlinear).
Click on Solve. The Solver Results window will pop up, saying that it has found a solution. You will notice that the values of the input cells have changed but the formulas in your calculated cells have been retained. You are given the option to accept the new values by clicking on OK, or reject them by clicking on Cancel.
Types of Solving Methods in Excel Solver
Excel Solver offers three methods to find the best solution for your problem. Here’s a quick overview:
GRG Nonlinear
- Best For: Nonlinear problems.
- How It Works: Finds a local optimal solution.
- Default Method: This is the most commonly used method and is set as the default.
Simplex LP
- Best For: Linear problems.
- How It Works: Efficiently solves problems with linear relationships.
Evolutionary
- Best For: Complex and non-smooth nonlinear problems.
- How It Works: Searches for a global optimal solution, which can take longer to run compared to GRG Nonlinear.
Using Solver for Complex What-If Problems with Constraints
Solver can be a powerful tool when you need to allocate a budget among multiple projects, especially with specific constraints. Here’s an example to illustrate how to use Solver for this purpose:
Scenario
You need to distribute a budget across several projects, with the following constraints:
- Extra costs should not exceed 1,000.
- Fixed costs must remain at 2,000.
- Total project cost for each project should not exceed 9,600.
Setting Up Solver
Set Objective:
- Go to the “Data” tab, find the “Analyze” section, and click on “Solver”. Set the objective cell ($B$14) to a value of 65,000.
Select Variable Cells:
Highlight the cells that Solver can change:
- Extra costs: $D$5:$D$13
- Production costs: $B$5:$B$13 (we choose Production Cost instead of Total Costs because the latter is a calculated field).
Add Constraints:
Click “Add” in the Solver Parameters window to set the following constraints:
- Extra costs ($D$5:$D$13) must be less than or equal to 1,000 ($E$18).
- Total project cost ($E$5:$E$13) must be less than or equal to 9,600 ($E$20).
Solve: Choose the appropriate solving method (e.g., GRG Nonlinear) and click “Solve”. Solver will find the best solution, adjusting the input cells within the defined constraints.
Tips for Using Solver
- Start Simple: Begin with straightforward scenarios and gradually add complexity.
- Experiment: Try different settings and constraints to see how they affect the outcome.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel Solver tool with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
