problem solving with excel

• Ablebits blog
• Financial functions

How to use Solver in Excel with examples

The tutorial explains how to add and where to find Solver in different Excel versions, from 2016 to 2003. Step-by-step examples show how to use Excel Solver to find optimal solutions for linear programming and other kinds of problems.

Everyone knows that Microsoft Excel contains a lot of useful functions and powerful tools that can save you hours of calculations. But did you know that it also has a tool that can help you find optimal solutions for decision problems?

In this tutorial, we are going to cover all essential aspects of the Excel Solver add-in and provide a step-by-step guide on how to use it most effectively.

What is Excel Solver?

Excel Solver belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models.

The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a linear programming solver . Apart from that, it can handle smooth nonlinear and non-smooth problems. Please see Excel Solver algorithms for more details.

How to add Solver to Excel

The Solver add-in is included with all versions of Microsoft Excel beginning with 2003, but it is not enabled by default.

To add Solver to your Excel, perform the following steps:

• In Excel 2010 - Excel 365, click File > Options . In Excel 2007, click the Microsoft Office button, and then click Excel Options .

To get Solver on Excel 2003 , go to the Tools menu, and click Add-Ins . In the Add-Ins available list, check the Solver Add-in box, and click OK .

Where is Solver in Excel?

Where is Solver in Excel 2003?

Now that you know where to find Solver in Excel, open a new worksheet and let's get started!

How to use Solver in Excel

Before running the Excel Solver add-in, formulate the model you want to solve in a worksheet. In this example, let's find a solution for the following simple optimization problem.

Problem . Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months.

Goal : Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.

And now, let's see how Excel Solver can find a solution for this problem.

1. Run Excel Solver

2. define the problem.

The Solver Parameters window will open where you have to set up the 3 primary components:

• Objective cell

Variable cells

Constraints.

Exactly what does Excel Solver do with the above parameters? It finds the optimal value (maximum, minimum or specified) for the formula in the Objective cell by changing the values in the Variable cells, and subject to limitations in the Constraints cells.

The Objective cell ( Target cell in earlier Excel versions) is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

