The Excel Solver Add-In: The Best Explanation in Just 5 Minutes
This Excel Solver Add-In tutorial is suitable for users of Excel 2013/2016/2019 and Microsoft 365.
There are many Excel features that make life easier for us. One of them is the Excel Solver Add-In. This clever feature lets us find solutions to problems that have several constraints, which cannot be solved with a simple formula.
Related:
The Best Excel Project Management Template In 2021
How To Use Excel Countifs: The Best Guide
How To Protect Cells In Excel Workbooks-the Easiest Way
In this Excel Solver guide, we’ll cover:
- VIDEO TUTORIAL – Excel Solver
- Excel Solver—Explained
- Loading the Solver Add-In
- Using Solver to Find Solutions to Models
Objective
Use the Excel Solver add-in to help you solve basic to complex problems in Microsoft Excel.
VIDEO TUTORIAL – Excel Solver
Excel Solver—Explained
Excel includes an analysis utility called Solver. It’s available as an add-in and helps you solve problems and find the optimum solution for any given scenario.
Loading the Solver Add-In
Solver is located on the Data tab in the Analyze group.
If you can’t see it and you are using a version of Excel that supports Solver, you will need to load the add-in.
- Click on the File tab
- Click Options
- Click Add-ins
- Select Excel Add-ins
- Place a tick next to Solver
Also Read:
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
Predict Future Values Using Excel Forecast Sheet – The Best Guide
Getting Started With Excel 3d Maps – The Top 5 Steps
Using Solver to Find Solutions to Models
Before using the Solver, you have to build a clear model for the problem that you want to solve. This helps both you and Excel understand the problem you are facing, its constraints & objectives. Let’s see what this means below.
Building the Model for Solver
Scenario: My company is putting on a concert and I am trying to calculate the profit of the event based on the number of tickets sold, and the price of the tickets. I have some fixed costs that can’t change. These costs cover the running of the event such as lighting, staff salaries, utilities etc.
I have added my basic figures into a spreadsheet and sent it to my manager. Unfortunately, my manager tells me that we need to make a $60,000 profit. He asks me to re-jig the numbers and to make it happen!
For building any Excel Solver model, you have to keep in mind these three things.
- Decision variables: These are the variables that need to be tweaked to achieve our desired outcome.
- Constraints: These are the constraints, that govern the problem. These may be fixed costs or variable costs with an upper ceiling, etc.
- Objective: This is our desired outcome or goal.
Solving the Model with Solver:
I can use Solver to help me with this.
- From the Data tab, in the Analyze group, click Solver
The first thing I need to do is set my objective. My objective is that cell B10 (Profit) needs to be set to $60,000.
Next, I need to tell Solver which cells can change. The ‘Profit,’ ‘Revenue’ and ‘Total Costs’ cells are calculations that will change when other value changes. The ‘Costs per ticket’ cell can’t change as all the event costs have been finalized.
Therefore, the only cells that can change are the ‘Tickets sold’ and ‘Price per ticket’.
- Click Solve
Solver has calculated that in order to achieve a profit of $60,000, we need to sell 2648 tickets at $33 each.
I send these calculations to my manager. He sends them back to me and says that we cannot charge over $30 per ticket.
I need to use Solver again to recalculate but this time add in a constraint.
- From the Data tab, in the Analyze group, click Solver
- Click the Add button
I need to add a constraint that sets cell B4 to less than or equal to $30.
In effect, this constraint prevents Excel from changing the value in this cell to anything greater than $30 when performing the calculation.
- Click Solve
Solver has calculated that to achieve a profit of $60,000, where the price per ticket can’t be greater than $30, we need to sell 3000 tickets.
Suggested Reads:
How to create an Excel dashboard in five minutes
Ten ways to clean data in Excel
FAQs
Q) What is the difference between Goal Seek and Solver?
A) Some important differences between Gola Seek and Solver are:
The Solver feature in Excel allows you to add up to 200 decision variables, while the Goal Seek allows only one decision variable.
With Solver, you can find the global maximum and minimum solutions to your equation.
The Solver allows you to save a particular solution for later use.
Q) How do you solve an equation using Excel Solver?
A) To solve any equation in Excel using Solver follow these steps
Define the equation and develop the model by setting variables, constraints and objectives.
Hit the Solver button in Excel.
Set the Objective in the Objective Cell.
Select the decision variables for your problem.
Set the constraint for your variables or set global constraints.
Hit Solve.
Voila! You have successfully solved your equation in Excel using the Solver
Excel Solver—Closing thoughts
This is just a quick guide to get you started with the solver feature. This is just one (simple!) example of how to use the Solver add-in to solve problems and achieve the optimum result.
You can add more than one constraint into Solver to achieve the results you are looking for. The Solver is used to solve more complex linear programming problems.
To learn more about Solver and other advanced Excel features, check out our Excel courses section for detailed and high-quality guides.
For more information, please check out the following link:
Dummies – How to Use the Excel 2019 Solver
For more, quick Excel tips, take a look at these free resources from Simon Sez IT.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
