problem solving questions in 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

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

• 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/

Define and solve a problem by using Solver

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Put simply, you can use Solver to determine the maximum or minimum value of one cell by changing other cells. For example, you can change the amount of your projected advertising budget and see the effect on your projected profit amount.

Example of a Solver evaluation

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell F5), until the total profit (objective cell F7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell F7, =SUM (Q1 Profit:Q2 Profit).

1. Variable cells

2. Constrained cell

3. Objective cell

After Solver runs, the new values are as follows.

Define and solve a problem

Note:  If the Solver command or the Analysis group is not available, you need to activate the Solver add-in. See: How to activate the Solver add-in.

In the Set Objective box, enter a cell reference or name for the objective cell. The objective cell must contain a formula.

Do one of the following:

If you want the value of the objective cell to be as large as possible, click Max .

If you want the value of the objective cell to be as small as possible, click Min .

If you want the objective cell to be a certain value, click Value of , and then type the value in the box.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the non-adjacent references with commas. The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, enter any constraints that you want to apply by doing the following:

In the Solver Parameters dialog box, click Add .

In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

Click the relationship ( <= , = , >= , int , bin , or dif ) that you want between the referenced cell and the constraint.If you click int , integer appears in the Constraint box. If you click bin , binary appears in the Constraint box. If you click dif , alldifferent appears in the Constraint box.

If you choose <=, =, or >= for the relationship in the Constraint box, type a number, a cell reference or name, or a formula.

To accept the constraint and add another, click Add .

To accept the constraint and return to the Solver Parameter s dialog box, click OK . Note     You can apply the int , bin , and dif relationships only in constraints on decision variable cells.

You can change or delete an existing constraint by doing the following:

In the Solver Parameters dialog box, click the constraint that you want to change or delete.

Click Change and then make your changes, or click Delete .

Click Solve and do one of the following:

To keep the solution values on the worksheet, in the Solver Results dialog box, click Keep Solver Solution .

To restore the original values before you clicked Solve , click Restore Original Values .

You can interrupt the solution process by pressing Esc. Excel recalculates the worksheet with the last values that are found for the decision variable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new worksheet in your workbook. If Solver doesn't find a solution, only certain reports or no reports are available.

To save your decision variable cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

Step through Solver trial solutions

After you define a problem, click Options in the Solver Parameters dialog box.

In the Options dialog box, select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

In the Solver Parameters dialog box, click Solve .

In the Show Trial Solution dialog box, do one of the following:

To stop the solution process and display the Solver Results dialog box, click Stop .

To continue the solution process and display the next trial solution, click Continue .

Change how Solver finds solutions

In the Solver Parameters dialog box, click Options .

Choose or enter values for any of the options on the All Methods , GRG Nonlinear , and Evolutionary tabs in the dialog box.

Save or load a problem model

In the Solver Parameters dialog box, click Load/Save .

Enter a cell range for the model area, and click either Save or Load .

When you save a model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. When you load a model, enter the reference for the entire range of cells that contains the problem model.

Tip:  You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook. Each worksheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a worksheet by clicking Load/Save to save problems individually.

Solving methods used by Solver

You can choose any of the following three algorithms or solving methods in the Solver Parameters dialog box:

Generalized Reduced Gradient (GRG) Nonlinear     Use for problems that are smooth nonlinear.

LP Simplex     Use for problems that are linear.

Evolutionary     Use for problems that are non-smooth.

Important:  You should enable the Solver add-in first. For more information, see Load the Solver add-in .

In the following example, the level of advertising in each quarter affects the number of units sold, indirectly determining the amount of sales revenue, the associated expenses, and the profit. Solver can change the quarterly budgets for advertising (decision variable cells B5:C5), up to a total budget constraint of $20,000 (cell D5), until the total profit (objective cell D7) reaches the maximum possible amount. The values in the variable cells are used to calculate the profit for each quarter, so they are related to the formula objective cell D7, =SUM(Q1 Profit:Q2 Profit).

Click Data > Solver .

In Set Objective , enter a cell reference or name for the objective cell.

Note:  The objective cell must contain a formula.

In the By Changing Variable Cells box, enter a name or reference for each decision variable cell range. Separate the nonadjacent references with commas.

The variable cells must be related directly or indirectly to the objective cell. You can specify up to 200 variable cells.

In the Subject to the Constraints box, add any constraints that you want to apply.

To add a constraint, follow these steps:

On the <= relationship pop-up menu, select the relationship that you want between the referenced cell and the constraint.If you choose <= , = , or >= , in the Constraint box, type a number, a cell reference or name, or a formula.

Note:  You can only apply the int, bin, and dif relationships in constraints on decision variable cells.

Click Solve , and then do one of the following:

To interrupt the solution process, press ESC . Excel recalculates the sheet with the last values that are found for the adjustable cells.

To create a report that is based on your solution after Solver finds a solution, you can click a report type in the Reports box and then click OK . The report is created on a new sheet in your workbook. If Solver doesn't find a solution, the option to create a report is unavailable.

To save your adjusting cell values as a scenario that you can display later, click Save Scenario in the Solver Results dialog box, and then type a name for the scenario in the Scenario Name box.

After you define a problem, in the Solver Parameters dialog box, click Options .

Select the Show Iteration Results check box to see the values of each trial solution, and then click OK .

Click Options , and then in the Options or Solver Options dialog box, choose one or more of the following options:

In the Solver Parameters dialog box, click Solve or Close .

Click Load/Save , enter a cell range for the model area, and then click either Save or Load .

Tip:  You can save the last selections in the Solver Parameters dialog box with a sheet by saving the workbook. Each sheet in a workbook may have its own Solver selections, and all of them are saved. You can also define more than one problem for a sheet by clicking Load/Save to save problems individually.

On the Select a Solving Method pop-up menu, select one of the following:

Note:  Portions of the Solver program code are copyright 1990-2010 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Because add-in programs aren’t supported in Excel for the web, you won’t be able to use the Solver add-in to run what-if analysis on your data to help you find optimal solutions.

If you have the Excel desktop application, you can use the Open in Excel button to open your workbook to use the Solver add-in .

More help on using Solver

For more detailed help on Solver contact:

Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 89450-4288 (775) 831-0300 Web site: http://www.solver.com E-mail: [email protected] Solver Help at www.solver.com .

Portions of the Solver program code are copyright 1990-2009 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc.

Need more help?

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

Using Solver for capital budgeting

Using Solver to determine the optimal product mix

Introduction to what-if analysis

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.

15 Excel Formulas That Will Help You Solve Real Life Problems

Your changes have been saved

Email Is sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

I Can’t Get Over This Outdoor Camera’s Battery Life and Video Quality

All browsers have pdf readers, but this one is my favorite, this novel malware uses discord emojis to steal data.

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

• Productivity
• Spreadsheet
• Microsoft Excel

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..

Trusted by users from these organizations (& many more!)

"I finished up all the lessons, they really helped me understand Excel logic better. Can't wait for more!"

"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", "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.", a better way to learn excel skills.

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. 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 started wondering why there wasn't an easier and faster way to master Excel.

Like I mentioned before, I spent hours watching Excel training videos, but they were honestly kind of boring. I'd find myself zoning out and would need to rewind and re-watch each video two or three times. And without immediately putting the techniques from the videos into practice, I'd forget the techniques right after watching the video.

Excel Practice For Real 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 get their Excel practice from Excel Exercises: because it works. Each lesson is designed to keep you engaged and entertained while walking you through new concepts, so that you can't advance if you're not absorbing the information. 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. Everything from simple data entry to advanced data analysis 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 be your new secret weapon to master Excel and get a new job or move up in your current role.

No sign up necessary. If you love it, you can create an account and join thousands of others who are already using their new Excel skills to stand out and get ahead in their careers.

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 Excel

Like never before.

Solve real-life problems in a fun & interactive way for FREE !

What's in Store for You?

Hint: You'll be Amazed!

Learn Like a Pro!

Hands-On with problems in a structured way & enhance your data analytics skills. No more Toy problems!

Practice Excel Anywhere & Anytime

Practice with an in-browser Excel (from basics to advanced) in a fun & interactive way.

Grab Solution For Every Problem

Well explained solution for every problem if you get stuck. No more time hassles!

Trusted & Verified

Vouched by our Beta users (Pms, Data Analysts, Markets,

HRs, Students etc). Hear them Applauding!

Who can use ExcelPractice?

Hint: Literally Everyone!

✅ Aspiring Data/Business Analysts

✅ Product Managers / Product Analysts

✅ Project Managers

✅ Students (Eng./Comm./MBA etc)

✅ Anyone who wants to hone data skills with Excel

Say 'Hi' to the 3 Musketeers behind the scenes for pulling it off! 🤩

Rohan Rashinkar

Product / Development

Achalesh Lakhotiya

Product / Marketing

Atharv Nagpure

Development

Have a feedback for us? Your Command, Our Action!

Excel Problems & Challenges

If you are looking for a quick challenge in Excel, then you have come to right place. Train your Excel muscles by answering these questions & quizzes.

• How well do you know your Lookups? [Quiz]
• How fast can you run [Excel game]
• Can you find Easter eggs in this workbook?

Latest Challenges:

• Can you calculate vacation days in a period?
• Can you find that pattern?
• Check if two dates are in same month
• Check if few cells have same value
• How many values are common in 2 lists?
• Waiter friendly pricing & round to nearest Monday
• Can you extract dates from text?
• Highlight employees by performance
• An IF formula challenge for you
• Finding next Friday the 13th…
• Calculate Payment Schedule
• Check if a word or phrase is palindrome…
• Writing bit-wise AND, OR & XOR formulas
• Amount donated vs. pledged
• Calculating sum of digits in a number
• Make nuts without going nuts
• Find the average of closest two numbers
• Mutant cell modeling problem
• When does next thanksgiving day occur on same date?
• VLOOKUP Formula challenges + a joke
• How to lookup values to left?
• Show zebra lines when values change
• How to calculate working hours between 2 dates?
• How many hours did Johnny work?
• Find out days overlapped between 2 ranges of dates?
• Calculate average of top 5 values

All the best! 🙂

Comments are closed.

• 3D Maps (non Power Maps)
• 3D Maps (Power Maps)
• advertising
• All Time Hits
• artificial intelligence
• calvin fund
• Chandoo.org Podcast Sessions
• Charts and Graphs
• Complex Numbers
• Cool Infographics & Data Visualizations
• Excel Challenges
• Excel Howtos
• Excel on iPad
• Excel Workshops
• Financial Modeling
• food and drink
• Formula Challenges
• Formula Forensics
• Google Spreadsheets – Tip & Tricks
• Powerpoint Cartoons
• Interview Questions
• Keyboard Shortcuts
• excel links
• Master Class
• Movies-Books
• Office Scripts
• Office Tips
• personal finance
• photographic fridays
• Pivot Tables & Charts
• Posts by Faseeh
• Posts by Hui
• Posts by Jeff
• Posts by KV
• Posts by Luke
• Posts by Sajan
• Power Automate
• Power Pivot
• Power Query
• Project Management
• R programming
• Training Programs

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free. 

Get Started

• Excel Basics
• Advanced Excel
• 100+ Formulas
• Free Crash Course
• Pivot Tables

Online Classes

• Excel School
• VBA Classes
• Power BI Classes
• 50 ways to analyze data
• Mini Courses
• Welcome Message
• Forum & Community
• 20+ Excel Templates
• 200+ Dashboards
• 40+ VBA Code samples
• Ebooks & More

© All rights reserved.

• Courses Business Charts in Excel Master Excel Power Query – Beginner to Pro Fast Track to Power BI View all Courses
• For Business

• Power Excel
• Dashboards, Charts & Features
• VBA & Scripts

Create Business charts that grab attention AND auto-update. Wow your coworkers and managers with smart time-saving techniques.

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.

Stay ahead of the game in 2024. Get access to our best-selling Power BI course now and become a highly sought-after Power BI professional. This course gets you started in Power BI – Fast!

Excel Solver – Example and Step-by-Step Explanation

Tired of struggling with complex problems in Excel? Excel Solver can help.

This powerful tool makes finding the best solutions easy. Unlike Goal Seek or the What-If tool, Solver goes beyond simple scenarios, tackling more complex optimization problems.

In this blog post, you’ll learn how to use Excel Solver with a simple example. Discover how it saves you time and improves your results. Boost your productivity and make better decisions with Excel Solver.

Keep reading to see how this feature can transform your workflow!

Watch video tutorial

In this tutorial:

• What is Excel Solver?
• How to Add Solver to Excel
• How to Use Solver in Excel - Example
• Types of Solving Methods in Excel Solver
• Using Solver for Complex What-If Problems with Constraints
• Tips for Using Solver
• Download the Workbook

Grab our practice workbook 👉 HERE and follow along:

Imagine you want to maximize your profit by adjusting prices and costs while keeping prices and costs within specific limits. Or, you need to allocate your remaining budget across multiple projects with certain spending constraints. Excel Solver can help you achieve these goals efficiently.

Excel Solver is a powerful tool in Microsoft Excel that helps you find the best solutions by adjusting multiple inputs. Use Solver in Excel to maximize or minimize outcomes like profit, cost, or efficiency.

Key Features of Excel Solver

• Optimization : Solver helps you find the best values for your inputs to get the desired results. Increase profits, reduce costs, or meet specific targets easily.
• Multiple Variables : Adjust several inputs at once, making Solver perfect for complex problems.
• Constraints : Set limits for your inputs, such as keeping prices within a range or ensuring numbers are whole.
• GRG Nonlinear: Best for nonlinear problems, finding a local best solution.
• Simplex LP: Ideal for linear problems.
• Evolutionary: Great for complex problems, finding a global best solution.

Often our students ask: Where is Solver in Excel?

Did you know the Microsoft Solver add-in is included with all versions of Excel since 2003, but it’s not enabled by default? Here’s how you can quickly activate it:

• Open Excel: Start by opening your Excel application.
• Go to File : Click on the “File” tab in the top left corner.
• Select Options : Choose “Options” from the drop-down menu.
• Choose Add-ins : In the Excel Options window, click on “Add-ins”.
• Manage Add-ins : At the bottom of this window, find the “Manage” box. Select “Excel Add-ins” and click “Go”.

• 6. Enable Solver : Check the box next to “Solver Add-in”.
• 7. Confirm: Click “OK” to activate Solver.

Now, you can find Solver under the “Data” tab in the “Analyze” section.

How to Use Solver in Excel – Example

Goal Seek is great, but it only changes one input variable. For more complex problems with multiple variables, Solver is the tool you need.

Here’s a simple example:

You want to achieve a target income of 2,000 by adjusting the units sold and the price per unit.

• Units Sold:  Input variable.
• Price per Unit : Input variable.
• Revenue : Calculated as Units Sold * Price per Unit
• Cost per unit : A fixed number.
• Total Costs : Calculated as Units Sold * Cost per Unit.
• Income : Calculated as Revenue – Total Costs.

Constraints

• Units sold must be a whole number.
• Price per unit should be between 3 and 4.

Using Solver in Excel

When you open the Solver Parameters window, you need to set up three primary components: the objective cell, variable cells, and constraints. Here’s how to do it:

Set the Objective

Objective cell: This is the cell that contains the formula you want to optimize. For example, if you want to maximize profit or minimize costs, select the cell with that calculation.

In our example, the objective cell is $B$11, which calculates the income.

• Go to the Data tab, find the Analyze section, and click Solver. Set the objective cell ($B$11) to a value of 2,000.

Select Variable Cells

These are the cells that Solver will change to achieve the objective. Highlight the cells you want to adjust.

• For our example, these are the units sold and the price per unit, so we select $B$3:$B$4 and $B$8.

Add Constraints

Constraints are the limits you set for your variable cells. Click “Add” in the Solver Parameters window to specify these limits. For our example, set the units sold to be a whole number (integer) and the price per unit to be between 3 and 4.

• Click “Add” to set limits. Specify that units sold must be an integer and the price per unit should be between 3 and 4.

• Choose Solving Method: Select the appropriate solving method (e.g., GRG Nonlinear).

Click on Solve. The Solver Results window will pop up, saying that it has found a solution. You will notice that the values of the input cells have changed but the formulas in your calculated cells have been retained. You are given the option to accept the new values by clicking on OK, or reject them by clicking on Cancel.

Excel Solver offers three methods to find the best solution for your problem. Here’s a quick overview:

GRG Nonlinear

• Best For: Nonlinear problems.
• How It Works: Finds a local optimal solution.
• Default Method: This is the most commonly used method and is set as the default.
• Best For: Linear problems.
• How It Works: Efficiently solves problems with linear relationships.

Evolutionary

• Best For: Complex and non-smooth nonlinear problems.
• How It Works: Searches for a global optimal solution, which can take longer to run compared to GRG Nonlinear.

Solver can be a powerful tool when you need to allocate a budget among multiple projects, especially with specific constraints. Here’s an example to illustrate how to use Solver for this purpose:

You need to distribute a budget across several projects, with the following constraints:

• Extra costs should not exceed 1,000.
• Fixed costs must remain at 2,000.
• Total project cost for each project should not exceed 9,600.

Setting Up Solver

Set objective :.

• Go to the “Data” tab, find the “Analyze” section, and click on “Solver”. Set the objective cell ($B$14) to a value of 65,000.

Select Variable Cells:

Highlight the cells that Solver can change:

• Extra costs: $D$5:$D$13
• Production costs: $B$5:$B$13 (we choose Production Cost instead of Total Costs because the latter is a calculated field).

Add Constraints:

Click “Add” in the Solver Parameters window to set the following constraints:

• Extra costs ($D$5:$D$13) must be less than or equal to 1,000 ($E$18).
• Total project cost ($E$5:$E$13) must be less than or equal to 9,600 ($E$20).

Solve: Choose the appropriate solving method (e.g., GRG Nonlinear) and click “Solve”. Solver will find the best solution, adjusting the input cells within the defined constraints.

• Start Simple: Begin with straightforward scenarios and gradually add complexity.
• Experiment: Try different settings and constraints to see how they affect the outcome.

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel Solver tool with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

Need help deciding?

Find your ideal course with this quick quiz. Takes one minute.

Featured Course

Business Charts in Excel

Excel Essentials for the Real World

Visually Effective Excel Dashboards

Master excel power query – beginner to pro, featured tutorials.

Black Belt Excel Package

You might also like...

How to Make a Pie Chart in Excel

How to Make a Line Chart in Excel

How to Hide and Unhide all Rows in Excel

EXCLUSIVE FREE NEWSLETTER

Join between the sheets.

Kickstart your week with our free newsletter covering Excel hacks, Power BI tips, and the latest in AI. You get to stay updated and get all the insights you need, delivered straight to your inbox.

You can unsubscribe anytime of course.

Stay Ahead with Weekly Insights!

Dive into Excel, AI and other essential tech news:

carefully crafted for the modern professional.

Success! Now check your email to confirm your subscription.

There was an error submitting your subscription. Please try again.

Practice Assessment Tests

Free assessment tests to get you prepared, excel like a pro: 25 essential test questions you need to know.

• To pass the Microsoft Excel Assessment Test, consistent hands-on practice is crucial as it offers comprehensive and realistic practice tests that cover basic, intermediate, and advanced Excel skills.
• In this post you will learn about Top 25 Excel Test Questions. You will also learn about detailed explanations, answers and solutions, helping candidates understand the reasoning behind each answer and improve their problem-solving abilities. By simulating real test conditions, practice Excel tests enhance candidates' familiarity with the test format and time management skills, reducing anxiety on test day.
• This article ensures thorough preparation for you as a candidate, to help you confidently demonstrate their proficiency in Excel, significantly increasing their chances of getting hired.
• If job posting emphasizes cognitive skills and you anticipate that you might get a pre-employment assessment, the CCAT ( Criteria Cognitive Aptitude Test ) is a cognitive assessment used by employers to measure problem-solving abilities, critical thinking, and learning skills.
• Other relevant assessments you may need to consider to pass job interview and pre-employment test are Cognitive Test , Personality Test , Situational Judgement Test and Leadership Assessment Test
• Job Candidates required to pass an Excel Test may also be asked to pass a Data Entry Assessment or Typing Test because these skills are essential for efficiently handling large volumes of data and ensuring accurate input and management within Excel spreadsheets.

What is Microsoft Excel Test?

The Microsoft Excel Assessment Test is a standardized evaluation tool designed to measure an individual's proficiency in using Microsoft Excel, a widely used spreadsheet software application.

By understanding the nature of the Microsoft Excel Assessment Test and adopting effective preparation strategies, job seekers can enhance their Excel skills and increase their chances of success in the recruitment process.

Excel assessment test is commonly used by employers during the recruitment process to assess candidates' Excel skills and determine their suitability for roles that require Excel proficiency.

Insights for Job Seekers:

• Purpose : Understand why the test is being administered. The primary purpose is to assess your Excel skills relevant to the job role. Employers want to ensure that candidates have the necessary Excel skills to perform tasks efficiently and accurately.
• Content : Familiarize yourself with the content of the test. It typically covers a range of topics, including basic navigation, data entry, formulas and functions, data analysis, chart creation, PivotTables, macros, and more. Reviewing these areas and practicing relevant tasks can help you prepare effectively.
• Format : Learn about the format of the test. It may include multiple-choice questions, practical exercises, simulations, or a combination of these. Understanding the test format allows you to tailor your preparation strategy accordingly.
• Scoring : Be aware of how the test is scored. Depending on the assessment, scoring may be based on correct answers, accuracy of solutions, time taken to complete tasks, or a combination of these factors. Focus on accuracy and efficiency in your responses.
• Preparation : Dedicate time to prepare for Excel Assessment test . Practice using Excel to perform various tasks commonly assessed in the test, such as creating formulas, analyzing data, and generating charts. Take advantage of online tutorials, practice exercises, and sample tests to strengthen your skills. For Premium option, explore JobTestPrep Excel Practice assessment to get prepared

Two Excel Assessment Types

By practicing regularly, candidates can build both their theoretical understanding and practical skills in Excel, increasing their confidence and proficiency for both types of Excel assessment tests, thereby enhancing their chances of getting hired.

1) Type 1 - Excel Test with Multiple Choice Question