Variable cells ( Changing cells or Adjustable cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.

In this example, we have a couple of cells whose values can be changed:

• Projected clients per month (B4) that should be less than or equal to 50; and
• Cost per service (B5) that we want Excel Solver to calculate.

The Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.

To add a constraint(s), do the following:

• Click the Add button right to the " Subject to the Constraints " box.

• In the Constraint window, enter a constraint.
• Click the Add button to add the constraint to the list.

• Continue entering other constraints.
• After you have entered the final constraint, click OK to return to the main Solver Parameters window.

Excel Solver allows specifying the following relationships between the referenced cell and the constraint.

• Less than or equal to , equal to , and greater than or equal to . You set these relationships by selecting a cell in the Cell Reference box, choosing one of the following signs: <= , =, or >= , and then typing a number, cell reference / cell name, or formula in the Constraint box (please see the above screenshot).
• Integer . If the referenced cell must be an integer, select int , and the word integer will appear in the Constraint box.
• Different values . If each cell in the referenced range must contain a different value, select dif , and the word AllDifferent will appear in the Constraint box.
• Binary . If you want to limit a referenced cell either to 0 or 1, select bin , and the word binary will appear in the Constraint box.

To edit or delete an existing constraint do the following:

• In the Solver Parameters dialog box, click the constraint.
• To modify the selected constraint, click Change and make the changes you want.
• To delete the constraint, click the Delete button.

In this example, the constraints are:

• B3=40000 - cost of the new equipment is $40,000.
• B4<=50 - the number of projected patients per month in under 50.

3. Solve the problem

After you've configured all the parameters, click the Solve button at the bottom of the Solver Parameters window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem.

Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours.

The Solver Result window will close and the solution will appear on the worksheet right away.

• If the Excel Solver has been processing a certain problem for too long, you can interrupt the process by pressing the Esc key. Excel will recalculate the worksheet with the last values found for the Variable cells.
• To get more details about the solved problem, click a report type in the Reports box, and then click OK . The report will be created on a new worksheet:

Excel Solver examples

Below you will find two more examples of using the Excel Solver addin. First, we will find a solution for a well-known puzzle, and then solve a real-life linear programming problem.

Excel Solver example 1 (magic square)

I believe everyone is familiar with "magic square" puzzles where you have to put a set of numbers in a square so that all rows, columns and diagonals add up to a certain number.

For instance, do you know a solution for the 3x3 square containing numbers from 1 to 9 where each row, column and diagonal adds up to 15?

It's probably no big deal to solve this puzzle by trial and error, but I bet the Solver will find the solution faster. Our part of the job is to properly define the problem.

With all the formulas in place, run Solver and set up the following parameters:

• Set Objective . In this example, we don't need to set any objective, so leave this box empty.
• Variable Cells . We want to populate numbers in cells B2 to D4, so select the range B2:D4.
• $B$2:$D$4 = AllDifferent - all of the Variable cells should contain different values.
• $B$2:$D$4 = integer - all of the Variable cells should be integers.
• $B$5:$D$5 = 15 - the sum of values in each column should equal 15.
• $E$2:$E$4 = 15 - the sum of values in each row should equal 15.
• $B$7:$B$8 = 15 - the sum of both diagonals should equal 15.

Excel Solver example 2 (linear programming problem)

This is an example of a simple transportation optimization problem with a linear objective. More complex optimization models of this kind are used by many companies to save thousands of dollars each year.

Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand.

Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.

Source data

Formulating the model

To define our linear programming problem for the Excel Solver, let's answer the 3 main questions:

• What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are Variable cells (B7:E8).
• What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are Constrained cells .
• What is the goal? The minimal total cost of shipping. And this is our Objective cell (C12).

To make our transportation optimization model easier to understand, create the following named ranges:

The last thing left for you to do is configure the Excel Solver parameters:

• Objective: Shipping_cost set to Min
• Variable cells: Products_shipped
• Constraints: Total_received = Ordered and Total_shipped <= Available

How to save and load Excel Solver scenarios

When solving a certain model, you may want to save your Variable cell values as a scenario that you can view or re-use later.

For example, when calculating the minimal service cost in the very first example discussed in this tutorial, you may want to try different numbers of projected clients per month and see how that affects the service cost. At that, you may want to save the most probable scenario you've already calculated and restore it at any moment.

Saving an Excel Solver scenario boils down to selecting a range of cells to save the data in. Loading a Solver model is just a matter of providing Excel with the range of cells where your model is saved. The detailed steps follow below.

Saving the model

To save the Excel Solver scenario, perform the following steps:

• Open the worksheet with the calculated model and run the Excel Solver.

• Excel will save your current model, which may look something similar to this:

Loading the saved model

When you decide to restore the saved scenario, do the following:

• In the Solver Parameters window, click the Load/Save button.

• This will open the main Excel Solver window with the parameters of the previously saved model. All you need to do is to click the Solve button to re-calculate it.

Excel Solver algorithms

When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:

• GRG Nonlinear. Generalized Reduced Gradient Nonlinear algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables. More details can be found here .
• LP Simplex . The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig. It is used for solving so called Linear Programming problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized. For more information, please check out this page .
• Evolutionary . It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing. For more information, please see this page .

This is how you can use Solver in Excel to find the best solutions for your decision problems. At the end of this post, you can download the sample workbook with all the examples discussed in this tutorial and reverse-engineer them for better understanding. I thank you for reading and hope to see you on our blog next week.

Practice workbook for download

You may also be interested in.

• Using Excel Goal Seek for What-If analysis
• Excel Copilot with examples
• Linear regression analysis in Excel
• Microsoft Excel formulas with examples
• How to use VLOOKUP & SUM or SUMIF functions in Excel

Table of contents

How to Use Solver in Microsoft Excel

Last Updated: July 28, 2022 Tested

Enabling Solver

Analyzing and solving.

This article was co-authored by wikiHow staff writer, Jack Lloyd . Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. The wikiHow Tech Team also followed the article's instructions and verified that they work. This article has been viewed 619,426 times. Learn more...

This wikiHow teaches you how to use Microsoft Excel's Solver tool, which allows you to alter different variables in a spreadsheet in order to achieve a desired solution. You can use Solver in both Windows and Mac versions of Excel, though you'll have to enable Solver before you can begin using it.

• Solver comes pre-installed with both Windows and Mac versions of Excel, but you'll have to enable it manually.

• If you have an existing Excel file you'd like to use Solver with, you can open it instead of creating a new file.

• On a Mac, click Tools instead, then skip the next step.

• On a Mac, click Excel Add-ins in the Tools menu.

• On a Mac, this window will open after clicking Excel Add-ins in the Tools menu.

• For example, you might create a spreadsheet documenting your various expenses over the course of a month with the output cell resulting in your money left over.
• You can't use solver on a spreadsheet which doesn't have solvable data (i.e., your data has to have equations).

• For example, if you're creating a budget where the end goal is your monthly income, you would click the final "Income" cell.

• For example, if your goal is to have $200 at the end of the month, you would type 200 into the text box.
• You can also check either the "Max" or "Min" box in order to prompt Solver to determine the absolute maximum or minimum value.
• Once you've set a goal, Solver will attempt to meet that goal by adjusting other variables in your spreadsheet.

• Click the cell (or select the cells) for which the constraint applies.
• Select a type of constraint from the middle drop-down menu.
• Enter the constraint's number (e.g., a maximum or minimum).

• If you do like your Solver's results, you can apply them to your spreadsheet by checking the "Keep Solver Solution" box and then clicking OK .

Expert Q&A

• Solver is best used for problems such as scheduling employees, determining the lowest price for which you can sell items while meeting a financial goal, and budgeting. Thanks Helpful 1 Not Helpful 0

• Solver cannot be used in spreadsheets in which there is no "output" or actual solution. For example, you can't apply solver to a spreadsheet which has no equations. Thanks Helpful 1 Not Helpful 1

You Might Also Like

• ↑ https://support.office.com/en-us/article/Load-the-Solver-Add-in-in-Excel-612926fc-d53b-46b4-872c-e24772f078ca#OfficeVersion=Windows
• ↑ https://www.youtube.com/watch?v=dRm5MEoA3OI

About This Article

1. Enable Solver in the "Add-ins" section of your Excel preferences if necessary. 2. Open a spreadsheet with data you want to analyze. 3. Click Data , then click Solver . 4. Select a cell to use from the "Set Objective" field. 5. Check the "Value Of" box, then enter a desired value. 6. Click Solve . Did this summary help you? Yes No

• Send fan mail to authors

Is this article up to date?

Featured Articles

Trending Articles

Watch Articles

• Terms of Use
• Privacy Policy
• Do Not Sell or Share My Info
• Not Selling Info

wikiHow Tech Help:

Tech troubles got you down? We've got the tips you need

How to Use Microsoft Excel Solver – A Comprehensive Guide

Microsoft Excel Solver is a powerful tool that can help you solve complex problems by finding the optimal solution. It’s like having a personal assistant that can crunch numbers and find the best answer to your question. Whether you’re trying to figure out the most cost-effective way to ship products or how to allocate resources in a project, Solver can help. All you need is a basic understanding of Excel and a clear idea of the problem you want to solve.

Step by Step Tutorial: How to Use Microsoft Excel Solver

Before we dive into the steps, let’s briefly talk about what we’re going to achieve. We’ll set up a problem in Excel, define our objective, set constraints, and let Solver find the best solution. Ready? Let’s go!

Step 1: Open Solver

Open Solver from the Data tab in Excel.

Solver is not displayed by default in Excel. If you don’t see it, you may need to enable it from the ‘Add-ins’ options.

Step 2: Set Your Objective

Select the cell with the formula you want to optimize.

This is what Solver will work towards improving, whether it’s maximizing profit, minimizing cost, or achieving a specific value.

Step 3: Define the Variable Cells

Choose the cells that Solver can change to reach the objective.

These are usually the inputs or decision variables of your problem that you’re willing to adjust.

Step 4: Add Constraints

Click ‘Add’ to set constraints for the variable cells.

Constraints are the rules that your solution must follow, like not exceeding a budget or ensuring a minimum production quantity.

Step 5: Solve the Problem

Press the ‘Solve’ button and let Solver do its magic.

Solver will run various calculations to find the optimal solution within your set constraints.

After completing these steps, Solver will present you with a solution. If it finds an optimal solution, it will ask if you want to keep the new values or restore the original ones. If no solution is found, it will let you know that as well.

Tips for Using Microsoft Excel Solver

• Always start with a clear understanding of the problem you want to solve.
• Make sure your objective function and constraints are correctly set up in Excel.
• Use the ‘Evolutionary’ solving method for problems that are not linear.
• If Solver is taking too long to find a solution, try simplifying the problem or constraints.
• Remember to save your work before running Solver, just in case.

Frequently Asked Questions

What is microsoft excel solver used for.

Solver is used to find the optimal solution for complex problems in Excel, such as resource allocation, budgeting, and scheduling.

Can Solver handle non-linear problems?

Yes, by using the ‘Evolutionary’ solving method, Solver can handle non-linear problems.

Is Solver available in all versions of Excel?

Solver is available in most versions of Excel, but it might need to be enabled from the ‘Add-ins’ menu.

What do I do if Solver can’t find a solution?

Try checking your constraints and objective function for errors, or consider simplifying the problem.

Can Solver handle multiple objectives?

No, Solver is designed to handle one objective at a time. However, you can run Solver multiple times with different objectives.

• Open Solver from the Data tab.
• Set your objective by selecting the target cell.
• Define the variable cells.
• Add constraints for the variable cells.
• Solve the problem.

In essence, Microsoft Excel Solver is like a Swiss Army knife for problem-solving. It’s an indispensable tool that can take your Excel skills to a whole new level, helping you make data-driven decisions efficiently. With the step-by-step tutorial you’ve just gone through, you should now feel confident in tackling problems that seemed daunting before. Remember, like any tool, practice makes perfect. So, why not challenge yourself with a real-world problem you’ve been wrestling with and let Solver come to the rescue? And if you ever find yourself in a tough spot, just come back to this article for a quick refresher on how to use Microsoft Excel Solver. Happy solving!

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Read his full bio here.

Share this:

Related posts:.

• How to Make All Columns the Same Width in Excel 2013
• How to Use Variables in Excel: A Step-by-Step Guide
• How to Merge Cells in Word 2016 Tables
• How to Use Excel’s Functions to Easily Solve Systems of Equations
• How to AutoFit All Columns in Excel 2013 (An Easy 4 Step Guide)
• How to Merge Cells in Excel 2010
• How to Calculate Percentage in Excel
• How to Subtract in Excel 2013 with a Formula (An Easy 4 Step Guide)
• How to Color Cells in Excel
• How to Use the AutoFit Excel Shortcut for Columns and Rows
• What Are Three Ways to Change Column Width in Excel?
• Can I Convert a Microsoft Word Table to Excel?
• How to Remove First Character in Excel 2013 Cell
• How to Do a Multivariate Regression in Excel: 2024 Guide
• How to Expand All Rows in Excel 2013
• How to Combine Three Columns Into One in Excel
• Microsoft Excel Skills to Know When Job Hunting
• How to Enlarge Cells in Excel 2010
• How to Expand Cells Horizontally in Excel 2013
• How to Merge Cells in Google Sheets

15 Excel Formulas That Will Help You Solve Real Life Problems

Excel isn't only for business. Here are several Microsoft Excel formulas that will help you solve complex daily problems.

A lot of people view Microsoft Excel as a tool that's only useful in business. Truth is, there are a lot of ways it can benefit you at home as well. The key to finding uses of Excel in daily life is picking the right formulas that solve problems.

Whether you're shopping for a new car loan, want to figure out which mutual fund investment is best for you, or if you're just trying to make sense out of your bank account, Excel is a powerful tool that can help.

We picked out 15 formulas that are simple, powerful, and help you solve complex issues.

Financial Formulas

Shopping for a new home and confused by all the mortgage lingo? Looking for a new car and getting confused by the car loan terms the salesperson keeps throwing at you?

Have no fear. Before you take out a loan, do your research with Excel by your side!

1. PMT---Payment

Whenever you're comparing any loan terms and want to quickly figure out your actual monthly payment given different variations in terms, take advantage of the powerful (and simple) PMT formula.

Here's what you need to use this formula:

• The interest rate of the loan
• The term of the loan (how many payments?)
• The starting principle of the loan
• Future value, if for some reason the loan will be considered paid off before it reaches zero (optional)
• Type of loan---0 if payments due at the end of each month, or 1 if they're due at the beginning (optional)

Here's a cool way to quickly compare a variety of loans to see what your payments will look like. Create an Excel sheet that lists every potential loan and all available information about them. Then, create a "Payments" column and use the PMT formula.

Just grab the lower right corner of the PMT cell you just created, and drag it down so it calculates the payment total for all the loan terms listed in the sheet. The Excel autofill feature is one feature you will use a lot with these tricks.

Now you can compare monthly payments for different kinds of loans.

(A very big thank you to Mark Jones (@redtexture on Twitter) who pointed out that for PMT and FV formulas, you've got to be very careful about using the same period---in this case using monthly payments requires dividing the interest term by 12 months)

This is why our readers are so great. Thanks for helping with this fix Mark!

2. FV---Future Value

The next formula comes in handy when you are looking to invest some money into something like a Certificate of Deposit (CD), and you want to know what it will be worth at the end of the term.

Here's what you need to know to use the FV formula :

• Number of payments (or investment term in months)
• The payment for each period (usually monthly)
• Current starting balance (optional)

So let's compare several CDs using the terms that you know from the information the banks have given you. In the example below, let's say you have a $20,000 inheritance to invest in a CD.

Interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100). Payments are zero because CD's are typically based on a starting value and a future value paid out.  Here's what the comparison looks like when you use the FV formula for every CD you're considering.

Without a doubt, the higher interest CD over a longer period of time pays out much more. The only drawback is that you can't touch any of your money for three whole years, but that's the nature of investing!

3-4. Logical Formulas---IF and AND

Most banks these days give you the ability to download nearly a year's worth of bank transactions to a format like CSV. This is a perfect format to analyze your spending using Excel, but sometimes the data you receive from banks is very disorganized.

Using logical formulas are a great way to spot overspending.

Ideally, the bank either automatically categorizes your spending or you've set up your account so that things are placed into spending categories. For example, any restaurants we go to get labeled with the DiningOut label.

This makes it easy to use a logical formula to identify whenever we've gone out to eat and spent over $20.

To do this, just create a logical formula in a new column looking for any value where the category column is "DiningOut" and the transaction column is larger than -$20

Note: The comparison below shows "<", less than, because the values in column C are all negative.

Here's what that looks like:

Using IF and AND together in one formula looks tricky, but it's actually quite simple. The IF statement will output the dollar amount (C2) if the AND statement is true, or FALSE if it isn't. The AND statement checks whether the category is "DiningOut" and the transaction is greater than $20.

There you have it! Without having to manually sift through all those transactions, you now know exactly those times when you've overspent in a certain category.

Making Sense of Lists

Lists are a big part of everyday life. If you're managing a household, you're using lists constantly. Excel has some pretty powerful tools for being productive with checklists , as well as other kinds of list formats.

5-6. COUNT and COUNTIF

Excel can help you quickly organize and sort values is a list.  Let's take the PTC example. Here's a list of donations from community members.

We want to see how many times a person's name shows up on the list. To do this, you can combine the COUNT formula with an IF formula. First, create a column to check if the person is Michelle or not. The formula will use an IF statement to fill the cell with a "1" if this is true.

Next, create another column that counts how many times you've found Michelle Johnson on the list.

This gives you the count of every place in Column E where there's a 1 rather than a blank.

So, this is the simplest way to do this kind of thing, but it does require two steps.

6-8. SUMIF, COUNTIF, AVERAGEIF

If you don't mind using a slightly more advanced formula, you might consider using one of the many combined "IF" formulas like SUMIF , COUNTIF , or AVERAGEIF . These allow you to perform the formula (COUNT, SUM or AVERAGE) if the logical condition is true. Here's how it works using the above example.

This formula looks at column A, which contains all the donor names, and if the cell within the range matches the criteria in quotes, then it counts up by one. This gives you a count of all the times the donor name equals "Michelle Johnson" in a single step.

It's much faster than using two columns, but is a little complex - so use the approach that works best for your situation.

The  SUMIF  and  AVERAGEIF  formulas work the very same way, just with different mathematical results. Using  SUMIF  in this example would give you the total donation dollars for Michelle Johnson if you use it instead.

Another formula that you can use creatively sometimes is the LEN formula. This formula is one of many Excel text formulas that tells you how many characters are in a string of text.

One interesting way to use this in the example above would be to highlight donors who donated over $1,000 by counting the number of digits in the donation column. If the length of the number is 4 or greater, then they donated at least $1,000.

Now you can add additional formatting to make it easier on the eyes.

To do this, you need to highlight all the cells in the Donation column, select the Home tab in the menu, and click on Conditional Formatting  in the toolbar. Then select Use a formula to determine which cells to format .

Set the range under Format values where this formula is true: to the column/range where all your LEN formula outputs are displayed.

In this example, if you make the condition ">3", then anything over $1,000 will receive the special formatting. Don't forget to click the Format... button and choose what kind of special formatting you want for these.

Also, a quick note. You'll notice my range is defined as "$E2:$E11", not "$E$2:$E$11". When you select the range, it defaults to the former, which won't work. You need to use relative addressing as shown in the picture above. Then, your conditional formatting will work based on the condition of the second range.

Organizing Bank and Financial Downloads

Sometimes, when you download information from businesses---whether it's your bank, or your health insurance company, the format of the incoming data doesn't always match what you need it to be.

For example, let's say that in the exported data from your bank and you're given the date in the standard format.

If you want to add a new column of your own with your own that's prefaced by the year and includes the Payee information (for your own sorting purposes), extracting pieces of information from a column is really easy.

10-14. RIGHT, LEFT, TEXT, and CONCATENATE

You can pull the year out of the text in that column using the RIGHT formula.

The formula above is telling Excel to take the text in column D and extract the four characters from the right side. The CONCATENATE formula pieces together those four digits, with the Payee text from column E.

Keep in mind that if you do want to extract text from a date, you will need to convert it to text format (instead of date) using the "= TEXT (D2,"mm/dd/yyyy")" formula. Then you can use the RIGHT formula to pull out the year.

What if your information is on the left? Well, instead use the  LEFT  formula and you can pull text from left to right.

CONCATENATE really comes in handy when you have some text from a bunch of different columns that you want to piece together into one long string. You can find out more in our guide on how to combine Excel columns .

There are also a few ways to separate text in Excel if you want to want to learn how to fully manipulate strings.

Picking Random Names from a Hat

15. randbetween.

One last fun formula is one you may use if you have to do something like pick some names out of a hat for a Christmas party. Put that hat and those scraps of paper away and instead pull out your laptop and launch Excel!

Using the formula RANDBETWEEN, you can have Excel randomly select a number between a range of numbers you specify.

The two values you need to use are the lowest and highest numbers, which should be at the ends of the range of numbers you've applied to each person's name.

Once you hit the Enter key, the formula will randomly select one of the numbers within the range.

It's about as random and tamper-proof as you can possibly get. So instead of picking a number from a hat, pick a number from Excel instead!

Using Excel for Everyday Problems

As you can see, Excel isn't just for data-analysis gurus and business professionals. Anyone can benefit from the many formulas that you'll find tucked away in Excel . Learn these formulas and you can start solving real-life problems in Excel.

Don't stop learning Excel. There is a long list of Excel formulas and functions that you can learn to use, you might find some neat little tricks you never thought Excel could do.

Image credit:  Goodluz via Shutterstock.com

#1 Excel tutorial on the net

Load the Solver Add-in | Formulate the Model | Trial and Error | Solve the Model

Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

Load the Solver Add-in

To load the solver add-in, execute the following steps.

1. On the File tab, click Options.

2. Under Add-ins, select Solver Add-in and click on the Go button.

3. Check Solver Add-in and click OK.

4. You can find the Solver on the Data tab, in the Analyze group.

Formulate the Model

The model we are going to solve looks as follows in Excel.

1. To formulate this linear programming model, answer the following three questions.

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total profit of the three products, so the objective is to maximize this quantity.

2. To make the model easier to understand, create the following named ranges .

3. Insert the following three SUMPRODUCT functions.

Explanation: The amount of capital used equals the sumproduct of the range C7:E7 and OrderSize. The amount of storage used equals the sumproduct of the range C8:E8 and OrderSize. Total Profit equals the sumproduct of UnitProfit and OrderSize.

Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we order 20 bicycles, 40 mopeds and 100 child seats, the total amount of resources used does not exceed the amount of resources available. This solution has a total profit of 19000.

It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution.

Solve the Model

To find the optimal solution , execute the following steps.

1. On the Data tab, in the Analyze group, click Solver .

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalProfit for the Objective.

3. Click Max.

4. Enter OrderSize for the Changing Variable Cells.

5. Click Add to enter the following constraint.

6. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

7. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to order 94 bicycles and 54 mopeds. This solution gives the maximum profit of 25600. This solution uses all the resources available. Try it yourself. Download the Excel file and find the optimal solution.

1/8 Completed! Learn much more about the solver > Go to Next Chapter: Analysis ToolPak

Learn more, it's easy

• Transportation Problem
• Assignment Problem
• Shortest Path Problem
• Maximum Flow Problem
• Capital Investment
• Sensitivity Analysis
• System of Linear Equations

Download Excel File

• solver.xlsx

Next Chapter

• Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Solver • © 2010-2024 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

Excel Exercises helps regular people learn Excel as quickly as possible.

Becoming the "spreadsheet wizard" of the office used to require years of industry experience and endless hours of watching excel training videos and tutorials online. excel exercises is the new method to learn excel that's faster, easier, and a lot more fun., registered users, countries with excelexercises users, interactive practice questions answered, discover a better way to learn excel, welcome to the fun, hands-on way to learn excel my name is jake and i'm known as a spreadsheet wizard around my office. but it wasn't a fast or easy process to get here, a few years ago i had to learn excel for work. i found excel, with all of its features and functionality, completely overwhelming., i paid for a video tutorial course, but found myself zoning out and daydreaming through the videos. and without putting the techniques from the videos into practice, i'd forget the techniques right after watching., i watched several hours worth of excel training videos, but found i didn't really master a formula until i actually got my hands dirty and used the formula at work. it took me a few years of working in finance and consulting (using excel nearly every day) to internalize all the keyboard shortcuts and functions., i wondered why there wasn't an easier , faster , more engaging way to learn excel, i stumbled across your website with excel exercises, and it's seriously an amazing resource [...] i seriously believe your platform proves to be one of the most efficient ways to learn excel., i finished up all the lessons, they really helped me understand excel logic better. can't wait for more, when i first started the practice exercises at your site i just found it really acclerated my comprehension of excel. i started to understand it so much better than any other prior learning platform that i had visited..

Ready to get started?

This is excel practice for normal people.

Practice Excel the right way - in small, bite-sized, engaging lessons - rather than falling asleep reading a lengthy article or daydreaming through a long video. This is Excel practice for those who learn by doing, not by watching. There's a reason thousands of people are choosing to learn Excel with ExcelExercises: because it works.

Each lesson is designed to keep you engaged while walking you through new concepts, so that you can't advance if you're not absorbing the information. You literally can't zone out! That's just one of the ways Excel Exercises helps you learn more efficiently and maximize your practice time.

Excel Exercises Solves the "Boring Video" Problem

We walk you through all the Excel functions that you need to know, forcing you to type through practice exercises to get hands-on and commit them to memory. You'll also practice keyboard shortcuts on your own keyboard to build muscle memory and get faster at manipulating spreadsheets. Through hands-on repetition and smart skills targeting, I've distilled all the skills I've learned from years of working with Excel to a program that can be completed in a matter of days.

Have Fun While You Learn With Excel Exercises

These practice exercises aren't just engaging; they are actually fun. score points by answering questions correctly and advance through the levels as you learn, rather than relying on boring memorization. you'll start by practicing some easy skills and work your way up to practicing more advanced techniques. by gradually introducing new concepts for you to practice, we make it easy to learn all the techniques you need to become an excel master. whether you're searching for easy excel practice exercises or more advanced formula practice, excel exercises offers a fun learning experience for all skill levels - it doesn't even feel like learning, thousands of people have already used excel exercises to practice excel skills and advance their careers. solid excel skills are critical for most finance, accounting, consulting, and other data-oriented jobs. and let's be honest - if your shortcut game is on point you'll impress anyone watching over your shoulder. learn excel the fun way today and get your career moving in the right direction., use excel exercises to excel-erate your career, almost any job in finance, accounting, data science, consulting, or any other quantitative industry will require you to use spreadsheets. by mastering excel you can give yourself an edge by completing your work faster and better than your peers. many jobs today also require an excel test as part of the application and interview process. whether you're applying for an internship or you're already an executive, excel mastery is a tangible and noticeable skill that can help you get the offer, earn more money, and make yourself indispensable., today's job market demands strong spreadsheet skills like never before. countless jobs from simple data entry to advanced data analysis, data science, and artificial intelligence (ai) will require proficiency with excel in order to pass the interview process and perform with the speed and quality necessary to thrive in your new job. luckily, there are now more resources than ever available to help you learn excel online. excel exercises is the first web-based excel practice resource that simulates real excel practice exercises right in your browser. let excel exercises help you master excel and get that new job or promotion., hands-on exercises.

Most people can't learn new skills simply by watching. There's a reason you hear that the best way to learn Excel is just to use it at work for a few years. But when you don't have years to learn Excel, your best option is the curated simulations offered by Excel Exercises. We walk you through new skills and let you write the actual formulas and tap out the keyboard shortcuts to build muscle memory and learn by doing. By focusing on one skill at a time, you'll get enough reps to commit the skill to memory before moving on.

Excel Exercises for Beginners and Experts

Whether you already have some Excel experience or you've never written a "sum" function in your life, this site will teach you the tools to stand out at work as the Excel Master. It starts easy with simple functions to build a solid foundation. Then it becomes more challenging as the lessons incorporate new concepts, shortcuts, and advanced functions to build your skills, boost your efficiency, and expand on what you thought was possible with Excel. Whether you're a beginner or advanced, there is always a new Excel skill you can learn.

Practice And Learn Excel Online For Free

Welcome to Excel Practice Online!

Now you can practice Excel everywhere! You can even practice on your mobile phone!

Every function and tool has an explanation followed by an online excel exercise which can be solved within the page itself, no need to download anything – All thanks to the amazing powers of Excel Online!

The tutorials are sorted from beginner level to advanced level. If you like this site please share it with your friends! 🙂

Tip for mobile phone users – tap twice on the cell you want to edit in order to edit it.

• Free Excel Courses and Resources
• Excel Self-Assessment Tool
• Free Excel Online Exercises
• Excel Basics – Zero to Hero
• Excel Tests
• Top 10 formulas and functions in Excel
• Vlookup – Tutorial with Example and Exercise Sheet
• Pivot Tables Tutorial
• Excel Shortcuts – Windows and Mac
• HOT! – Excel Mortgage Calculator – Calculate your mortgage payments and get the payment schedule for the entire period of the loan – Step-by-step tutorial on how to build a Mortgage Calculator in Excel.
• New! Excel Online Cheat Sheet – A Quick Guide To Excel’s Web Version
• Can’t find what you’re looking for? Suggest a tutorial here!
• Excel Basics – Start here if you are new to Excel! Learn how Excel works, how to perform basic calculations, and how to use cell references to save time and increase efficiency!
• Addition (Plus)
• Subtraction (Minus)
• Multiplication
• Excel Shortcuts for Windows – Master Excel Shortcuts to save time and increase efficiency!
• Excel Shortcuts for Mac – Learn how to make the most of Excel on your Mac!

Formulas/Functions

• SUM function – Sum multiple values in Excel
• MAX – find the maximum value in a range
• MIN – find the minimum value in a range
• COUNT – Count numeric values in a range
• COUNTA – Count numeric and textual values
• AVERAGE – Calculate average of a range
• Filtering in Excel – Learn how to filter your data using Excel’s Filter Tool
• Excel Sort – Learn how to sort your data in Excel.
• Flash Fill – Excel’s hidden gem for auto-completing data based on a pattern
• Remove Duplicates – Remove duplicate values in a single column or multiple columns!

Intermediate

Conditional.

• IF function – check if a condition is met
• NESTED IF – Multiple if conditions
• Conditional Formatting – Format Excel Cells based on criteria
• COUNTIF – Count cells in range which meet a certain criteria
• SUMIF – Sum range based on criteria
• AVERAGEIF – Calculate the average of a range based on criteria
• SUMIFS – Sum cells using multiple criteria
• COUNTIFS – Count cells using multiple criteria
• MAXIFS – Find maximum value in a range based on criteria
• MINIFS – Find minimum value in a range based on criteria
• AND/OR – Check if multiple criteria are met (Works great when combined with an IF function!)
• ISBLANK – Check if a cell is blank or not
• VLOOKUP – lookup value and return corresponding value from a table
• HLOOKUP – lookup value and return corresponding value from a table
• Hot!!! XLOOKUP – Excel’s next generation lookup function which combines the best features from VLOOKUP, INDEX MATCH, HLOOKUP and IFERROR/IFNA

Pivot tables

• Pivot Table – Quickly Analyze and Summarize your data using Excel’s most powerful tool!

Text Formulas

• LEFT, MID, RIGHT – Basic Text Functions
• HOT! – TEXTBEFORE & TEXTAFTER – Extract text before or after a delimiter using Excel’s brand new powerful functions!
• HOT! – TEXTSPLIT – Split your text into multiple cells using this super powerful new function!
• TEXTJOIN – Easily combine multiple cells using delimiter
• CONCAT – Combine range of cells without delimiter
• CONCATENATE – Combine two cells or more into one cell
• LEN – Find the length of a cell
• FIND – Find the position of a text within another text (Case-sensitive)
• SEARCH – Find the position of a text within another text (Case-insensitive)
• SUBSTITUTE – Replace text with another text in a cell/expression
• TRIM – Remove extra spaces from the text
• LOWER, UPPER, PROPER – Convert text to lowercase, uppercase and proper case
• VALUE – Convert data stored as text into values
• TEXT – Convert and format numbers into text
• Text to Columns – Quickly split a column into multiple columns using a delimiter. Bonus – Quickly change date formats or convert text to numbers!
• FORMULATEXT – display a formula in another cell as text

Date functions

• DAY, MONTH, YEAR – Extract day, month and year from a date in Excel
• DATE – Create a date from individual values
• WEEKDAY – Return the number of the day of the week
• EOMONTH – Return the date of the last day of the month based on a specific date

Index & Match lookup

• INDEX – Retrieve cell in nth position in a range
• MATCH – Find position of value in a range
• INDEX MATCH – Just like VLOOKUP, only better.

Other advanced tools

• SUMPRODUCT – Sum the products of Excel ranges
• Excel Wildcards – Advanced searching and matching in Excel
• Advanced Filter – Filter by multiple criteria in the same column, or even in different columns!

Power Query

• Combine data from multiple Excel workbooks using Power Query
• Column from Examples tool – Learn the secret to mastering Power Query without any prior knowledge!
• Unpivot columns easily using Power Query

Secret Excel Functions

This section covers Excel functions that are not available in most of Excel’s versions. These functions will unlock a new set of capabilities such as fining only unique values, sorting, and filtering – the tutorials below will help you with mastering Excel’s new functions!

• UNIQUE – Extract unique values from a range
• SORT Function – Sort range dynamically
• SORTBY – Sort range dynamically by using another range
• FILTER Function – Filter range by specific criteria
• RANDARRAY – Create an array of random numbers 
• SEQUENCE – Create a range of sequential values
• LET – Assign values and calculations to names to improve your formula’s ease of use, readability, and performance!
• HOT! – LAMBDA – The mother of all functions that will help you create amazing and powerful custom functions for your own need! 
• VSTACK – Vertically stack arrays/ranges in Excel
• HSTACK – Horizontally stack arrays/ranges in Excel
• CHOOSEROWS – Return specific rows from a range or array
• CHOOSECOLS – Return specific columns from a range or array
• TOROW – Convert a range/array into a single row
• TOCOL – Convert a range/array into a single column

Financial Functions

Learn how to use Excel to make financial calculations!

• Excel Financial Calculator – quickly calculate PV, FV, PMT, NPV, IRR
• PMT – Calculate the periodic payment amount of a loan, mortgage, or another financial instrument
• PPMT & IPMT – Find the Principal and Interest portion of a certain payment
• PV – Find the Present Value of a loan, mortgage, or any other financial instrument 

Excel Macros – VBA (Visual Basic for Applications)

• Start here – How to run your first VBA Macro in Excel without knowing VBA? 

Excel Data Sheets for Practice

Want to do some freestyle practice? Create your own Excel playground with our blank excel Worksheet!

• Excel-Online Blank Worksheet
• Excel Practice Data

How to Calculate in Excel – Excel-Online Calculators

• How to Calculate GPA in Excel
• How to Calculate BMI in Excel
• How to Calculate Density in Excel
• How to Calculate Weighted Average in Excel

Terms and Conditions - Privacy Policy

Load the Solver Add-in in Excel

The Solver Add-in is a Microsoft Office Excel add-in program that is available when you install Microsoft Office or Excel.

To use the Solver Add-in, however, you first need to load it in Excel.

In Excel 2010 and later, go to File > Options

Click Add-Ins , and then in the Manage box, select Excel Add-ins .

In the Add-Ins available box, select the Solver Add-in check box, and then click OK .

 If the Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.

If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.

After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data tab.

On the Tools menu, select Excel Add-Ins .

In the Add-Ins available box, select the Solver Add-In check box, and then click OK .

If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.

If you get a prompt that the Solver add-in is not currently installed on your computer, click Yes in the dialog box to install it.

After you load the Solver add-in, the Solver button is available on the Data tab.

The Solver add-in from Frontline Systems isn't currently available for Excel on mobile devices.

Solver is a free add-in for Excel 2013 with SP1 and later. For more information, search for Solver in the Office Store .

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Define and solve a problem by using Solver

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Keyboard shortcuts in Excel

Excel functions (alphabetical)

Excel functions (by category)

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

• Get started with computers
• Learn Microsoft Office
• Apply for a job
• Improve my work skills
• Design nice-looking docs
• Getting Started
• Smartphones & Tablets
• Typing Tutorial
• Online Learning
• Basic Internet Skills
• Online Safety
• Social Media
• Zoom Basics
• Google Docs
• Google Sheets
• Career Planning
• Resume Writing
• Cover Letters
• Job Search and Networking
• Business Communication
• Entrepreneurship 101
• Careers without College
• Job Hunt for Today
• 3D Printing
• Freelancing 101
• Personal Finance
• Sharing Economy
• Decision-Making
• Graphic Design
• Photography
• Image Editing
• Learning WordPress
• Language Learning
• Critical Thinking
• For Educators
• Translations
• Staff Picks
• English expand_more expand_less

Excel Formulas  - Solving Real-Life Problems in Excel

Excel formulas  -, solving real-life problems in excel, excel formulas solving real-life problems in excel.

Excel Formulas: Solving Real-Life Problems in Excel

Lesson 6: solving real-life problems in excel.

/en/excelformulas/functions/content/

Solving real-life problems in Excel

Excel can be used to solve all kinds of real-life problems. But how do you turn those problems into formulas that Excel can understand? All it takes is a little bit of planning (and some basic math). To learn more, check out the video below!

/en/excelformulas/doublecheck-your-formulas/content/

Excel’s Solver Feature – A Powerful Problem-Solving Tool!

Excel's Solver Feature - A Powerful Problem-Solving Tool!

Excel's solver feature - a powerful problem-solving tool.

Excel’s Solver feature is a powerful tool that allows users to find optimal solutions to complex problems. Solver is an add-in tool you can install in Microsoft Excel, and it uses mathematical algorithms to determine the best possible values for a given set of constraints. Solver is particularly useful for optimization problems, where the goal is to maximize or minimize a particular objective subject to defined constraints. For example, this tool can assist with issues like finding the optimal production schedule for a manufacturing plant, determining the best allocation of resources for a project, or maximizing profits for a business.

Working With Solver

Excel’s Solver feature uses an iterative process to search for the best solution to an optimization problem. It does this by changing the values of the decision variables and evaluating the objective function at each iteration. The objective function is the mathematical function that describes the problem you are solving.

Solver - Step By Step

To use Solver, first ensure it is available in your instance of Excel. If it is, you will access it on the Ribbon’s Data tab, likely near the right margin. If you do not see it on the Data tab, click File , Options , and Add-ins . Then, click Go near the bottom of the window to manage Excel add-ins . Finally, check the box next to Solver Add-in near the bottom of the window to activate the tool.

The next step is identifying the problem and defining the objective function and any relevant constraints . The objective function is the function that needs to be optimized, and it can be any mathematical equation that represents the goal of the problem. The constraints are the limitations or requirements inherent to the model.

Upon defining the problem, you are ready to use the optimization tool to find its solution. To do so, click Solver on the Ribbon’s Data tab. Next, specify the target cell , which is the cell that contains the objective function. Then, indicate the variables to adjust to find the optimal solution and the constraints in place. Constraints are any limitations that you must factor into the equation. For instance, if you’re using Solver to optimize the results of a capital budgeting problem, the total amount of capital available for investment would be a constraint.

Solver uses mathematical algorithms to find the best possible values for the variables to optimize the objective function. When doing so, it takes into account all specified constraints. You can choose from various Solver methods, including Simplex LP, GRG Nonlinear, and Evolutionary, depending on the complexity of the problem.

Solver's Advantages

One of Solver’s primary benefits is that it can handle nonlinear problems which are difficult or impossible to solve using traditional optimization techniques. Nonlinear problems involve functions that are not linear, and they can be much more complex than linear problems. Solver uses advanced algorithms to handle these problems and find the best solutions.

Another advantage of Solver is that it can handle multiple constraints simultaneously. This feature allows you to model complex systems and find optimal solutions that meet all the requirements. Solver also enables users to set different levels of precision and tolerances, which can help fine-tune the results.

A Downside To Solver

One potential downside of the tool is that it can be time-consuming to set up and run, especially for complex problems. For example, you may need to spend significant time defining the objective function and constraints and selecting the appropriate solver method and parameters. Additionally, Solver may not always find the global optimum solution, particularly for nonlinear problems. However, despite these potential drawbacks, Solver remains a valuable tool for solving optimization problems in Excel.

Solver In Action

To illustrate the tool’s power, consider the following example. Suppose you have a customer or client who sent a payment to you for $14,663.10, but they did not provide the remittance advice. Further, suppose the customer has forty-two outstanding invoices with your company, meaning trying to guess which combination of invoices totals $14,663.10 would be very time-consuming, at best. Instead, you could create a Solver model to identify which combination(s) of invoices sum to $14,663.10.

To build your model, perform the following steps.

• List all the invoice numbers in a single column in Excel.
• Enter each outstanding invoice amount in an adjacent column.
• Enter “0” in all the cells in another adjoining column.
• Add a formula in another adjacent column multiplying the invoice amount by the cell containing the zeroes.
• Create a cell to record the payment amount your customer sent you.
• Also, create a cell that sums all the amounts of the values in the column created in Step 4 above.

Upon completing the steps above, your spreadsheet should resemble that shown in Figure 1 .

Click Solver on the Ribbon’s Data tab. Again, if you do not see Solver on that tab, the most likely cause is that you need to activate the add-in. Upon clicking Solver, Excel opens the Solver Parameters dialog box pictured in Figure 2 .

Near the top of the dialog box, indicate that you want to set cell B3 equal to the specific value of $14,663.10 by changing cells C6 through C47. Further, specify three constraints, as shown below.

• Cells C6 through C47 must remain less than or equal to 1.
• Cells C6 through C47 must remain greater than or equal to 0.
• Cells C6 through C47 must be integers.

Upon indicating the constraints and clicking Solve , Excel identifies the solution shown in Figure 3, with some rows hidden for presentation purposes. In this case, the combination of the invoice amounts for 8033, 8340, 8449, 8509, 9373, 9433, and 9473 is the only combination that adds to $14,663.10.

Although Solver is not a new Excel feature, it is often overlooked and underutilized. Yet, many accounting, financial, and other business professionals can use this feature to identify optimal results for various business problems, including budgeting, capital allocation, sales and marketing activities, and inventory optimization. Despite the tool’s power, it is relatively easy to use and can reduce the time spent trying to identify optimal results in complex models. Therefore, examine Solver and see how it can help you and your team find the best solution to your simple and complex models.

For more information on installing Solver, visit https://k2e.fyi/install_solver . Want to learn more about advanced features in Excel? If so, check out the learning options available from K2 Enterprises, by clicking here .

Tommy Stephens

Schedule Optimization in Excel (Detailed Analysis)

I have written an article on how to determine the optimal product mix using Excel Solver. Today, I’d like to discuss how to schedule the workforce along with schedule optimization in Excel with the same method. Schedule a workforce is also a Linear Programming problem.

Here is to refresh your mind before moving forward to solve problems. There are three parts essential to Excel Solver: Set Objective, By Changing Variable Cells, and Subject to the Constraints. Set Objective represents our objective in the LP model. By Changing Variable Cells represents a decision variable that we can adjust or change to optimize our objective. And Subject to the Constraints is our explicit or implicit constraints.

Watch Video – Schedule Optimization in Excel

What Is Solver in Excel?

Solver is a Microsoft Excel add-in program. The Solver is part of the What-If Analysis tools that we can use in Excel to test different scenarios. We can solve decision-making issues using the Excel tool Solver by finding the most perfect solutions. They also analyze how each possibility impacts the worksheet’s output.

How to Enable Solver Feature in Excel

You can access Solver by choosing Data ➪ Analyze ➪ Solver. Sometimes it may happen that this command isn’t available, you have to install the Solver add-in using the following steps:

• First of all, choose the File
• Secondly, select Options from the menu.

• Thus, the Excel Options dialog box appears.
• Here, go to the Add-Ins
• At the bottom of the Excel Options dialog box, select Excel Add-Ins from the Manage drop-down list and then click Go.

• Immediately, the Add-ins dialog box appears.
• Then, place a checkmark next to Solver Add-In, and then click OK.

Once you activate the add-ins in your Excel workbook, they will be visible on the ribbon. Just move to the Data tab and you can find the Solver add-in on the Analyze group.

Techniques for Schedule Optimization

By schedule optimization, we generally mean to make sure that the whole schedule (every individual task and action) aligns with a specific purpose. Schedule optimization is a win-win scenario for any aspect imaginable. You can efficiently work around your resources, manage employees and times better, reduce the workload, etc. To optimize your schedule, you can work around the following important techniques.

• Identifying priority : The one that should be obvious from any schedule optimization definitions.
• Assessing the to-do list : To make sure that your tasks align with your purpose or not, you need to assess and judge the actions of your to-do list.
• Formulating actions : You don’t have to plan everything to the perfect point. But every good job starts with a good plan. So you should aim to formulate one that aligns most with your purpose.
• Reassessing plans : Not everything will be perfect on the first go. You may slip up things here and there. Also, you can miss out on important steps or actions or find a better way to do things. So it is very important to reassess your plans continuously even if they are seemingly perfect at a glance.
• Executing : An average plan in action is better than a perfect plan on paper. So make sure to follow up with the plans and send them out to your employees or peers and follow the adjustments accordingly.

Can You Create a Schedule in Excel?

In short, yes, you definitely can. Microsoft Excel is a wonderful tool to record and track schedules. In fact, Excel has some built-in weekly, daily, and monthly schedule templates built into it. You can simply select the File tab and search for your desired schedule from the backstage view.

Or you can make one on your own. Follow these simple steps for that.

• First, select some cells to record time in them and put them in your preferred alignment. For example, we are putting them in a vertical alignment.

• Then add the days of the week on the right as headers.

• Finally, add some borders to the to-be-used cells.

There you have it- a weekly schedule in its simplest form made in Excel. You can track your work progress or plan ahead by putting the values in the cells beside the intended time.

How to Do Schedule Optimization for a Particular Workforce Using Excel Solver: 3 Cases

Here, we are going to use Excel’s solver feature for schedule optimizations. We are going to look at four different cases of the same organization. And we are going to optimize them in four different ways depending on what we need in each case.

Case 1: Calculating Minimum Number of Telephone Reservation Operators to Meet Labor Demands

Let’s look at the dataset before explaining the situation.

Here shows the number of telephone reservation operators needed by an airline during each time of day. Each operator works one of the following six-hour shifts: midnight to 6:00 A.M, 6:00 A.M. to noon, noon to 6:00 P.M., and 6:00 P.M. to midnight. The minimum number of operators needed is shown in the range C12:H12 .

Let’s determine the objective, changing cells, and constraints of this problem first.

Objective : Minimize the total number of employees

Changing cells : The number of employees who work each of that six-hour shift.

Explicit Constraints : For each time of day, the number of employees who are working must be greater than or equal to the number of employees required

Implicit Constraints : Each changing cell must be a non-negative integer

To set up the model for this problem, problem information was put in the range C5:H8 . The number of employees working each of that six-hour shifts will be filled into the range I5:I8 . Since the numbers are unknown and need to be solved, we will leave them blank now.

We have prepared the sheet by the usage of the SUM and SUMPRODUCT functions.

Follow these steps to prepare the dataset and optimize the schedule in Excel.

• First, enter the following formula into cell C14 to calculate the total number of employees.

=SUM($I$5:$I$8)

• Then press Enter .

• To calculate the number of employees available from midnight to 4 A.M., enter the following formula into cell D10.

=SUMPRODUCT($I$5:$I$8,C5:C8)

• Now click and drag this to the right till cell H10 replicates the formula.

• Next, click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box.

• After that, fill in the Solver Parameters box with the essential information as shown in the following figure.

• Now click on Solve .
• Consequently, a Solver Results box will appear like the following. Click OK on it.

Finally, the cells of the Excel spreadsheet will get filled up like the following indicating the optimum number of operators required in this schedule optimization problem.

Case 2: Calculating Minimum Number of Bank Employees to Meet Labor Demands

For solving this schedule optimization problem in Excel, we will use the following dataset.

The number of workers needed for a bank is shown in cells from C15 through I15 . For example, 17 workers are needed on Monday, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All bank employees work five consecutive days.  What is the minimum number of employees that this bank can have to meet its labor requirement?

Look at the figure again. Numbers of employees who start work (the first of five consecutive days) each day of the week will be recorded in the range J5:J11 . Our objective – the total number of employees – is put into cell C17 . The range C5:I11 is used to track if employees work or not. 1 means that the employee will work on that weekday while 0 indicates that the employee will not work on that day. For example, 1 in C5:G5 means that employees start working on Monday and work Monday through Friday. We have prepared the sheet by the usage of the SUM and SUMPRODUCT functions

Now follow the steps to solve the schedule optimization problem in Excel.

• To count the total number of employees who are working on Monday is filled into cell C13 by using the following formula.

=SUMPRODUCT($J$5:$J$11,C5:C11)

• Then replicate it by clicking and dragging the fill handle icon to the right.

Finally, the cells of the Excel spreadsheet will get filled up like the following indicating the optimum number of employees required to meet labor demands in this schedule optimization problem.

Case 3: Minimizing Salary That Banks Should Pay Employees

Suppose those employees in the same bank (in case 2) are paid $150 per day for the first five days and they work a day of overtime at a cost of $350. How should the bank schedule its employees?

Generally, there are two parts to the dataset. The range C8:I14 provides information that can be used to compute constraints while the range C21:I27 offers information for calculating how much the bank should pay its employees. Constraints, in this case, are the same as those in case 2. And similar to case 2, we have prepared the sheet using the SUM and SUMPRODUCT functions.

Now we can follow these steps once we have the spreadsheet ready at our disposal.

• First, go to the Data tab on the ribbon.
• Then select Solver from the Analyze  group.
• Now fill out the Solver Parameters box with the essential information shown in the figure below.

• After that, click on Solve .
• In the consequent Solver Results box, click on OK .

As a result, the cells of the Excel spreadsheet will get filled up like the following indicating the minimum salary the bank can pay employees in this schedule optimization problem.

Case 4: Maximizing Number of Weekend Days Off with a Fixed Number of Employees

Suppose that the bank has 22 employees. How should the workers be scheduled so that they would have the maximum number of weekend days off?

Just like the previous one, this spreadsheet has two parts too.

We have prepared the sheet by the usage of the SUM and SUMPRODUCT functions. Once the sheet is prepared, you can follow these steps to tackle this particular schedule optimization problem in Excel.

• Then select Solver from the Analyze
• Consequently, the Solver Parameters box will appear. Now fill out the Solver Parameters box with the essential information as shown in the figure below.

As a result, the cells of the Excel spreadsheet will get filled up like the following indicating the minimum number of weekend days off with a fixed number of employees in this schedule optimization problem.

Download Practice Workbook

Download the working file from the link below.

Schedule Optimization.xlsx

That was all for the schedule optimization in Excel. Hopefully, you have grasped the idea of using the solver feature to optimize schedules in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

Related Articles

• How to Solve Linear Optimization Model in Excel
• How to Solve Network Optimization Model in Excel
• How to Optimize Multiple Variables in Excel
• How to Calculate Optimal Product Mix in Excel 
• Mean Variance Optimization in Excel
• Perform Multi-Objective Optimization with Excel Solver
• How to Make Price Optimization Models in Excel
• How to Perform Route Optimization in Excel
• Excel Optimization with Constraints

<< Go Back to Optimization in Excel   | Solver in Excel  |  Learn Excel

What is ExcelDemy?

Tags: Optimization in Excel

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet... Read Full Bio

Thank you very much, Zhiping. Examples you provided refreshed my memory, which are helpful. Good work, much appreciated indeed.

Hi Peter, Thank you.

Thank you for your effort in the examples you provided, they are clear and understandable……Are there any solved examples about distribution or transportation problem using solver?. I appreciate your response. Thank you very much.

Greetings, Huda. I appreciate you asking this question. Solved examples are available about distribution or transportation problems. You can find it here “Solving Transportation or Distribution Problems using Excel Solver”.

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF