Excel is a widely used spreadsheet program. While it’s most popularly known for its ability to make use of heaps of data, Excel has an infinite number of capabilities, from making lists and charts to organizing and tracking information.
Learn moreCourses
- Microsoft Excel - Basic & Advanced
- Microsoft Excel - Basic
- Microsoft Excel - Advanced
- Microsoft Excel - Pivot Tables
- Microsoft Excel - Macros and VBA
- Microsoft Excel - Dashboards
- Microsoft Excel - Power Query
- Microsoft Excel - Power Pivot
- Microsoft Excel for Mac - Basic
- Microsoft Excel for Mac - Advanced
- Microsoft Excel for Mac - Basic & Advanced
- + 3 more courses
Resources
Whether you want to do XLOOKUP, COUNTIF, CONCATENATE or simply merge cells, we’ve got you covered with step-by-step guides to the most important tools, formulas, and functions in Excel.
See resourcesCertification
90% of offices use Excel. Getting Excel-certified can be highly beneficial for job seekers. Get certificates in multiple Excel courses to prove your proficiency in Excel.
See certificationMicrosoft Office
Learning Microsoft Office has significant benefits for your daily workflow, productivity, and career possibilities. Microsoft Office skills are indispensable across a plethora of industries and professions.
Learn moreCourses
- Microsoft Excel - Basic & Advanced
- Microsoft Word - Basic & Advanced
- Microsoft PowerPoint 365
- Microsoft Outlook
- Microsoft Teams Training
- Microsoft OneNote Training
- Windows 11 for Beginners
- SharePoint Online
- Microsoft Visio Training
- Microsoft Planner and Project for the Web
- Microsoft Project Fundamentals
- + 15 more courses
Resources
Looking for help with Microsoft Office? Check out our free guides with tips on how to maximize the potential of apps like Word, PowerPoint, Teams, Excel and more!
See resourcesLean Six Sigma
Lean Six Sigma provides a structured problem-solving methodology that can be used to address any type of problem. Being able to find and fix problems will improve your ability to perform in any position and industry.
Learn moreCourses
- Intro to Lean Six Sigma
- Lean Six Sigma - Yellow Belt
- Lean Six Sigma Principles - Green Belt
- Lean Six Sigma Advanced Principles - Black Belt
- Statistical Process Control
- Measurement Systems Analysis
- Hypothesis Testing
- Failure Mode and Effects Analysis
- Design of Experiments
Resources
Deepen your understanding of popular LSS tools and techniques, and simplify complex LSS concepts with our thorough how-to guides and resources.
See resourcesCertification
Lean Six Sigma certification can fast track your career and boost your pay packet. Certified Lean Six Sigma professionals are among the highest paid globally. Get trained in LSS!
See certificationFinance
Whether you are in HR, Sales, IT, Admin or Support, you need to speak the language of finance. Whether discussing plans, strategy or how well your department is performing, you’ll be conversing in financial terms and numbers.
Learn moreCourses
- Finance for Non-Financial Professionals
- Finance for Operations Managers
- Finance for Project Managers
- Finance for Operations Directors
- Financial Modeling Basics
- Financial Modeling Techniques
- QuickBooks Online
Resources
From accounting software tips, to taxes and financial modelling techniques, our resource center has free guides to help you gain the finance knowledge you are looking for.
See resourcesProject Management
Project management jobs are in high demand with not enough skilled applicants to fill them. For qualified candidates, project management roles are available in almost every industry.
Learn moreCourses
- Project Management Basics
- Project Management Bootcamp
- Project Management Framework
- Project Teams and Stakeholders
- Project Planning
- Project Performance and Delivery
- Project Management for Engineers
- Project Meetings
- Microsoft Planner and Project for the Web
- Microsoft Project Fundamentals
- CAPM Certification Training
- + 6 more courses
Resources
Explore our resource center to find templates to help you get the job done, job interview tips, insights to tackle your biggest project management challenges, and so much more.
See resourcesCertification
It pays to be certified in project management. You need to complete 35 hours of PM training from an ATP to get your certification. We can help you meet that requirement.
See certificationOffice Productivity
How successful could you be if you were more productive? Master widely-used productivity tools like Trello, Todoist, and Google Drive to optimize your workflow and spend time doing the work that truly matters. Start getting things done!
Learn moreCourses
- Keynote for Mac
- 1000minds Decision-Making
- Trello
- Google Drive & Apps
- Todoist
- Essential Productivity Training
Resources
Having difficulties adjusting to remote work? Need help with work-life balance? Need tips on how to use tools like Zoom or Slack effectively? Try these resources!
See resourcesLeadership & Management
Good leaders employ a comprehensive set of hard and soft skills to act as the oil of a well-functioning machine. While some of these traits are expressions of their inherent personality, most are learned and refined over time.
Learn moreCourses
- Leadership Training
- Team Leadership
- Deliver Effective Criticism
- Listening Skills
- Speaking Skills for Leaders
- How to Conduct a Job Interview
Resources
Finding yourself in need of simple tools and guidance to navigate through challenging situations as a leader? Take a look at our resources for management tips and strategies that you can implement right away.
See resourcesSoft Skills
Soft skills matter a lot in the workplace. Here’s why. As the workforce grows more dependent on knowledge workers, companies are beginning to see the value in soft skills.
Learn moreCourses
- Business Writing Skills
- Public Speaking
- Body Language
- Press Releases
- Customer Service Training
- Introduction to Sales
- Introduction to Marketing
- Communication Skills Basics
- Remote Work Communication Skills
- How to Prepare for a Job Interview
Resources
We want to help you succeed! Check out our resource center for help with your resume, job interviews, and your career. Let us help you put yourself ahead of the rest.
See resourcesDevelopment
Learning to code could be your ticket to a lucrative and fulfilling career. High salaries, the flexibility to work from anywhere, and a healthy job outlook are just three benefits you can look forward to in this dynamic industry.
Learn moreCourses
- Introduction to HTML
- Introduction to CSS
- Introduction to JavaScript
- Introduction to PHP
- Introduction to SQL
- Introduction to Python
- Introduction to Data Analysis with Python
- Django for Beginners
- Ruby on Rails for Web Development
- Intro to Ruby Programming
- Python with Excel
- + 1 more courses
Resources
Check out our resource center to find answers to common coding questions, interview tips, and step-by-step guides that will help you in your development career.
See resourcesDesign
If you want a job where you’ll be working on something different every day, thriving in a fast-paced environment and loving a creative challenge, a career in graphic design may be just for you.
Learn moreCourses
- Photoshop for Beginners
- Photoshop - Advanced
- Illustrator - Basic
- Illustrator - Advanced
Resources
Check out our resource guides to learn more about the graphic design tools that will help you to achieve your design dreams.
See resourcesData Analysis
Become a data analysis pro with our range of expert-led courses. Whether you are a data analytics novice or a numbers whiz, our on-demand courses will help advance your skills at a pace that suits you.
Learn moreCourses
- Introduction to SQL
- Power BI
- Introduction to Python
- Microsoft Excel - Power Query
- Microsoft Excel - Power Pivot
- Introduction to Data Analysis with Python
- Python with Excel
- Introduction to Tableau
Resources
From Python to Excel, or Power BI, Tableau and beyond, check out these free resources to help take your data analysis skills to the next level.
See resourcesSearch
- Search
- Log in
- Start free trial
- Go to dashboard
Microsoft Excel
11 minute read
How to Use Excel SolverClaudia Buckley
Join the Excel conversation on Slack
Ask a question or join the conversation for all things Excel on our Slack channel.
Join Slack channel
Solver is an Excel solution used for What-if analysis. Excel Solver is similar to Goal Seek in that it works backward to achieve a numeric objective by changing variables.
Its capabilities exceed those of Goal Seek, however. Solver allows you to use up to 200 variable cells to find an optimal value for the objective cell, as opposed to Goal Seek’s one-variable-at-a-time limitation.
Additionally, Solver’s “Constraints” parameter, adds to the tool’s sophistication, because you get to tell Excel what it is and is not allowed to do in solving for the optimal value.
Solver is an add-in that needs to be enabled within the application before you can start working with it.
How to add Solver in Excel
If the Solver add-in is already activated, you’ll see it within the Analyze group of the Data tab.
1. On the File tab, click Options.
2. Under Add-ins, select Solver Add-in and click on the Go button.
Download your free practice file!
Use this free Excel Solver file to practice along with the tutorial.
Enter your email address
How to add Solver in Excel (Mac)
- Click the Tools menu, and then click Excel Add-ins.
- In the Add-Ins Available box, select the Solver Add-In check box. Click OK.
- If Solver Add-In is not listed in the Add-Ins available box, click the Browse command to find it.
Note:
- If you get a prompt that the Solver Add-In is not currently installed on your computer, click Yes to install it, then quit Excel and restart.
How to use Solver - a case study
Solver works with variable cells to arrive at an objective, or target, subject to certain constraints. It uses the variable cells to produce the result you want in the objective cell. You can see from this that:
- The objective cell must contain a formula.
- The formula in the objective cell must be directly or indirectly dependent on the variable cells.
In our example, we run an outsourcing company that handles short-term projects for our clients. Clients give us their project objectives, scope, and deadlines and that helps us to know how many agent hours will be needed that week.
The Workforce Management (Agent Scheduling) team can use Solver to create weekly employee schedules to satisfy these objectives within whatever constraints we may have. Some possible constraints are:
- The minimum and/or maximum number of hours each employee may be scheduled daily.
- The minimum and/or maximum number of hours each employee may be scheduled weekly.
- The maximum number of overtime hours we want to schedule (i.e., wage cost factor).
Before we can begin to think about the constraints, however, what is our objective? We want to dedicate our employee resources to a certain number of hours to work on our available projects for the upcoming week.
Let’s assume that we have three weeks in which to complete these projects and 10 employees. All we need to do is spread the 1,065 hours across three weeks and distribute those hours to the 10 employees.
Cell H21 is set to calculate the total number of hours for all agents.
As the Workforce Manager, you may want to experiment with a few scenarios, such as:
- Spreading the hours evenly across three weeks.
- Maximizing agent hours this week (maybe you expect additional projects in the upcoming weeks and want to get these done as quickly as possible).
- Scheduling the maximum allowable number of employee hours without incurring overtime, and so on.
To make the cell ranges easier to work with, we’ve created named ranges as follows:
- Agt_Hrs (cells H11:H20), and
- Shifts (cell B11:G20).
Formulate the model
To get started, click on the Solver command, which opens up the Solver Parameters dialog box.
Set Objective:
This field refers to the cell which will contain a single, quantifiable result.
To Max/Min/Value of:
Decide whether you want to solve for the minimum, maximum, or for an exact value.
By Changing Variable Cells:
These are cells that Excel can change to achieve the objective. Excel Solver allows specifying up to 200 variable cells.
Now we can use the above fields to set up our problem and its solution. Our Set Objective: cell will be cell H21. Remember, this cell calculates the total number of scheduled working hours for all agents next week.
Let’s consider spreading the 1,065 hours required across approximately three weeks. This would mean scheduling 355 hours each week. Select the Value of: radio button and enter 355.
By Changing Variable cells: will be the range that we named Shifts.
Constraints
Now we can move on to the constraints — that is, what Excel is and isn’t allowed to do when trying to solve the problem. Constraints in Solver fall within the realm of:
>=
The value in this cell must be greater than or equal to ….
<=
The value in this cell must be less than or equal to ….
=
The value in this cell must be equal to ….
int
This variable must be an integer
bin
The value of this variable is binary (must be either 0 or 1)
dif
The variables in this range must be all different. No repetition is allowed.
In our case study example, we want to achieve our 355 hours without anyone having to work overtime (more than 40 hours). This is achievable since we have 10 agents. Maybe there’s a union regulation that says all employees are guaranteed a minimum of 20 hours per week. We can add these constraints by clicking Add to the right of the Solver Parameters window.
Click Add to add another constraint.
Agt_Hrs<=40
Click OK to submit these constraints and return to the Solver Parameters dialog box.
- Employee shifts should be 8 hours or less (Shifts <=8).
- Employee shifts should be whole numbers (Shifts = integer).
- We also want our Objective cell to be equal to the value in B6 (H21 = B6).
Edit a constraint
You can edit a constraint by clicking on it, then selecting Change on the right of the Solver Parameters window.
You can delete a constraint by clicking it, then selecting Delete on the right of the Solver Parameters window.
Solving methods (Excel Solver algorithms)
You’ll notice the following options for your solving methods:
Simplex LP
This method is used for linear problems. A Solver model is considered linear when the Objective cell is computed by adding together the terms of the (variable cells)*(constant) form. In linear problems, each constraint is also evaluated by adding together the terms of the (variable cells)*(constant) form and comparing the sums to a constant. When the Simplex LP Solving Method is selected, there is no limit on the number of constraints.
Generalized Reduced Gradient (GRG) Nonlinear
The GRG Nonlinear method is used for smooth nonlinear problems. If your target cell, any of your constraints, or both, contain references to changing cells that are not of the (variable cells)*(constant) form, then you have a nonlinear model.
When the GRG Nonlinear Evolutionary Solving Method is selected, there is a limit of 100 constraints, in addition to bounds and int, bin or dif restrictions on the variables.
Evolutionary
The Evolutionary solving method is used for non-smooth problems. These are the most difficult types of optimization problems to solve because some of the functions are non-smooth or even discontinuous.
We will choose the LP Simplex method for our model since this is a linear problem. If you are not sure of your problem type, you can leave it at GRG Nonlinear.
Solve!
Now we can set our Solver to work. Since we have a constraint that says that the objective cell should be equal to the value in cell B6, it will try to meet that goal whether we use the Min or Max radio button.
The optimal solution is shown below.
How to save an Excel Solver scenario
When Excel provides a solution, you may want to save your scenario and experiment with a few other options before deciding on which one to use.
- In the Solver Parameters window, click the Load/Save button.
- Excel Solver will tell you how many cells are needed to save your scenario. Select that number of empty cells and click Save.
- Excel will save your current model, which may look something like this:
How to load a saved model
To restore a saved scenario, do the following:
- In the Solver Parameters window, click the Load/Save button.
- On the worksheet, select the range of cells holding the saved model and click Load.
- In the “Load Model” dialog box, click the Replace button.
- This will open the main Excel Solver window with the parameters of the previously saved model. Click the Solve button to recalculate it.
Download the practice file
Download your free practice file!
Use this free Excel Solver file to practice along with the tutorial.
Enter your email address
Still getting the hang of Solver? Practice makes perfect! Experiment with different scenarios, and save them if you like the results or want to return to them later.
- See what happens if you remove the H21 = B6 constraint. If the radio button remains at Max, then Solver will find the optimal solution with the maximum allowed by the defined constraints.
- Adjust your constraints to make Agt_Hrs <=48, and see how you feel about the results.
- Or set the objective to a specific value, say 400, and see how that changes your model.
Everyday uses
Solver can be applied to workforce management, maximizing profitability, financial planning, and budgeting settings. Can you think of any other ways you can find optimal solutions for some of your problems using the Solver add-in?
Explore other useful Excel tools with our Excel Basic and Advanced course today!
Ready to become a certified Excel ninja?
Start learning for free with GoSkills courses
Start free trial
Loved this? Subscribe, and join 398,352 others.
Get our latest content before everyone else. Unsubscribe whenever.
Your email address
Join the Excel conversation on Slack
Ask a question or join the conversation for all things Excel on our Slack channel.
Join Slack channel
Claudia Buckley
Claudia is a project manager and business skills instructor at GoSkills. In her spare time, she reads mystery novels and does genealogy research.
Facebook Twitter LinkedIn WhatsApp Pocket Email
Recommended
Excel Challenge 23
Take this Excel challenge! How do you rank the top 5 agents when duplicate scores exist?
Recommended
Pivot Table Filter in Excel
So you've entered all your data in Excel and you want to zero in on a special segment. Pivot Table filters help to display select portions of data at your discretion.
Recommended
Handling Lookups for Multiple Criteria in Excel
Learn how to lookup multiple criteria in Excel. Analyze the best options from the FILTER function to XLOOKUP.
GoSkills courses
- GoSkills Unlimited
- Microsoft Excel
- Microsoft Office
- Lean Six Sigma
- Finance
- Project Management
- Office Productivity
Company
- About us
- Careers
- Courses for teams
- Learning management system
- Accreditations & awards
- FAQ
- Terms & Privacy
Discover
- Resources
- Scholarship
- Gifts
- Reviews
- Redeem a code
- Verify a certificate
- Site map
Contact
USA/Canada: USA/CA: +1 650 822 7732
Email: info@goskills.com Media: media@goskills.com
Facebook Twitter LinkedIn Instagram
- Send us a message
About
GoSkills - Skills for career advancement
Advance your career with GoSkills! We help you learn essential business skills to reach your full potential. Learn effectively via bite-sized video tutorials taught by award-winning instructors.
Thank you for choosing to learn with us.
Newsletter
Your email address
Don’t miss out on our best deals! Sign up for our newsletter to get the latest news and specials delivered direct to your inbox.