Description : The Excel Test - Multiple Choice Question assesses a candidate’s theoretical knowledge and understanding of Excel functions, features, and best practices without requiring direct interaction with an Excel spreadsheet.

• Question Structure : Candidates are presented with a question followed by multiple answer choices. Only one answer is correct, and the candidate must select the best response.
• Topics Covered : Questions may cover a wide range of topics, including basic functions (SUM, AVERAGE), formatting cells, using formulas (IF, VLOOKUP), data analysis tools, and understanding Excel terminology.
• Difficulty Levels : Questions range from basic to advanced, testing knowledge across different levels of Excel proficiency.

Example Questions :

• a) To sort data
• b) To create a static table
• c) To summarize and analyze data
• d) To format cells

Considerations :

• Theoretical Knowledge : This test evaluates a candidate’s understanding of how Excel functions work and their ability to choose the correct function or tool based on a given scenario.
• Challenging for Practitioners : Those who are used to working hands-on with Excel may find it challenging to select the correct answers without the ability to verify their responses by actually performing the tasks.

2) Type 2 - Interactive Spreadsheet for Excel Assessment

Description : The Excel Test - Interactive Spreadsheet evaluates a candidate’s practical skills by requiring them to perform specific tasks within an actual Excel spreadsheet or an Excel simulation.

• Task Structure : Candidates are given a set of instructions to complete tasks directly in Excel. These tasks may involve creating formulas, generating charts, applying formatting, or analyzing data.
• Topics Covered : Tasks can range from basic data entry and formatting to advanced data analysis using PivotTables, complex formulas, and macros.
• Difficulty Levels : Tasks vary in complexity, assessing skills from basic operations to advanced Excel functionalities.

Example Tasks :

• Task: Enter sales figures into cells A1 to A5 and format them as currency.
• Task: Use VLOOKUP to retrieve the price of a product from a table and display it in another cell.
• Task: Generate a PivotTable to summarize total sales by region and create a corresponding PivotChart.
• Hands-On Skills : This test assesses a candidate’s ability to execute tasks in real-time, demonstrating their proficiency with Excel’s features and tools.
• Realistic Work Simulation : By performing tasks in an interactive environment, candidates can showcase their practical skills and ability to handle real-world Excel tasks efficiently.

Importance of Practice

Using JobTestPrep Materials :

• Comprehensive Preparation : JobTestPrep offers practice materials that cover both types of assessments, providing a well-rounded preparation experience.
• Realistic Practice Tests : For multiple-choice questions, JobTestPrep offers tests that mirror the format and difficulty of actual assessments, helping candidates improve their theoretical knowledge. For interactive spreadsheets, the practice tasks simulate real Excel tasks, enhancing practical skills.
• Detailed Explanations : Each question and task comes with explanations and solutions, helping candidates understand the correct answers and learn from their mistakes.
• Timed Practice : Simulating the time constraints of real tests helps candidates improve their time management skills, crucial for performing well under pressure.

Did You Know?

In today's digitally-driven workplace, proficiency in the Microsoft Office Suite is not limited to Excel alone. Employers recognize the need for comprehensive knowledge across all core applications. Therefore, in addition to Excel Assessment Tests, companies also administer Word Assessment Tests and PowerPoint Assessments to gauge a candidate's abilities in document creation, formatting, and presentation.

The Word Assessment Test evaluates a candidate's skills in word processing, including formatting text, using styles and templates, creating tables, and implementing document collaboration features. This assessment ensures that candidates can efficiently produce, manage, and edit professional documents.

The PowerPoint Assessment measures a candidate's capability in creating engaging presentations. This includes designing slides, working with custom animations and transitions, inserting multimedia elements, and understanding best practices for visual communication and storytelling within a corporate setting.

Some roles may require proficiency in the entire Microsoft Office Suite , leading companies to conduct a comprehensive assessment that covers skills in Excel, Word, PowerPoint, and sometimes other applications like Outlook and Access. Such tests ascertain a candidate's versatility and ability to integrate various Office tools to optimize productivity and perform a wide range of office-related tasks effectively.

Testing for the full Microsoft Office Suite skills provides a holistic view of a candidate's technical competencies, ensuring they are well-equipped to handle the multifaceted digital tasks that modern office jobs demand.

Jobs Assessed with Excel Test

Employers use Microsoft Pre-Employment Assessment Tests to evaluate job candidates on their proficiency in Excel, which is integral to the job functions of various roles.

For each of these roles, the complexity of the Excel test will align with the level of expertise expected in the position. Candidates are typically tested not just on their knowledge of Excel functions, but also on their ability to apply these skills to solve job-specific problems efficiently and accurately. Practice with Excel, especially on tasks relevant to their field, is essential for candidates to perform well on these assessments.

Here's how different professionals might be assessed:

Administrative Assistants

Employers test administrative assistants on basic to intermediate Excel functions, such as formatting, simple formulas, data entry, and the creation of tables and charts. The test may include organizing schedules, filtering and sorting data, and generating reports to assess the candidates' ability to manage office-related tasks effectively.

Financial Analysts

Financial analysts are typically tested on advanced Excel features, including financial functions, data analysis tools, complex formulas, and the ability to create detailed financial models and reports. Employers assess their ability to analyze large datasets, forecast financial outcomes, and utilize pivot tables and what-if analysis.

Accountants

Accountants may be tested on their expertise in using Excel for accounting-specific tasks such as ledger management, reconciliation, using functions like VLOOKUP and SUMIF, and the preparation of financial statements. The assessment might also involve applying conditional formatting and creating complex formulas to handle financial data efficiently.

Project Managers

Project managers are assessed on their ability to use Excel for project tracking, including Gantt charts, budget management, timeline scheduling, and resource allocation. Tests might involve scenario-based tasks that require combining multiple Excel functionalities to maintain and update project plans.

Sales and Marketing Professionals

These professionals are likely to be tested on their skills in analyzing sales data, market trends, and customer databases in Excel. Tests might include creating sales forecasts, tracking campaign performance, and using statistical functions to interpret market data.

Human Resources Professionals

HR professionals' Excel tests typically include managing employee databases, creating and using HR metrics, payroll functions, and schedule planning. They might be assessed on data organization, analysis, and the ability to maintain complex employee information with accuracy.

Data Analysts

Data analysts may be assessed on their ability to perform data cleaning, use formulas for manipulation, and conduct basic analysis within Excel. While they often move on to more specialized software, their proficiency in Excel for preliminary data work is critical.

Operations Managers

For operations managers, Excel assessments may involve inventory tracking, logistics planning, supply chain analysis, and operations performance metrics. Employers test their ability to manage operational data, perform what-if analysis, and create reports that support decision-making.

Researchers

Researchers' tests in Excel might include organizing and cleaning research data, performing statistical analysis with Excel's toolset, and creating visualizations like graphs and charts to present findings clearly.

Business Consultants

Business consultants are tested on their ability to use Excel for a wide array of consulting-specific tasks, such as developing business models, performing market analyses, and creating persuasive client presentations and detailed reports.

Excel Assessment Test Proficiency Levels

Proficiency levels in Excel tests are typically categorized as Basic, Intermediate, and Advanced.

These proficiency levels provide a framework for assessing candidates' Excel skills across a spectrum of complexity. Depending on the job requirements, employers may tailor the assessment to focus more on certain skills within each proficiency level.

Here's a breakdown of the skills that might be assessed in each category:

Basic Proficiency :

• Basic navigation within Excel (moving between cells, sheets).
• Entering and editing data in cells.
• Basic formatting (font styles, cell borders, alignment).
• Basic arithmetic operations (addition, subtraction, multiplication, division).
• Understanding and using basic functions (SUM, AVERAGE, MAX, MIN).
• Sorting and filtering data.
• Understanding basic data types (text, numbers, dates).
• Creating basic charts (bar charts, pie charts).
• Formatting chart elements (axes, titles, legends).

Intermediate Proficiency :

• Working with logical functions (IF, AND, OR).
• Using lookup and reference functions (VLOOKUP, HLOOKUP, INDEX, MATCH).
• Using PivotTables for data summarization.
• Conditional formatting based on specific criteria.
• Applying data validation rules to control input.
• Protecting worksheets and workbooks with passwords.
• Creating and customizing various types of charts (line charts, scatter plots).
• Adding trendlines and error bars to charts.

Advanced Proficiency :

• Performing complex data analysis using functions like SUMIFS, COUNTIFS, AVERAGEIFS.
• Creating complex formulas and nested functions.
• Using advanced filtering techniques (advanced filter, filter by color).
• Consolidating data from multiple sources using data consolidation tools.
• Recording and editing macros to automate repetitive tasks.
• Writing custom macros using VBA (Visual Basic for Applications).
• Importing data from external sources using Power Query.
• Exporting Excel data to other formats (PDF, CSV) using VBA or built-in tools.

To successfully pass an Excel Assessment test at any level, it's essential to be well-prepared for a wide range of questions, including the fundamental ones. JobTestPreps's MS Excel Test PrepPack covers all levels of difficulty required for the actual Excel assessment test, ensuring you're ready to tackle any question that comes your way.

Practice Free Microsoft Excel Test

Show Answer

Basic vs Intermediate vs Advanced Excel Test

The Microsoft Excel Assessment Test is designed to evaluate a range of skills from basic to advanced levels. Here’s a detailed breakdown of the skills assessed at each level:

Basic Skills

• Description : Ability to enter and edit data in cells, including text, numbers, and dates.
• Assessment : Candidates may be asked to input a series of data points or correct existing data entries.
• Description : Understanding of basic formatting options like font style, size, color, cell borders, and shading.
• Assessment : Tasks might include formatting a set of data according to specific guidelines.
• Description : Use of basic arithmetic formulas (SUM, AVERAGE, COUNT).
• Assessment : Candidates could be asked to calculate totals or averages for a given data set.
• Description : Ability to sort data in ascending or descending order and apply basic filters.
• Assessment : Tasks might involve sorting a list of items or filtering data to show specific criteria.
• Description : Creating simple charts like bar graphs or pie charts.
• Assessment : Candidates may be required to generate a chart from a given data set.

Intermediate Skills

• Description : Applying conditional formatting to highlight data based on specific rules.
• Assessment : Tasks may include setting up conditional formatting to highlight cells based on values or text.
• Description : Use of more complex functions such as VLOOKUP, HLOOKUP, IF, and nested formulas.
• Assessment : Candidates might be asked to use these functions to extract and manipulate data.
• Description : Setting up data validation rules to control the type of data entered in a cell.
• Assessment : Tasks could involve creating drop-down lists or setting up rules to restrict data entry.
• Description : Creating and manipulating pivot tables to summarize and analyze data.
• Assessment : Candidates may be asked to create a pivot table to summarize a large data set.
• Description : Customizing charts with titles, legends, data labels, and other elements.
• Assessment : Tasks might include modifying a chart to meet specific formatting and presentation requirements.

Advanced Skills

• Description : Mastery of complex functions like INDEX-MATCH, array formulas, and advanced IF conditions.
• Assessment : Candidates could be required to solve complex problems using these advanced formulas.
• Description : Creating and using macros to automate repetitive tasks and understanding basic VBA (Visual Basic for Applications) scripting.
• Assessment : Tasks may involve writing a simple macro or modifying existing VBA code to automate processes.
• Description : Using Excel’s data analysis tools like Goal Seek, Solver, and Data Analysis Toolpak for advanced data modeling and analysis.
• Assessment : Candidates might be asked to perform data analysis using these tools to solve business problems.
• Description : Creating complex pivot tables and pivot charts, using calculated fields and items.
• Assessment : Tasks could include building a detailed pivot table report and generating dynamic pivot charts.
• Description : Importing data from external sources, setting up data connections, and using Power Query.
• Assessment : Candidates may be required to connect to an external database, import data, and clean it using Power Query.
• Description : Utilizing dynamic array functions like SORT, FILTER, UNIQUE, and the new XLOOKUP function.
• Assessment : Tasks could involve using these functions to dynamically analyze and display data.

By assessing these skills across basic, intermediate, and advanced levels, the Microsoft Excel Assessment Test ensures that candidates have the necessary proficiency to handle a variety of tasks, from simple data entry to complex data analysis and automation. Preparing for this test using targeted practice materials can help candidates excel in each skill area, improving their chances of success in roles that require strong Excel capabilities.

Top 25 Microsoft Excel Test Questions

Instructions: For each question below, review the question and try to anticipate what the correct answer is. Once completed, click Show Answer button, to validate if your guess was correct. Review explanations to the questions and repeat the process as many time as necessary for you to learn.

Tip: If you are getting ready for the test sometime soon, for best results, it might be best to go through all the questions multiple times per day, but taking a break in between attempts (i.e. morning, afternoon & evening)

Question. Selecting multiple columns, especially if they run into the hundreds, can take a long time if you do this the manual way by using your mouse. What is a shortcut for quickly selecting a range of contiguous columns? a) Select the first column. Hold down Ctrl and press the right arrow on the keyboard. b) Select the first column. Hold down Ctrl and drag the mouse cursor towards the right. c) Select the first column. Hold down Shift and press the right arrow on the keyboard. d) All of the above options are correct.

Answer: The correct answer is c. Selecting the first column, holding down Shift and pressing the right arrow key will allow you to quickly select columns that are directly adjacent to each other. You can select all the columns up to the end of the worksheet by holding down the Ctrl key and then pressing Shift and the right arrow (Ctrl+Shift+Right Arrow). This is the ideal solution for hiding columns you do not use to make your worksheet appear tidier.

Question. Select which of the following lists most accurately represent functions found in the Backstage View in Excel: a) Save, Page Layout, Document Preview, Print, Recent Workbooks and Review. b) Save, Save As, Open, Close, Info, Recent Workbooks, Recent Places, New, Print and Options. c) Save, Save As, Open, Recent Print, Print Preview, Page Layout, Options and Access. d) None of the above lists are entirely correct.

Answer: The correct answer is b. All the other options above contain items that are not located in the Backstage View.

Question. Which of the following functions are you likely to find under the Insert tab on the Ribbon? a) Insert a page break. b) Insert a formula. c) Insert a table. d) Insert track changes.

Answer: The correct answer is c. Although the other functions (i.e. Insert a page break, insert a formula or insert track changes) are also elements you might be inserting into a spreadsheet, they are not under the Insert Tab.

Question. The Recent Workbooks list located in the Backstage View shows all workbooks recently opened. What action can I take to ensure a workbook remains on this list regardless of how many workbooks I opened subsequent to opening this workbook? a) I can save the Workbook to the Recent Workbooks folder. b) By adding the Workbook to My Favorites on the Quick Access Toolbar. c) The Recent Workbooks list automatically keeps all workbooks that have been opened and I do not need to do anything. d) By 'pinning' the workbook to the Recent Workbooks list by clicking the pin icon located next to it.

Answer: The correct answer is d. After pinning a workbook to the list the only way to remove it is to click the pin again.

Question. What is the keyboard shortcut to select a row of active cells?

a) Ctrl + Spacebar b) Shift + Spacebar c) Shift + End d) Ctrl + End?

Answer: The correct answer is b. Holding down the Shift key while pressing Spacebar will select the entire row in which the cursor is currently positioned in Microsoft Excel.

Question.  Most workbooks in Excel contain multiple worksheets (noted on the tabs at the bottom right-hand side of the Excel screen). Which of the following is the keyboard shortcut to for switching between worksheets in Microsoft Excel? a) Alt+Tab b) Alt+PgDn/PgDn c) Ctrl+PgDn/PgUp d) Ctrl+UpArrow/DownArrow

Answer: The correct answer is c. You can use either Ctrl+PgDn or Ctrl+PgUp to switch between the available worksheets in a workbook . Press this shortcut repeatedly to navigate through the available worksheets.

Question. Jason has a workbook open that his colleague created. The workbook is titled: "Management Budget". John wants to use this workbook as a base for creating a workbook called "Project Management". Which of the following is the correct sequence of actions for him to take? a) File Tab – Save – File Name – "Project Management" – Save. b) File Tab – Save As – File Name – "Project Management" – Save. c) File Tab – Open – File Name – "Project Management" – Open. d) File Tab – Save As – File Name – "Management Budget" – Save as Type – "Project Management".

Answer: The correct answer is b. This will save a duplicate of the workbook with the name "Project Management".

Question. What happens when you select cells and press Ctrl+2? a) Text in the selected cells are underlined. b) Italic formatting is applied to the text in the selected cells. c) Bold formatting is applied to the text in the selected cells. d) A duplicate of the selected cells will be inserted directly below the selection.

Answer: The correct answer is c. Using Ctrl+2 you can quickly apply bold formatting to a selected cell, row or column. Another alternative to make selection bold is to use the more traditional Office shortcut of Ctrl+B to achieve the same result.

Question. The following shortcut key combinations will allow you to Open, Close and create a New workbook, respectively: a) Pressing Alt + O to Open, Alt + C to Close, Alt + N for New. b) Pressing Control + O for Open, Control + C for Close, Control + N for New. c) Pressing Control + O for Open, Control + W for Close, Control + N for New. d) None of the above options are correct.

Answer: The correct answer is c. Holding down the control key on your keyboard whilst pressing O, W or N respectively will allow you to quickly access the Open dialogue box, close an open workbook and create a New blank workbook.

Question. What is the keyboard shortcut for reversing (undoing) your last action? a) Ctrl+U b) Ctrl+Z c) Ctrl+X d) Ctrl+Y

Answer: The correct answer is b. Holding down the Ctrl key on your keyboard and pressing the Z key at the same time will undo the last action you performed in the selected workbook. Holding down Ctrl and pressing Z repeatedly will undo multiple actions that have been performed.

Question. Mikhala made some changes to a workbook called "Sept 2014". She then clicked Save As, typed "Sept_2014_New" in the File Name box and clicked Save. Which of the following statements are true? a) The changes Lucy made to "Sept 2014" will be reflected in the new workbook, "Sept_2019_New" only. b) The changes Lucy made will be reflected in both versions, "Sept 2014" and "Sept_2019_New". c) By using the Save As function Lucy replaced the file "Sept 2014" with "Sept_2019_New". The original file no longer exists. d) The changes Lucy made will stay in "Sept 2014" and will not be reflected in "Sept_2019_New".

Answer: The correct answer is a. When you click Save As, Excel assumes you want to save all the changes you made to a new workbook. Unless you clicked save in the existing workbook, none of the changes you made will be saved in the original.

Question. Which of the following functions is NOT associated with the Review Tab on the Ribbon? a) The spelling checker. b) The sort data function. c) The comment function. d) The track changes function.

Answer: The correct answer is b. The sort function is associated with the Data tab on the Ribbon and not with the Review Tab.

Question. Allen is using a colleague's PC. He is curious as to how much his colleague earns and when he sees a folder with the title "Budget", he decides to snoop. He opens the folder and views a few of the workbooks in this folder. Will his colleague be able to know he snooped, and if yes, how? a) No, Ben is safe. Unless his colleague is an IT specialist, he will never find out. b) Yes, if his colleague presses the Data tab on the Ribbon, this will reveal a list of recently displayed documents. c) No, Excel automatically deletes recently access workbook and file history if you close and shutdown the PC. d) Yes, if his colleague opens the Backstage View and go to the Recent Workbooks and Recent Places area.

Answer: The correct answer is d. Unless his colleague first opens and works on a number of other workbooks / folders that will push the files Ben viewed out of the list, his colleague could easily see which workbooks and even which files Ben had viewed.

Question. Which shortcut key combination can be used to insert the current date into a cell? a) Ctrl+D b) Ctrl+Shift+; (Control+Shift+semicolon) c) Ctrl+; (Control+semicolon) d) Ctrl+Shift+D

Answer: The correct answer is c. Holding Ctrl key and pressing ; (semicolon) key at the same time inserts current date into the spreadsheet. This is a great shortcut for timesheets that require a user to insert the date into a sheet on a continual basis. The formatting of the date, for example 01/01/2015 or 1 January 2015, will depend on the formatting applied to the cell in which the date is inserted.

Question. What happens when you press the shortcut combination Ctrl+i while you have cells, containing content such as text or numbers, selected? a) This italicizes the selected content. b) This launches the Excel Information and Help dialogue box. c) This inserts a new row or column depending on the selection. d) This hides the selected cell range.

Answer: The correct answer is a. Pressing Ctrl+i makes selection italic . You can also use the shortcut Ctrl+3 to achieve the same result.

Question. A client emails you and informs you that they were unable to open a workbook you sent them as they still use MS Excel 2010. Which of the following options will quickly resolve this issue and allow the client to open the workbook? a) They will need to install MS Excel 2010 or use someone else's software in order to open the workbook. b) You can use the Save As function to select a Save As Type: Excel 2010 Workbook. c) If you open the workbook, you can go to the client and copy and paste all the content into their version of Excel. d) If you rename your workbook: "Excel 2003", this will allow the client to open the file.

Answer: The correct answer is b.

Question. What is the shortcut key to save a workbook? a) Ctrl+Enter b) Ctrl+Shift+S c) Alt+S d) Ctrl+S

Answer: The correct answer is d. Clicking Ctrl+S saves active document in Microsoft Excel. You can quickly and continually save a worksheet whilst working, without lifting your fingers from the keys on your keyboard, by using this shortcut to save.

Question. Which of the following statements about the Print Preview is true? a) It allows you to view what your document will look like before you physically print the pages. b) You access the Print Preview area of the by pressing: Control + P. c) You access the Print Preview area by clicking on the File button to go to the and then on the Print button. d) All of the above statements are correct .

Answer: The correct answer is d.

Question. What is the difference between Excel for desktop and Excel 365?

Answer: Office 2016 for Desktop is the newest version of the Office productivity suite (Word, Excel, PowerPoint and other application), typically accessed via the desktop. Office 365 is a cloud-based subscription to a suite of programs including Office 2016. Office files can be accessed in the browser and on Mobile devices, which allows you more flexibility.

You can also download and install Office 2016, including Excel 365 on your desktop as part of Office 365 subscription. Full desktop versions of Office (Word, PowerPoint, Excel plus OneNote and Publisher) is included with license for up to 5 computers per user.

Question. What are the most typically used functions in Microsoft Excel?

Answer: This is an open ended question, which shows personal preferences and might trigger different answers from candidates with different level of experience. One of the most useful Excel functions is SUM, which allows you to quickly to sum a range of cells by using AutoSum button. It automatically enters a SUM function in the selected cell. The SUM function totals one or more numbers in a range of cells.

Other useful Excel functions that are very commonly used are are AVERAGE, MAX and MIN.

Question. What is a function in Microsoft Excel?

Answer: Functions are predefined formulas and are already available in Excel. Formulas in Excel are useful to perform various mathematical, statistical, and logical operations. You can type in a formula (though you have to be sure it's exactly right) or you can use Excel's preset formulas called functions.

If you type in the formula, you must start with an equal sign, so Excel knows that the data in the cell is a formula. After the =, what comes next depends on what you’re trying to do. Excel offers you some suggestions and shows the syntax for the given function. Excel also corrects you and provides suggestions if you have made a mistake, whily typing.

Question. What is the order of operations used when evaluating formulas in Excel?

Answer:  Excel uses an order of operations when evaluating different operators within the formula, using below sequence:

• P arentheses
• M ultiplication
• S ubtraction

You can easily remember the sequence as PEMDAS , based on the first letters of above listed operations, and this would allow you to quickly bring it up and shine during the interview: when evaluating formulas, Excel always processes operators in this order. If you find yourself receiving an unexpected result from your mathematical formulas, double-check to make sure that parentheses are used properly to achieve the results you want.

Question. What are the most important data types in Excel, and how are they used?

An swer: It is hard to pinpoint most important data types, but most typically used Excel data types are Number, Percentage, Date and Text:

Learn Faster with Excel Video Tutorial

Is excel the only test you need.

Microsoft Excel Assessment test may not be the only assessment you need to get hired and here is why:

• If your applying for analyst, accountant or any other relevant jobs that require strong analytical and math skills, you may also benefit by checking out sample questions for for Numerical Reasoning Test and Logical Reasoning Test
• If your job requires Excel Skills , consider refreshing your knowledge and practicing the questions related to Microsoft Excel Assessment
• For engineering jobs Mechanical Aptitude Test might be used - check out sample questions to make sure you get prepared
• Can't find your assessment test here? Download our free PDF Ebook to review other types of sample assessment test questions
• If you have hiring assessment or job interview are coming make sure to start preparing as quickly as possible and do it regularly: regular practice boosts your confidence and enhances your chances of performing well on the actual test, increasing your likelihood of getting hired.
• All the best on your evaluation! Hope you will get hired soon!!!

If you know the name of your assessment, the easiest way to find it is by searching using relevant keywords (e.g., "Excel Test," "TSA," "CCAT").

Why Employers use Excel Assessment Test

The Excel Assessment Test is a common component of the hiring process for a wide range of job positions, spanning from administrative roles to accounting positions and beyond.

Employers use Excel assessment tests for several reasons:

• Skill Evaluation: Excel is a fundamental tool in many industries, and employers want to ensure that candidates possess the necessary skills to excel in their roles. Assessment tests help them gauge a candidate's proficiency in using Excel, from basic tasks to more advanced functions.
• Job Relevance: These tests allow employers to tailor the evaluation to the specific requirements of the job. They can test for skills that are directly relevant to the tasks the candidate will perform.
• Efficiency: Excel proficiency can greatly impact productivity. Employers want to assess a candidate's ability to work efficiently, and Excel tests provide a quantifiable measure of this efficiency.
• Data Handling: Many positions require candidates to work with data. Excel is a powerful tool for data analysis and reporting, and employers use tests to determine if candidates can effectively handle data-related tasks.
• Cost Savings: Hiring someone who is proficient in Excel can reduce the need for extensive training, potentially saving the company time and money.
• Quality Assurance: Employers want to ensure the quality of work produced by employees. Proficiency in Excel can be a critical component of maintaining high standards in data management and analysis.
• Competitive Edge: Companies often compete in industries where data analysis and reporting are essential. Having a skilled Excel user can provide a competitive advantage.
• Technical Skills Assessment: For roles in finance, accounting, data analysis, and project management, strong Excel skills are often non-negotiable. Assessment tests help employers evaluate these skills objectively.

Excel assessment tests help employers make informed decisions, save time and resources in training, and ensure that candidates have the necessary skills for the job.

Companies that Use the Excel Test

Microsoft Excel Assessment Tests are used across various industries and by numerous employers to evaluate candidates' proficiency in Excel skills. Here's a breakdown of some industries and specific employers within those industries known to use Excel assessment tests:

• Banks : Financial institutions often use Excel assessment tests for positions such as financial analysts, investment bankers, and risk managers. Examples include JPMorgan Chase, Goldman Sachs, and Morgan Stanley.
• Accounting Firms : Firms like Deloitte, PwC (PricewaterhouseCoopers), EY (Ernst & Young), and KPMG may use Excel tests for roles in auditing, tax, and financial advisory.
• Consulting firms such as McKinsey & Company, Boston Consulting Group (BCG), Bain & Company, and Accenture often assess candidates' Excel skills as consultants frequently use Excel for data analysis, modeling, and client deliverables.
• Tech companies like Microsoft, Google, Amazon, and Apple may use Excel assessment tests for various roles, including business analysts, product managers, and project managers, where Excel is used for data analysis and project tracking.
• Retailers such as Walmart, Target, and Amazon may assess candidates' Excel skills, particularly for roles in inventory management, sales analysis, and financial planning.
• Hospitals, healthcare organizations, and pharmaceutical companies use Excel for managing patient data, financial analysis, and operational planning. Examples include Mayo Clinic, Pfizer, and Johnson & Johnson.
• Manufacturing companies like Toyota, General Electric, and Boeing may use Excel assessment tests for roles in supply chain management, production planning, and quality control.
• Educational institutions, particularly universities and colleges, often assess candidates' Excel skills for administrative roles such as academic advisors, finance officers, and research analysts.
• Government agencies at various levels (local, state, federal) use Excel for data analysis, reporting, and budgeting. Examples include the U.S. Department of Treasury, the Internal Revenue Service (IRS), and local municipal offices.
• Nonprofits use Excel for managing donor databases, financial reporting, and program evaluation. Examples include the Red Cross, World Wildlife Fund (WWF), and Amnesty International.
• Insurance companies like AIG, State Farm, and Allstate may assess Excel skills for roles in actuarial analysis, underwriting, and claims management.

Manage Your Time on the Test

Preparing for a Microsoft Excel Assessment Test requires both familiarity with Excel's features and efficient time management strategies.

By following these preparation and time management strategies, candidates can enhance their Excel skills and increase their chances of successfully passing a Microsoft Excel Assessment Test.

Here's a detailed guide on preparation and time management strategies for candidates:

• Start by assessing your current Excel proficiency level. Identify your strengths and weaknesses to focus your preparation efforts effectively.
• Obtain information about the test format, including the number of questions, types of questions (multiple choice, practical tasks), and time constraints. This understanding will help you plan your preparation strategy.
• Brush up on basic Excel concepts such as cell formatting, basic formulas (SUM, AVERAGE), navigating worksheets, and data entry. Ensure you are comfortable with these foundational skills as they form the basis of more advanced tasks.
• Dedicate regular practice sessions to improve your Excel skills. Use online tutorials, practice exercises, and sample tests to reinforce your knowledge and build confidence.
• Prioritize learning commonly used Excel functions and formulas such as VLOOKUP, SUMIF, COUNTIF, and IF statements. These are frequently tested and are essential for data analysis and manipulation tasks.
• Practice under timed conditions to simulate the test environment. Allocate specific time limits for each question or task to improve your time management skills.
• Mimic the test conditions as closely as possible during your practice sessions. Use Excel software or online platforms that offer simulated Excel assessment tests to familiarize yourself with the interface and navigation.
• Take multiple practice tests to assess your progress and identify areas for improvement. Analyze your performance, review incorrect answers, and understand the reasoning behind correct solutions.
• Ask for feedback from peers, mentors, or online communities. Collaborate with others to learn new techniques and approaches to Excel tasks.
• Emphasize accuracy and efficiency in your practice sessions. Aim to complete tasks accurately within the allotted time, focusing on both speed and precision.
• On the day of the test, remain calm and focused. Read each question carefully, manage your time effectively, and avoid getting stuck on challenging tasks. If you encounter difficult questions, move on and come back to them later if time allows.
• After completing the test, review your performance and reflect on areas where you could improve. Use your experience to refine your preparation strategy for future assessments.

Excel Test Passing Score

Scoring models for Microsoft Excel Assessment Tests can vary depending on the test format, the complexity of questions, and the specific skills being assessed.

These scoring models help evaluate candidates' Excel proficiency accurately and objectively, providing valuable insights into their skills and capabilities. Depending on the assessment's goals and context, one or more of these models may be employed to calculate results effectively.

Here are several scoring models commonly used to calculate results:

Multiple Choice Questions (MCQs):

Correct Answers: For Excel Assessment Tests using MCQs, each correct response garners points, cumulatively adding to a candidate’s score. This straightforward scoring reflects the candidate's knowledge of Excel functionalities and theory without the need to directly engage with the software.

Incorrect Answers: In tests that incorporate negative marking, incorrect answers reduce the candidate’s total score. This method is particularly relevant for Excel MCQs to ensure that candidates have a strong conceptual understanding and are not relying on guesswork.

Practical Tasks and Simulations:

Accuracy: When candidates perform tasks in Excel, their responses are scored on precision—accurate completion of each function or formula directly impacts their score, reflecting their practical proficiency in Excel.

Completeness: Candidates are evaluated on the thoroughness of their task completion, with partially completed tasks often receiving some credit. This scoring is particularly applicable in Excel when steps are sequential and build upon each other.

Time-Based Scoring:

Efficiency: Time efficiency can be an essential factor in scoring Excel tests, with points awarded for completing tasks within a set timeframe, reflecting the candidate's ability to use Excel swiftly and effectively in time-sensitive situations.

Speed and Accuracy: A balanced score that takes into account both the rapid completion of Excel tasks and the correctness of the actions encourages candidates to work quickly without sacrificing the quality of their work.

Weighted Scoring:

Task Complexity: More complex tasks, such as those involving advanced functions or data analysis in Excel, may carry a higher score, emphasizing the significance of advanced skills in Excel.

Skill Levels: Tasks are weighted according to difficulty level, with basic, intermediate, and advanced Excel tasks having different impacts on the candidate’s total score, reflecting the depth of their Excel skills.

Rubric-Based Scoring:

Criteria-Based Evaluation: Scoring against a rubric allows for a nuanced assessment of Excel tasks, scoring candidates based on the degree to which they meet various performance indicators or quality criteria.

Subjective Assessment: This method is essential for open-ended Excel tasks where there are multiple ways to achieve the correct outcome. A rubric ensures consistency across subjective evaluations of the candidate's work.

Composite Scoring:

Overall Performance: Scores from various sections of the Excel test are compiled to provide an aggregate score, offering a comprehensive view of the candidate's abilities across different aspects of Excel.

Normalization: For Excel tests administered to a large candidate pool, normalization adjusts scores to account for variances in difficulty levels, ensuring fairness and comparability of performance.

Pass/Fail Threshold:

Minimum Score: Some Excel tests set a pass/fail cut-off point, where achieving a minimum score indicates sufficient proficiency in Excel for the job requirements. This binary scoring method streamlines the selection process by quickly identifying qualified candidates.

Sample Excel Test Questions

Excel Test questions typically cover a range of Excel skills and functionalities commonly assessed in Microsoft Excel Assessment Tests. Job seekers should review these questions, practice similar tasks, and ensure they are comfortable with each concept to perform well on the assessment.

Here are some sample assessment test questions that might be used in a Microsoft Excel Assessment Test, along with recommended answers and considerations for job seekers:

• Answer: You can navigate to cell B5 by either clicking on it with your mouse or by typing "B5" into the Name Box and pressing Enter.
• Considerations: Demonstrating proficiency in basic navigation is essential. Candidates should know how to move between cells efficiently using both keyboard shortcuts and mouse clicks.
• Answer: This formula calculates the sum of the values in cells A1 through A10.
• Considerations: Candidates should understand basic Excel functions and their syntax. Knowing how to use the SUM function for basic calculations is fundamental.
• Answer: You can apply conditional formatting by selecting the range of cells, clicking on "Conditional Formatting" in the Home tab, choosing "Highlight Cells Rules," and then selecting "Greater Than." Enter the value 100 and choose a formatting style.
• Considerations: Candidates should demonstrate an understanding of how to apply conditional formatting based on specific criteria to visually identify important data points.
• Answer: Select the data range, click on the Insert tab, choose the Bar Chart option, and select the appropriate chart type (e.g., clustered bar chart).
• Considerations: Candidates should know how to create basic charts and select appropriate chart types to effectively represent data.
• Answer: Select the data range, click on the Insert tab, choose PivotTable, specify the data range, and drag the relevant fields (product category, region) to the Rows and Values areas.
• Considerations: Candidates should be familiar with creating PivotTables and arranging data fields to summarize and analyze information effectively.
• Answer: Click on the Data tab, choose "Get Data" or "From Text/CSV," select the CSV file, and follow the prompts to import the data into Excel. Use Text to Columns or other formatting tools to ensure data is properly formatted.
• Considerations: Candidates should understand how to import data from external sources into Excel and prepare it for analysis, including handling text-to-columns conversions and data cleansing.
• Answer: Click on the View tab, choose Macros, select "Record Macro," perform the formatting actions, and then stop recording. Assign a shortcut key or save the macro for future use.
• Considerations: Candidates should be familiar with basic macro recording techniques to automate repetitive tasks and enhance productivity.

Why Prepare with JobTestPrep Practice Materials

JobTestPrep's Excel Assessment Test PrepPack™ offers a unique, hands-on approach to preparing for your Excel assessment test, vital for securing employment. By granting access to GMetrix Excel practice tests that closely mimic the real Microsoft Office environment, candidates can engage in an authentic application experience, essential for practical learning. With a year-long access or up to 100 test attempts, you have ample opportunity to refine your skills.

The user-friendly GMetrix Excel practice tests come equipped with a suite of tools that track your progress and provide immediate feedback on your performance. The interface includes a dual-display with an active Excel worksheet and a control panel, allowing seamless navigation between tasks, test restarts, and instantaneous grading views.

Offering both associate and expert practice levels, the GMetrix practice tests encompass the full range of difficulty one might encounter in an actual Excel assessment—from basic to the most advanced questions. This structured approach is designed to incrementally enhance your Excel mastery, ensuring no part of the test catches you off-guard.

Each practice test is available in two modes: training and testing. Training mode is a guided, untimed environment where you can learn at your own pace with real-time feedback and access to a detailed study guide—a virtual tutor for every task. Testing mode offers a time-restricted, simulated test experience, demanding a score of 80% or higher to pass, mirroring the conditions and pressures of the actual assessment.

Upon completion of each practice test, a comprehensive statistics panel allows for reflection and assessment of your progress. It records your performance across tests, detailing grades received and concepts mastered, thus identifying areas of strength and those needing improvement. This feedback loop is crucial for focusing your study efforts effectively, bolstering your confidence and competence as you prepare to pass the Excel assessment test and advance your career prospects.

Preparing for an Excel test using JobTestPrep materials is the most efficient way to get hired due to the comprehensive coverage and practical approach of their preparation resources.

Two Types of Excel Tests

Excel Test - Multiple Choice Questions :

• Description : In this test, candidates answer questions based on their Excel knowledge without directly working on a spreadsheet. The questions test theoretical understanding and the ability to recall Excel functions and features.
• Challenges : This format can be challenging because it requires candidates to select the correct answer based solely on memory and understanding, without the opportunity to apply and verify their knowledge on an actual spreadsheet. Even experienced users can find it difficult to choose the best response without hands-on practice.

Excel Test - Interactive Spreadsheet :

• Description : This test involves performing specific tasks on an actual Excel spreadsheet or an Excel simulation. Tasks may range from simple commands to complex, multi-functional operations requiring a deep understanding of Excel’s capabilities.
• Challenges : Candidates must demonstrate practical proficiency by completing various commands directly in the spreadsheet environment. This type of test assesses the ability to execute functions accurately and efficiently.

Why JobTestPrep Materials are the Most Efficient Way to Get Hired

• JobTestPrep offers extensive practice tests for both multiple-choice questions and interactive spreadsheets, covering a wide range of topics and difficulty levels. These practice tests mirror the format and content of actual Excel tests, providing realistic preparation.
• Each question in JobTestPrep’s materials comes with detailed explanations and solutions. This helps candidates understand not only the correct answer but also the rationale behind it, reinforcing their theoretical and practical Excel knowledge.
• For the interactive spreadsheet test, JobTestPrep provides simulations that allow candidates to perform tasks in a real or simulated Excel environment. This hands-on experience is crucial for building confidence and proficiency in executing Excel commands accurately.
• JobTestPrep materials are designed to cater to different skill levels, from beginners to advanced users. Candidates can follow tailored learning paths that focus on their specific areas of improvement, ensuring a targeted and efficient preparation process.
• In addition to content knowledge, JobTestPrep provides valuable test-taking strategies that help candidates manage their time effectively, approach questions methodically, and reduce test anxiety.
• Simulating real test conditions with timed practice sessions helps candidates build endurance and become familiar with the test’s time constraints, enhancing their ability to perform well under pressure.

By using JobTestPrep materials , candidates can systematically build both their theoretical understanding and practical skills in Excel. This comprehensive preparation ensures they are well-equipped to handle both multiple-choice questions and interactive spreadsheet tasks, significantly increasing their chances of success in the Excel test and improving their prospects of getting hired.

Why Use JobTestPrep:

• Realistic Test Simulation: JobTestPrep provides practice tests that accurately replicate the format and difficulty level of the actual test, allowing you to prepare effectively for the real assessment.
• Eliminate Stress by Practicing Time Management Practice: Practicing with time limits helps you improve your speed and accuracy during the test, ensuring you can efficiently complete all sections within the allotted time.
• Get Detailed Feedback and Customized Study Plan: JobTestPrep's practice tests offer detailed feedback and personalized study plans based on your performance, enabling targeted improvement and optimized study time.

What Are Our Customers Saying About Us

Thank you very much, your presentation is very useful to learn. God bless you.

Another excellent tutorial. I opened my own blank worksheet and copied the original and used pause to carry out step by step what was taught. I'm proud of my work and my 3-D chart all within the print area. This style of tutorial works really well for me. Thank you so much, Vadim you are the Best.

Vadim, outstanding classroom instruction methodology, sir, very helpful, love the soothing background music, your meticulous attention to detail makes for indelible subject matter retention, Bravo! Bravo!

Thank you!! I just passed my excel test that I needed to be promoted!!

Excellent teacher! I like your accent. Your demeanor comes across patient, eager to teach, ability to give relatable examples. English is very good well spoken. Comprehensive knowledge of the subject being taught with multiple strategies to teach.

Im in 8th grade and this was very very helpful to get me to pass my excel certification!!

Thanks for the tutorial. Had my interview/ performance excel test today. I used some of your tips. They came in handy. I will know in two weeks if I got the gig. Thanks!!!

Great information and excellent information. Just what I needed to pass my Excel Employment Interview. Kudos to you and good for me. Muchas gracias and thanks very much.

Thank you so much! I always get anxious during excel practical examinations because I only know basics. This helped me a lot.

Am learning a lot. Thank you very much my friend. Bless you.

Vadim, your uploads are really helpful for me as I'm a relative newbie to Excel. I think your style of presenting is perfect for demonstrating Excel and I'm very grateful for the Bonus question at the end as I'm learning financial mathematics. Thanks again brother, keep up the good work!

insane and in perfect Tutorial. thanks ! Would love to see more Tutorials form you, more in depth with examples, than the one you made.

I just completed the quiz successfully! 7 questions were asked same way in the video! Will definitely learn Excel now! However it's not required for me but good to have Excel knowledge

This information is excellent the way you explain it, thank you!

Thanks a MILLION. I've learnt so much. You are a very good teacher. Thank you!!

Thank you for the Video. I just aced the assessment. I was a little scared though because only about 5 of the questions from here came but I wouldn't have passed this if not of this video, gracias.

Next Step: Prepare for the Assessment Test

Preparing for Microsoft Excel Pre-employment assessment test can be a daunting task, but practicing beforehand can significantly improve your chances of success. Below are 3 reasons why you might consider to get prepared by practicing using materials from JobTestPrep.

Using JobTestPrep practice materials can be an effective way to prepare for the employment assessment test , helping you to build confidence and increase your chances of success. By following these tips and practicing regularly, you can improve your chances and succeed on the Microsoft Excel Test.

What is included with PrepPack

• Microsoft Excel training software
• Two full-length Excel assessment tests (25 question each)
• 180 additional Excel practice questions by topic
• Detailed explanations for each question
• Smart score reports
• Limited Time Bonus - Full preparation courses for MS Office: Word, PowerPoint, Outlook, and more!

All the best on your job interview and assessment test! Good luck & I truly hope you will get hired soon!

Practice Now

Excel Practice Worksheets

Excel practice exercises.

Download our 100% fre e Excel Practice Workbook.

The workbook contains 50+ automatically graded exercises . Each exercise is preceeded by corresponding lessons and examples.

Excel Boot Camp

Excel Boot Camp: Learn Excel inside Excel

The ultimate Excel tutorial - learn efficiently with the "boot camp" approach.

Practice Online

Instead of practicing inside Excel, you can practice online with our interactive Formulas & Functions Tutorial !

Practice Shortcuts or VBA

You can also practice online with our interactive Shortcuts and VBA tutorials:

#1 Excel tutorial on the net

• Assignment Problem

Formulate the Model | Trial and Error | Solve the Model

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the Model

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

1. To formulate this assignment problem , answer the following three questions.

a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0.

b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity.

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

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

Trial and Error

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

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

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.

Note: can't find the Solver button? Click here to load the Solver add-in .

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 TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

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

9. Finally, click Solve.

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

Learn more, it's easy

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

Download Excel File

• assignment-problem.xlsx

Next Chapter

• Analysis ToolPak

Follow Excel Easy

Become an Excel Pro

• 300 Examples

Assignment Problem • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

Excel Tutorial

Excel formatting, excel data analysis, table pivot, excel functions, excel how to, guided projects, excel examples, excel references, excel exercises.

You can test your Excel skills with W3Schools' Exercises.

We have gathered a variety of Excel exercises (with answers) for each Excel Chapter.

Try to solve an exercise by editing some code, or show the answer to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start Excel Exercises

Start Excel Exercises ❯

If you don't know Excel, we suggest that you read our Excel Tutorial from scratch.

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

Excel Practice Exercises PDF with Answers

To solve the following exercises, you will need to know about the SUM , AVERAGE , IF , VLOOKUP , INDEX , MATCH , ROUNDUP , UNIQUE , COUNTIF , LEFT , SEARCH , MID , RIGHT , LEN , FIND , SUBSTITUTE , AND , and SUMIF functions and the Data Bars feature of Excel. You can use Excel 2010 or later, except for the UNIQUE function, which is only available in Excel 2021 .

Download the Practice Files

You can download the PDF and Excel files by filling out this form:

Eleven Practice Exercises with Solutions.pdf [/wpsm_box] [wpsm_box type="download" float="none" textalign="left"] Eleven Practice Exercises.xlsx [/wpsm_box] -->

Problem Overview

Here is a snapshot of the first two problems. The solutions to all problems are given in a separate sheet.

• Exercise 01 – Class Performance Evaluation . You will find these values –
• The total number for each student,
• Their average on those subjects,
• Based on the average score, you will return a GPA. For GPA calculation, less than 60 is B and higher is A .
• You need to find the employee salary in the lookup table on the right side.
• Exercise 03 – Lookup Values (Any Direction) .
• Here your task is the same as the second task. However, this time the lookup range is on the right side. Therefore, you cannot use the VLOOKUP function here.
• You will need to round the sales generated values in this exercise.
• You will need to add the first name and last name.
• Your task is to create a Data Bar for the salary values and hide the salary values.
• Firstly, you need to find the unique values in a list of names.
• Then, you will find how many times that value occurred in that lis
• You need to separate the three parts of a name from a given list.
• You will need to find the total sales for a particular country.
• Your objective is to ensure that users cannot type less than 0 in a column.
• Your target is to determine whether a date is between two dates or not.

Here is a screenshot of the solutions to the first two problems.

What is ExcelDemy?

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

You really help me alot thanks

Hello, Aleem!

Thanks for your appreciation. stay in touch with ExcelDemy .

Regards ExcelDemy

Thanks a lot

Dear Rashmi ,

You are most welcome.

This is very helpful. You are God send

Thanks for your appreciation.

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

Get instant live expert help with Excel or Google Sheets

“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

• CONDITIONAL FORMATTING
• PIVOT TABLES
• LIVE EXCEL HELP
• ONLINE EXCEL HELP
• EXCEL HELP CHAT
• FREE EXCEL HELP
• EXCEL EXPERT HELP
• BOX AND WHISKER
• CONCATENATE
• TIME AND DATE
• DATA VALIDATION
• DATE SERIES
• MAX AND MIN
• ENGINEERING
• STATISTICAL

Here are some problems that our users have asked and received explanations on

• Skip to main content
• Keyboard shortcuts for audio player

The Supreme Court says cities can punish people for sleeping in public places

Jennifer Ludden

U.S. Supreme Court says cities can punish people for sleeping in public places

A homeless person walks near an elementary school in Grants Pass, Ore., on March 23. The rural city became the unlikely face of the nation's homelessness crisis when it asked the U.S. Supreme Court to uphold its anti-camping laws. Jenny Kane/AP hide caption

In its biggest decision on homelessness in decades, the U.S. Supreme Court today ruled that cities can ban people from sleeping and camping in public places. The justices, in a 6-3 decision along ideological lines, overturned lower court rulings that deemed it cruel and unusual under the Eighth Amendment to punish people for sleeping outside if they had nowhere else to go.

Writing for the majority, Justice Gorsuch said, “Homelessness is complex. Its causes are many.” But he said federal judges do not have any “special competence” to decide how cities should deal with this.

“The Constitution’s Eighth Amendment serves many important functions, but it does not authorize federal judges to wrest those rights and responsibilities from the American people and in their place dictate this Nation’s homelessness policy,” he wrote.

In a dissent, Justice Sotomayor said the decision focused only on the needs of cities but not the most vulnerable. She said sleep is a biological necessity, but this decision leaves a homeless person with “an impossible choice — either stay awake or be arrested.”

The court's decision is a win not only for the small Oregon city of Grants Pass, which brought the case, but also for dozens of Western localities that had urged the high court to grant them more enforcement powers as they grapple with record high rates of homelessness. They said the lower court rulings had tied their hands in trying to keep public spaces open and safe for everyone.

Supreme Court appears to side with an Oregon city's crackdown on homelessness

But advocates for the unhoused say the decision won’t solve the bigger problem, and could make life much harder for the quarter of a million people living on streets, in parks and in their cars. “Where do people experiencing homelessness go if every community decides to punish them for their homelessness?” says Diane Yentel, president of the National Low Income Housing Coalition.

Today’s ruling only changes current law in the 9th Circuit Court of Appeals, which includes California and eight other Western states where the bulk of America’s unhoused population lives. But it will also determine whether similar policies elsewhere are permissible; and it will almost certainly influence homelessness policy in cities around the country.

Cities complained they were hamstrung in managing a public safety crisis

Grants Pass and other cities argued that lower court rulings fueled the spread of homeless encampments, endangering public health and safety. Those decisions did allow cities to restrict when and where people could sleep and even to shut down encampments – but they said cities first had to offer people adequate shelter.

That’s a challenge in many places that don’t have nearly enough shelter beds. In briefs filed by local officials, cities and town also expressed frustration that many unhoused people reject shelter when it is available; they may not want to go if a facility bans pets, for example, or prohibits drugs and alcohol.

Critics also said lower court rulings were ambiguous, making them unworkable in practice. Localities have faced dozens of lawsuits over the details of what’s allowed. And they argued that homelessness is a complex problem that requires balancing competing interests, something local officials are better equipped to do than the courts.

"We are trying to show there's respect for the public areas that we all need to have," Seattle City Attorney Ann Davison told NPR earlier this year. She wrote a legal brief on behalf of more than a dozen other cities. "We care for people, and we're engaging and being involved in the long-term solution for them."

The decision will not solve the larger problem of rising homelessness

Attorneys for homeless people in Grants Pass argued that the city’s regulations were so sweeping, they effectively made it illegal for someone without a home to exist. To discourage sleeping in public spaces, the city banned the use of stoves and sleeping bags, pillows or other bedding. But Grants Pass has no public shelter, only a Christian mission that imposes various restrictions and requires people to attend religious service.

"It's sort of the bare minimum in what a just society should expect, is that you're not going to punish someone for something they have no ability to control," said Ed Johnson of the Oregon Law Center, which represents those who sued the city.

He also said saddling people with fines and a criminal record makes it even harder for them to eventually get into housing.

Johnson and other advocates say today’s decision won’t change the core problem behind rising homelessness: a severe housing shortage, and rents that have become unaffordable for a record half of all tenants. The only real solution, they say, is to create lots more housing people can afford – and that will take years.

• homelessness
• Supreme Court