excel assignment for practice with solutions

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

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Microsoft Excel Tutorials : Including Free Practice Exercises!

Right below you find +100 excel-tutorials. all tutorials are super practical and include free exercises. simply download the included sample file and start practicing your excel skills today.

More results...

Excel Line Spacing: How to Adjust Line Spacing (2024)

Excel Line Spacing: How to Adjust Line Spacing (2024) Sometimes, you may need to input a long text into your Excel spreadsheet. When you do, you need to make sure that it's organized and presentable, not only for your sake but for others as well😊 One of the many ways to do that [...]

How to Calculate MEDIAN IF in Excel (“MEDIANIFS” Formula)

How to Calculate MEDIAN IF in Excel ("MEDIANIFS" Formula)Excel is a powerful tool that can help you easily analyze and manipulate data.One of the most useful functions in Excel is the median function, which helps find the middle value in a set of data.However, what if you only want to find the median of [...]

How to SUM by color in Excel: Step-by-Step Guide (2024)

How to SUM by color in Excel: Step-by-Step Guide (2024) Most of you know how to get the Excel SUM. It's one of the most basic Excel functions. What most people don't know is that you can get the Excel SUM by color, too 🤩 There is no built-in function for this but [...]

How to do RANK IF in Excel to Rank With Condition (2024)

How to do RANK IF in Excel to Rank With Condition (2024) Ranking values in Excel is something very common. And with the RANK function, that’s not even a problem. However, if you want to perform a conditional (or criteria-based) ranking in Excel, this might be a little problem at first 😣 That’s [...]

Excel Not Responding – Here is How to Fix it (2024)

Excel Not Responding - Here is How to Fix it (2024) Excel is a powerful tool used by millions of people around the world to handle data 💪🏻 However, it is common for Excel users to get issues with Excel files such as the Excel not responding error. This is such a frustrating [...]

“Reference isn’t Valid” Excel Error: How to Fix (2024)

"Reference isn't Valid" Excel Error: How to Fix (2024) When you're working in Excel to organize and analyze your data, you may encounter different types of Excel errors ⚠️ Getting Excel errors is so frustrating because it hinders you from getting work done 😩 The "Reference isn't Valid" error is one of the [...]

Excel Ran Out of Resources – How to Fix it Step-by-Step (2024)

Excel Ran Out of Resources - How to Fix it Step-by-Step (2024) Microsoft Excel is an amazing tool that we can use to do our calculations. But, sometimes you get a headache with Excel when there are errors 😞 Most of the time you have to sort out these issues by yourself because [...]

How to use the Excel functions TRUE & FALSE (Boolean)

How to use the Excel functions TRUE & FALSE (Boolean) Written by co-founder Kasper Langmann, Microsoft Office Specialist. There are many functions in Microsoft Excel that are conditional by nature. They are based upon logical tests that result in either a TRUE or FALSE outcome. For example, you might manage a sales team. You [...]

How to Switch X and Y Axis in Excel (Flip Chart Axes)

How to Switch X and Y Axis in Excel (Flip Chart Axes) Written by co-founder Kasper Langmann, Microsoft Office Specialist. Knowing how to switch the x-axis and y-axis in Excel will save you a lot of trouble. Microsoft Excel is powerful spreadsheet software that will let you store data and make calculations on [...]

How to Print Labels From Excel: Step-by-Step (2024)

How to Print Labels From Excel: Step-by-Step (2024) Mail Merge is one awesome feature that will help you create mass letters and email messages. But you can also use it to create and print mailing labels for your mailing list 😀 All you need to do is to prepare your label data in [...]

The 9 Best Project Management Templates for Excel (Free)

The 9 Best Project Management Templates for Excel (Free) Project managers are known for their decisiveness and time management. A project's success depends on how a project manager identifies, plans, and employs the resources available for any given project (especially the timespan). If you, too, have a complicated project ahead of you that [...]

Free Excel Dashboard Templates (My Top-6)

Free Excel Dashboard Templates (My Top-6) Excel dashboards are the difference between good and great data presentation. A well-built dashboard can breathe life into the most boring and mundane subjects. Simple Excel dashboards are easy enough to build if you follow a proper tutorial like ours here. But to truly wow your [...]

How to Create Macros in Excel: Step-by-Step Tutorial (2024)

How to Create Macros in Excel: Step-by-Step Tutorial (2024) Get ready to have your mind blown! 🤯 Because in this tutorial, you learn how to create your own macros in Excel! That’s right! And you don't need to know VBA (Visual Basic for Applications)! Instead, you will use the Excel macro recording feature [...]

How to Recover an Unsaved Excel File (5 Free Solutions)

How to Recover an Unsaved Excel File (5 Free Solutions) Losing an unsaved Excel file is a real pain🤕 Whether you lose an entire Excel spreadsheet or just the latest round of edits, it can take a lot of time to fix the problem. But don’t worry! In this guide, I’ve compiled the [...]

How to Insert a Checkbox in Excel (4 Uncomplicated Steps)

How to Insert a Checkbox in Excel (4 Uncomplicated Steps) If you want to collect user input in your spreadsheet there's no better way than the checkbox. User-friendly, slick-looking, and easy for you to work with. Well... After you learn it at least. If you're having a hard time understanding the ins [...]

How to Remove Blank Rows in Excel (3 Easy Methods)

How to Remove Blank Rows in Excel (3 Easy Methods) Choosing the right method to remove blank rows in Excel is crucial - a wrong move can lead to lost data. Method 1: Remove all rows that contain only blank cells Method 2: Remove all rows that contain one or more blank cells Method [...]

How to use the VLOOKUP Formula in Excel (Example)

VLOOKUP is one of the most well-known Excel functions – and not without reason. VLOOKUP’s ease of use and simplicity when “looking up” data is unparalleled in Excel. In this tutorial you'll learn how to create and troubleshoot a VLOOKUP.

How to Add Axis Labels in Excel Charts (X and Y Titles)

How to Add Axis Labels in Excel Charts (X and Y Titles) An axis label briefly explains the meaning of the chart axis. It's basically a title for the axis. Like most things in Excel, it's super easy to add axis labels, when you know how. So, let me show you 💡 If [...]

How to Use INDEX MATCH With Multiple Criteria in Excel

In this tutorial, you'll learn to use the MATCH INDEX functions with multiple criteria by following 5 easy steps. In the end, you'll have built a tool to search through your spreadsheet for information that you define. Don't miss this one!

How to Create a Drop-down List in Excel (in 60 Seconds)

The drop-down list is a great way to look like a superuser and impress your co-workers and boss. At the same time, it’s a very user-friendly asset in almost all custom-made Excel sheets. In this tutorial, we're going to show you the 5 steps to create a drop-down in 1 minute or less. We call it the “1 Minute Drop-Down”.

How to Calculate Present Value (Excel Function PV)

How to Calculate Present Value (Excel Function PV) Present value is the today’s value of a stream of cashflows expected to occur sometime in the future. The concept of present value originates from the fact that money doesn’t retain its value forever. And as time passes, it continues to lose its [...]

How to Use Excel’s Annuity Formula (Example)

How to Use Excel's Annuity Formula (Example) The word of annuity might be one of the unheard or less-heard ones for you but believe me, we see the concept of annuity practically applied almost everywhere around us. Most of the financial products you've arranged with your bank or provider are annuity arrangements. Surprised? [...]

How to Use the “Evaluate Formula” Feature in Excel

How to Use the "Evaluate Formula" Feature in Excel Did you just put together a very smart formula in Excel that makes you want to give yourself a pat on the back? But plot twist – Excel returns an error instead of the results? 😲 How do you troubleshoot the [...]

How to Calculate a Z-Score in Excel (Formula Guide)

How to Calculate a Z-Score in Excel (Formula Guide) The Z-score is a very important statistical measure that tells how far each value of a dataset lies from its mean. Since standard deviation is the measure of the spread of a dataset, Z-score evaluates the distance of each value from the [...]

How to Calculate Future Value in Excel (FV Function)

How to Calculate Future Value in Excel (FV Function) One of the key driving factors we all have in our head while making investments is – to what value would it grow after a given number of years? This is what we call the future value of investment. Just like money [...]

How to Calculate Percentiles in Excel Fast (Formula)

How to Calculate Percentiles in Excel Fast (Formula) We’ll have seen our result cards with a certain percentile mentioned on it (good or bad, besides the point 😝) Often people confuse percentiles for percentages, and others do not know the concept of percentiles. This tutorial is a step-by-step guide on what percentiles are [...]

Profit Margin Formula in Excel: A Step-by-Step Guide

Profit Margin Formula in Excel: A Step-by-Step Guide Profit margin is the simplest and the most common financial analysis metric. If you want to quickly sketch your head of how a company is doing – get its profit margin ratios. Three numbers are you are all sorted. Yes, you heard that [...]

How to Duplicate a Sheet in Excel (3 Methods)

How to Duplicate a Sheet in Excel (3 Methods) There are many situations where you need to copy data in Excel. When copying data from one cell to another, it’s easy. But what if you need to copy data from an entire Excel worksheet? 😨 Well, it’s easy too. You just need to [...]

How to Use The ROI Formula in Excel (Step-by-Step)

How to Use the ROI Formula in Excel (Step-by-Step) If you have two investments in your portfolio, how do you compare and evaluate them both? Out of many ways, one key investment evaluation metric is the Return on Investment (ROI) ratio 🗝 It expresses the net profit on an investment as [...]

Use This Excel Formula to Remove Duplicates (Fast)

Use This Excel Formula to Remove Duplicates (Fast) If you’ve ever had to deal with duplicate values in your Excel worksheet, you would know how very troublesome they can be to manage. You can manually find and delete duplicate values when the worksheet is small but if the data is large, deleting duplicate [...]

Excel Most Popular Functions Practice

Description

Additional information, reviews (2), intermediate excel exercises, real exercises of the most popular excel functions, excel practice description.

Practity’s Intermediate Excel exercises offer the perfect opportunity for Excel students to master the most important functions and formulas of spreadsheets. With a focus on essential concepts such as conditional statements, counts, duplicates, SUMIF, VLOOKUP, and more. This type of Excel challenges provide a hands-on approach to learn how to combine and implement these functions to solve real-world problems and calculate KPIs.

You are given an Excel file with data of 700 purchase orders of one of the most important products of a chemicals company. You will have to gather, analyze and manipulate the data in four sheets in order to check controls, discover useful information, manage risk and draw conclusions for the company decision making. The Excel exercises intermediate download includes more than 30 Excel practice questions of the most common functions and tools used in the real world. You will have to perform the same tasks a business analyst, controller or middle office have to do on the spreadsheet on a regular basis.

Why try these Excel Practice Projects?

Real-world Application : The assignments offer real-life scenarios where Excel is the tool to go, allowing learners to confront practical problems and apply their knowledge in a meaningful way.
Master Complex Functions : Practice is the only way to truly master Excel. By working through these challenges, Excel intermediate students can build a deep understanding of how to use and combine functions to achieve specific goals.
Critical Thinking and Problem Solving : Our exercises foster creativity and problem-solving skills, encouraging learners to think critically and approach Excel practice problems from different angles, preparing them for real-world Excel challenges.

WHAT YOU WILL PRACTICE

1) Data querying formulas. You will learn about finding, matching and retrieving values in order to get insights about the business. 2) Compare data and check conditions so that you can make decisions based on results. 3) Common Excel tools and tricks to make professional spreadsheets ready to be used in presentations and be shared with others. 5) Summarize data with breakdown tables. 6) Combine and merge functions. 7) Real applications of the following functions: IF, COUNTIF, AND,OR, VLOOKUP , IFNA , text functions, nested functions, pivot tables, conditional formatting, sort, filters, sheet formatting, workbook protection.

REQUIREMENTS

These Intermediate Excel exercises require a minimum knowledge of Excel. Students must have attended a complete Excel intermediate course and completed successfully regular Excel exercises for beginners. The spreadsheet included in the project have been done with latest Excel version . It is then compatible with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.

After purchase, you will receive a Zip file attached in the confirmation email. The Zip includes the following files:

One PDF with the Excel assignments.
One PDF with the Excel exercises solved and explained. It includes detailed explanations and print screens about each Excel practice problem. You will see in each image the function, the expected outcome and how to find the requested options and tools. – Excel file with data (4 sheets). – Excel file with all problems solved.
Email Address *

Specification: Excel Most Popular Functions Practice

2 reviews for excel most popular functions practice.

scargot – 11 October, 2023

I liked a lot. The exercises are focused on the important stuff and very well explained. It helped get confident with spreadsheet. Recommended!!

Vlad Korse – 20 October, 2023

Very good. Some exercise was challenging but the documentation is very helpful. I can move now to more difficult things.

Only logged in customers who have purchased this product may leave a review.

Related Products

Excel Templates Practice

Excel Projects for Practice

Excel Data for Practice

Real Excel Exercises for Beginners

20 Real LookUp Exercises

Excel Exercises for Job Interview

Terms of Use

Shopping cart

VLOOKUP Practice Example File [With Solutions]

What is vlookup.

Vlookup stands for Vertical Lookup and is part of a group of functions that I like to call "Lookup Functions". A Lookup Function's sole purpose is to pull in information from a table of data based on a unique identifier (I will refer to these as “IDs”).

It’s like telling your dog to find your yellow ball ( Lookup Value ID ) and that it's somewhere in the backyard ( Table Array ). Okay, give your dog a little more help because you have a REALLY BIG backyard!

You then specify it’s on the left side of the backyard ( Column Reference ) and that he has to bring back the exact ball you asked for ( Range Lookup ).

Kind of make sense? For a simple spreadsheet-based example let’s look at the data below:

In Table 1 we have a data table with an Employee ID, Last Name, & First name field. Let’s say that we received Table 2 from the Payroll department and we need to fill in the last name associated with the Employees ID.

With the small amount of data in this example, you probably could get away with manually looking up each person’s ID number and copying/pasting their last name into Table 2 , however, there would be two things that would prevent you from wanting to do this:

The solution is manual and therefore time-consuming
You are keying in data by hand which should always be avoided if possible

Plus, what if you had to fill in the last names of a thousand employees? This would turn into an all-day exercise! Luckily we can use an Excel Lookup function to do this search for us. Watch the below animation to see how quickly I can use the VLOOKUP function to pull in the data I want.

VLOOKUP Function Inputs

There are 3 required and 1 optional input in the VLOOKUP function. The below table describes all four inputs.

Possible Errors That Can Occur

There are 3 different errors that can occur if your VLOOKUP function cannot find a match or is set up improperly.

#REF! - If your function's Col_Index_Num is larger than the number of columns in your Table_Array , your VLOOKUP function will return a #REF! error.
#VALUE! - If your function's Col_Index_Num is less than 1, your VLOOKUP function will return a #VALUE! error.
#N/A - If you input FALSE (or 0) for your Range_Lookup parameter and no exact match can be found, your VLOOKUP function will return a #N/A error. You can hand this by wrapping an IFERROR function around your VLOOKUP function.

VLOOKUP Function Practice Examples

Here is an Excel file you can download to see ways you can apply the VLOOKUP Function in your spreadsheets!

There are both working tabs and solution tabs provided within the Excel file so you can reference the answers if you can’t solve the task on the first try.

Example 1: Add First/Last Name From Another Table

In this practice example, you are asked to add the first and last name of the employee to a Pay Report . You will need to use the Employee ID to Vlookup the name columns from another table so you don’t have to manually type out all the names.

Example 2: Vlookup From Multiple Tables

In this practice example, you will need to reference two separate data table sources in order to complete the requested Pay Report .

Example 3: Vlookup Using Approximate Matching

In this practice example, you will need to utilized VLOOKUP’s approximate match capability to categorize each employee to their proper payroll pay band based on the amount they are being paid.

Why You Should Learn VLOOKUP

I currently sit right across from the Human Resources department and I always find myself listening in on the questions that our hiring managers ask prospective hires over the phone. Over time I recognized that certain questions were always asked to size up abilities pertaining to their analytic abilities. I found it really fascinating that computer skills (especially Excel) could be analyzed by one very simple question: Do you have experience using VLOOKUP?

VLOOKUP seems to be that one function that basic users (including myself at one point) have never heard of and that even recognizing the function’s name puts you into a category of an “experienced Excel user”. While I can agree that VLOOKUP is an essential function to know, I probably would not categorize all people who know how to use it as experienced users, but it is definitely a stepping-stone towards becoming one.

Understanding how to use VLOOKUP was the pinnacle moment in my Excel experience where I realized that there was way more to spreadsheets than adding and multiplying numbers. Hopefully, the information on this page has helped you get a grasp of what Vlookup is and how it can be used.

Other Lookup Functions To Learn

Microsoft Excel has additional Lookup functions that you can use within your spreadsheets. Each function has its own pros and cons.

XLOOKUP (New!)
INDEX/MATCH

XLOOKUP was released in 2020 and was created to be an all-in-one solution for lookup needs. This is the function I would recommend you learn next as it has the most diversity in its use cases.

You can learn more about this function in my dedicated XLOOKUP guide which will teach you everything you need to know.

Additional Vlookup Resources

The Vlookup Formula: Why Your Employer Wants you to Know How to Use It
VLOOKUP Explained in Simple Terms at Starbucks

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Keep Learning

Build A United States Dashboard Map Chart In Excel

Map Charts In Excel Are Limited… Let’s face it, Excel’s built-in Map Chart feature seemingly was thrown together overnight. Its...

Will VBA Die in 2022? Is It Worth Learning? [Inside Scoop]

VBA is a coding language used by millions of people across the world to automate tasks in Microsoft Office products....

4 PRO Ways to Create A Filtering Search Box [Excel Download]

Creating A Search Box In Excel It's unfortunate Excel does not have a form control search box (maybe in the...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

Excel Beginner Exercises

In this article, I’m going to walk you through a couple of Excel exercises that are perfect for beginners. We will slowly ramp up the difficulty from beginner to advanced so if things are too easy for you, just scroll down a bit.

Getting started with formulas

‘Formulas?!’ You may be thinking. ‘That’s way too advanced for a first Excel beginner exercise.’ It’s not really. Let me explain why: formulas and functions are the bread and butter of Excel. If you want to work with Excel, it is wise to at least know what a formula looks like and why a formula is handy. That’s why we’re starting with this. Don’t worry, they are really not as complicated as they sound.

So let’s jump into it. We will be calculating the result of 32+57. However, we won’t be doing that ourselves, we will be making Excel do the hard work for us.

To start with a formula in Excel, we have to fill in the equals sign = into a cell. After that, we add our formula, so 32+57 in this case. The resulting cell content becomes =32+57 .

Note: all of our exercises have a Show answer button in the top-right that you can click if you cannot figure out what the answer has to be. After clicking Show answer , you can click on the cell to see the formula that was used in the formula bar. You can also reset the editor back to its default contents using the Reset button.

I hope the number 89 is showing in cell A1 of the spreadsheet editor above. The cool thing about this is that you can substitute the + for a minus – and it will also work. Or you can use the asterisk * to multiply. Or the slash / to divide. You can try all of these different calculations in the editor above if you want.

And just like that, you have learned how Excel formulas work! You can now get rid of your calculator 😉

Using Excel beginner functions: SUM

Now that we’ve seen how to include formulas in Excel spreadsheets, we can get to the real deal: functions. Functions are basically formulas with names. It’s easiest to understand with an example:

The SUM function will sum the values that you give it. We can recreate the formula that we created in the exercise above ( =32+57 ), but using the SUM function. That would look like this:

=SUM(32,57)

If you write this in a cell, it will show 89 . Exactly as you may expect. But you can add more numbers (separated by commas) to the function: =SUM(32,57,1) will result in 90, for example.

In this next exercise, we will combine the SUM function with another interesting Excel feature: references. So what are references? Once again, an example will explain this very quickly. Look at the following formula:

=SUM(A1,A2)

What do you think the result will be? The answer is: it depends. That’s because the formula uses references to cells A1 and A2 .

We know the SUM function will sum the values that you give it, but in this case we give it references to cells. When you do that, it will instead sum the contents of the cells that you reference. So the result will be different depending on the values in cells A1 and A2 . If cell A1 contains 1 and cell A2 contains 2 , then the result will be 3 .

These references can be super handy because they update automatically if the contents of the cells change. Whenever you edit the value in cell A1 , the result of =SUM(A1, A2) will also automatically update! And that’s why references are so ridiculously useful.

Now, let’s get to the exercise. We will be using the SUM function to calculate our total monthly income after expenses.

Calculating income using SUM

I hope that wasn’t too difficult. Or if it was a little difficult, that when you looked at the answer, it made sense.

Note about the answer: if you looked at the answer to the above exercise, you saw a notation that you may not have seen before. =SUM(B2:B5) . The colon : is used to mean a cell range . In this case, that means cells B2 up to and including B5 . So it is equivalent to using =SUM(B2,B3,B4,B5) . In Excel, there are often multiple ways of solving the same problem. I’d encourage you to use the solution that is most intuitive to you.

A step up: Average

Calculating the average of a group of numbers is quite simple: you sum them all up and divide by how many number you have. For example, the average of the number 1, 2, 3, 4 and 5 is: 1 + 2 + 3 + 4 + 5 divided by 5, because there are 5 numbers.

You could do this in Excel by typing =SUM(1,2,3,4,5)/5 into a cell. But there is an easier way. You can simply use the AVERAGE function. Let’s do so in an Excel exercise.

Note: you may have noticed by now that both of these functions are written in all-caps. That’s just the way function names in Excel are. So it may look like I’m screaming SUM at you, but that’s just how it is written.

More practical: Concatenate

We’ve seen functions that use numbers, now let’s look at another type of function: text functions. They are functions that take text as input or that result in text output (or usually both).

Before we get to the exercise, you need to know something about text in Excel formulas. Texts in Excel formulas are almost always surrounded by double quotes “. That’s just the way to let Excel know that it is a text.

Concatenate first and last names exercise

For the fourth of our Excel beginner exercises, we will be combining the contents of two cells. The first cell will have a first name, the second cell will have a last name in it. We would like to fill the third cell with the first name, then a space and then the last name.

We can do this using the CONCATENATE function. The CONCATENATE function looks like this: =CONCATENATE("text1","text2","text3") . If we would run this function, it would result in text1text2text3 . So it just combines the texts that you give it.

More Excel Exercises

That was it for our Excel beginner exercises. I hope you’ve learned something new today. If you just can’t get enough of these types of exercises, we have an Excel exercises page filled to the brim with exercises to dig your teeth into.

5 thoughts on “Excel Beginner Exercises”

Pingback: How to Learn Excel Fast: 3 Principles to Learn Excel Quickly

Helpful and clear for beginners.

Written with very clear instructions except with the CONCATENATE. Unfortunately I cannot figure this simple formula.

This is awesome

Excel IF Function

The IF function (or "IF statement") in Excel is a simple function that allows you to build logic into your worksheets. The IF function evaluates a logical test and returns one value if the logical test condition is true, and another value if the condition is false. Learning this function will help you begin to write complex logic to power your spreadsheets and models. For example, if a cell in your spreadsheet contains the top speed of a vehicle, you might write an IF statement which returns the text "fast" if the speed is above 30, and returns the text "slow" if the speed if below 30.

Syntax of the IF Function

The IF function takes three arguments (function inputs inside the parenthesis separated by commas):

=IF(condition, value_if_true, [value_if_false])

condition : This is a statement or expression, usually comparing two values in the form of a logical comparison that evaluates to true or false. A logical comparison requires one of the logical operators in the table below.
value_if_true : This is the value that will display if the condition is true.
value_if_false : This optional argument is the value that the function will display if the condition argument is false.

The value_if_true and value_if_false arguments can be any type of values, including cell references, numbers, text, dates, as well as other functions or formulas.

Logical Statements in Excel

You may be wondering how to write a logical comparison for the first part of your IF statement. To compare two values, use one of the six logical comparison operators from the table below.

You can use these comparison operators to compare text values, dates, numbers, and more.

See the table below for some examples of logical comparisons in Excel.

Using the IF Function in Excel

Recall the syntax of an IF statement:

=IF( condition , value_if_true , value_if_false )

The first argument is the condition, or the logical test that compares two different values. If the condition is true, Excel returns the second argument (shown above as value_if_true). If the condition is false, Excel returns the third argument (shown above as value_if_false). See the example below:

=IF( C2>30 , "Fast" , "Slow" )

The IF statement above compares the value in cell C2 to 30. Excel returns the text "Fast" if speed is greater than 30 (because the condition is true), and returns the text "Slow" if speed is NOT greater than 30 (because the condition is false).

You could also omit the third argument:

=IF( C2>30 , "Fast" )

This IF statement would display the word "Fast" if speed was greater than 30, and would otherwise simply be blank. This is because the IF statement does not have the value_if_false argument.

Combining IF With Other Excel Functions

You saw how an IF statement can return text values, but we can also combine any other function in Excel with any of the arguments of an IF statement. For example, take this exercise using the SUM function : IF Texas and California sales combined are at least $500, commission is 10% on the total. Otherwise Commission is 0.

=IF( SUM(C2, C3)>=500 , SUM(C2, C3)*0.1 , 0 )

The formula above says If Sales for TX + CA is greater than or equal to 500, then return 10% of the total. Otherwise, return 0.

Because the SUM function in the condition would return 865, which is greater than 500, our IF statement would return the value_if_true argument. The value_if_true argument returns the sum multiplied by 0.1, so this IF statement would return 86.5.

Nested IF Statements

You can combine any number of IF Statements in Excel by "nesting" them inside the second or third arguments of another IF statement. For example, say you are shopping for a new car. You want a newer car, but you also like older Cadillacs. You decide to look for a car that meets the following conditions:

If the car is a Cadillac, buy it.
If the car is not a Cadillac, buy it only if it was made after the year 2010.
If the car is not a Cadillac and was made in 2010 or earlier, do not buy.

Below we see different car options in a table. We can write a nested IF statement, starting with row 2, to help you determine whether to buy the car or not.

=IF( C2="Cadillac" , "Buy" , IF(B2>2010, "Buy", "Do not buy") )

The IF statement above says If make is equal to "Cadillac", then buy it. Otherwise, if the year is greater than 2010, then buy it. Otherwise do not buy it.

Starting with row 2, the make is not Cadillac, so we then go to the value_if_false clause. The year is not greater than 2010, so we would not buy the car.

For row 3, the make is Cadillac, so we would buy the car.

In row 4, the make is not Cadillac so we go to the value_if_false clause again. This time the year is greater than 2010, so we would buy this car.

Using IF Functions with AND and OR Functions in Excel

Say you want your IF statement to trigger the value_if_true_ argument only if several different conditions are all true. The AND function takes comparisons as its arguments and returns TRUE if ALL of the arguments are true. For example, the function =AND(A1>B1, A2=B2, A3<=B3) will return TRUE only if ALL three arguments are true. It will return FALSE if any of its arguments is false.

Now imagine you want your IF statement to trigger the value_if_true_ argument if at least one of several different conditions is true. The OR function also takes comparisons as its arguments and returns TRUE if AT LEAST ONE of the arguments is true. For example, the function =OR(A1>B1, A2=B2, A3<=B3) will return TRUE if AT LEAST ONE of the three arguments is true. It will return FALSE only if ALL of the arguments are false.

To use AND or OR with an IF function, you simply replace the condition argument with the AND or OR function:

=IF( AND(condition_1, [condition_2]...) , value_if_true , value_if_false )

=if( or(condition_1, [condition_2]...) , value_if_true , value_if_false ), continue to if practice exercises →.

Practice And Learn Excel Online For Free

Pivot table.

Post published: November 9, 2019

Pivot Table is a great tool for summarizing and analyzing data in Excel.

We can use a Pivot Table to perform calculations on our data based on certain criteria. For example – Sales per Store, Sales per Year, Average Discount per Region, and many more…

Here are some of the advantages of using Pivot Tables:

There is no need to use formulas in order to perform calculations.
We can perform complex calculations in a quick and simple way.
The summarized data is dynamic and can easily be applied to different sets of data

Click to download our absolutely FREE Pivot Table Exercise

Table of Contents

Creating a Pivot Table

To create a new Pivot Table, we first need to select the data range which we would like to analyze, then click on one of the desired cells in our data range, then click Insert tab, then Pivot Table.

Let’s assume we want to analyze a database of cars sold by a car vendor:

To create a new Pivot Table:

We will click on one of the cells in the data range.
We will go to the Insert tab and click on Pivot Table:

Next, we will confirm that the selected range is indeed the right range.

Last, we will select “New Worksheet” to create the Pivot Table in a new worksheet, or “Exisiting Worksheet”, to place it in an existing worksheet.

After we decided to create a Pivot Table, we can see all the column headers – these are the fields from our database which we can work with:

To start creating our Pivot Table, we can drag the different fields to the following areas:

Rows – Here we will choose the field/s which we would like to base our Pivot Table rows upon.
Columns – Here we will choose the field/s which we would like to base our Pivot Table columns upon.
Filters – Here we will choose the field/s by which we would like to filter our data in the Pivot Table. i.e.- we would choose “Year” to filter by a specific year.
Values – Here we will choose the field we want Excel to calculate and our desired calculation.

Creating a basic Pivot Table – Example

One of the most basic examples of using a Pivot Table is summing values of a specific field based on a criteria that appears in a different field.

In order to do so, we will drag the field which we would like to analyze into the “Rows” area or “Columns” if we would like to present the analysis in columns. We will the drag the field we want to sum into the “Values” area:

Changing the way Values are calculated

We will notice that most times, the basic calculation we will get when dragging a field to the “Values” area will be “Sum”.

We can change the calculation by clicking the field after we dragged it into the “Values” area, then “Value Field Settings…”, which will open a menu where we can choose to sum, count, average and many more calculations:

Segmentation to Columns and Rows

We can segment the data using rows and columns simultaneously by dragging fields to the “Rows” and “Columns” areas:

Performing multiple calculations on the same field

We can perform a number of different calculations on the same field by dragging the field several times to the “Values” area and changing the type of calculation in each of the columns:

Segmentation of more than one field

In the Pivot Table, we can segment based on more than one field by dragging several fields into the “Rows” area:

Designing a Pivot Table

Changing the pivot table design to a classic table design.

In order to give the Pivot Table a “classic” look, where each field is presented in a different column, we will click the Pivot table, click on “design” and perform the following steps:

Click on Report Layout
Click on “Show in Tabular Form” to show the table in a classic format
Click on “Repeat All Items Labels” to show all item labels.
We can click on “Do Not Show Subtotals” to hide the subtotals in the newly created table.

This is the process and final result:

Formatting a Pivot Table field

We can quickly select the way we wish to format a certain value field, by right-clicking the field and then clicking on “Format Cells”, or directly on “Number Format”, if we wish to format the values as number and add 1000 separator (4,524,254 instead of 4524254):

Designing missing values and errors

Filtering a Pivot Table

Filtering existing fields in a pivot table.

We can filter data shown in the Pivot Table rows simply by clicking the corresponding button in the desired field. For example, to filter the “Gear” field, we simply have to click the button next to the field name:

Filtering values in a Pivot Table

What if we wanted to filter the values in our Pivot Table?

To do so, we can start our filtering by clicking the filter button in one of the fields, then click on “Value Filters”, following which we will be able to see the various value filtering options.

Here’s an example of how to filter values greater than 40,000:

Adding an external filter to a Pivot Table

If we want to filter based on a field that is not currently in the Pivot Table, we could drag that field into the “Filters” area:

Please note – we can add more than one field to the “Filters” area.

Sorting values in a Pivot Table

If we want to sort our fields, we just have to right-click on the desired field and click on “Sort”:

Updating and refreshing the Pivot Table data

After updating the source data, we have to refresh the Pivot Table in order for the new data to be reflected in the Pivot Table. We can do that by right-clicking the table and clicking on “Refresh” or by Refresh/Refresh all in the “Data” group

Adding new data at the end of the data range

If we want to add new data to our Pivot Table that will be added at the end of the previously used data range, we need to update the source data’s range by clicking on “Change Data Source” in the “Data” group:

Another way of dealing with this issue is by adding the new data in the middle of the previously used data range and then refreshing.

Automatically update Data Source Range when adding new rows by using Tables

Another way to save time if we are planning to update the data source range often is changing the data source range to a table by clicking in “Table” in the “Insert” tab or by clicking CTRL+T

Now we can create/update the Pivot Table that will use the table as the source data, and when the table will be updated- the Pivot Table’s source data range will be updated as well. Here’s how our Data Source looks like:

Show Values As

Presenting a breakdown of a value in a Pivot Table

Whenever we like, we can present all the items that are calculated in a certain cell in the Pivot Table by double-clicking that cell. This will result in a new sheet opening:

Grouping Data

We can group data presented in the Pivot Table’s rows and columns with “Group” and reverse it with “Ungroup” by right-clicking one of the cells:

Date data will usually be grouped automatically to months/years

We can also group numerical data (i.e 1-100, 101-200, etc.)

Creating Pivot Charts

We can add charts to existing Pivot Tables or create new charts based on a new Pivot Table.

Existing Pivot Table – We will click on the “Analyze” tab and then on “Pivot Chart” in the “Tools” group (we have to select a cell in the Pivot Table before doing this)
Creating a new Pivot Table – “Insert” tab -> “Pivot Chart” in the “Charts” group (we have to select the desired source data before doing this)

When we click on the Pivot Chart, the names of the categories will look like this:

Like any other chart, we can control the axis’ directions and the chart type by clicking on the “Design” tab. We can, for example, replace the X and Y axis by “Switch Row/Column” in the “Design tab”. We can also change the Chart type:

It is important to note that Pivot Charts behave exactly as Pivot Tables, so each functionality that can be used in Pivot Tables, can also be used in Pivot Charts.

Adding Slicers / Timelines to a Pivot Table

Adding slicers to a pivot table.

We can add Slicers to our Pivot Table / Chart, which will enable visually filtering the field, by clicking on the “Analyze” tab and then on “Insert Slicer”. Here’s how it looks:

We can have multiple slicers to our Pivot Table, which will work simultaneously:

We can select several values in the Slicer by using CTRL/ SHIFT.
To cancel the filtering of a Slicer, we will click on this button at the top of the Slicer:

Adding a Timeline to a Pivot Table

For date fields, we can add a Timeline by clicking on the “Analyze” tab and then on “Insert Timeline”:

Pivot Table Calculated Fields

We can perform calculations within the Pivot Table itself, Instead of creating calculation columns in the source data. For that, we can use a “Calculated Field”.

A Calculated Field is calculated based on the sum of a certain field.

We will add a Calculated field by clicking on: Analyze tab -> Fields, Items & Sets -> Insert Calculated Fields:

We will name each Calculated Field and write the desired formula for it (you can insert the desired field by double-clicking it).

Here’s an example of calculating the Sales amount after a 2% commission:

Practice Pivot Table

Click here to download our FREE Excel Pivot Table exercise , in which you will be able to practice and learn how to create Pivot Tables, design them, update their data, create Pivot Charts, adding Slicers and many more Pivot Table tips and tricks!

Having an issue with the formulas' language? check out this post

Yes, that would be helpful!
I don't know
Intermediate

Terms and Conditions - Privacy Policy

Excel Solver Exercises: 8 Advanced Problems

In this article, you will get eight exercises related to the Excel solver. These problems are quite difficult to solve. You should have an advanced level of Excel knowledge to solve the problems. To solve the problems, you should know about the following: the SUM , SUMPRODUCT , HLOOKUP , COUNTIF , IF , and OR functions and Enable solver , solver properties , solver example , choosing the best project , portfolio optimization with solver , solver for linear programming , usage of solver to minimize cost , assign work using an evolutionary solver , and schedule optimization . Excel 2010 or a higher version is required to solve all the exercises about Excel solver.

Download Practice Workbook

You can download the Excel file from the following link.

Solver Exercises.xlsx

Problem Overview

There are eight exercises related to the solver in this file. The “Problem” sheet shows the exercises, and the “Solution” sheet shows the problems worked out. Additionally, the solver settings for each exercise are provided in the “Reference” sheet. You can load the values from there into the solver for each problem. The following image shows the first problem of the article.

The first table shows the shipping costs from one port to another. Then, the second table shows the product requirement for each port. After that, there is a table that contains the information about the warehouse inventory. Your task is to find the values using the solver that will minimize the shipping costs from the warehouses to the outlets.
Set Objective: G27
By Changing Variable Cells: D15:F20
Subject to the Constraints: C15:C20=G15:G20, D25:F25>=0
Select a Solving Method: Simplex LP

The following animated image shows the solution to the first problem.

Set Objective: I38
By Changing Variable Cells: C32:E34
Subject to the Constraints: C35:E35>=C37:E37, C41:E41>=C43:E43,F32:F34<=H32:H34
Firstly, the combined production capacity is 300 units per day.
Secondly, the company needs 50 units of Product A to fill an existing order.
Thirdly, the company needs 40 units of Product B to fill an expected order.
Fourthly, the market for Product C is relatively limited. So the company is not interested in producing more than 40 units of this product per day. Additionally, the Product D to Q should be more or equal to 15.
Set Objective: E65
By Changing Variable Cells: C48:C64
Subject to the Constraints: C48>=50, C49>=40, C50<=40, C51:C64>=15,C65=400
Set Objective: J83
By Changing Variable Cells: H71:H82
Subject to the Constraints: H71:H82>=60000, H83=E83
Select a Solving Method: GRG Nonlinear
The condition is, Total Number of Employee >= Number Needed is given in row 96 and 98.
Set Objective: C100
By Changing Variable Cells: B88:B94
Subject to the Constraints: B88:B94=integer, D96:J96>=D98:J98
Firstly, find the maximum value of NPV.
Secondly, the year 1 value should be <=50,000,000 and year 2 value should be <=20,000,000.
Thirdly, selecting a project means 1 and discarding means 0. So use this.
Set Objective: C104
By Changing Variable Cells: B107:B115
Subject to the Constraints: B107:B115=binary, C117:D117<=C118:D118
Firstly, the amount that the credit union will invest in new-car loans must be at least three times the amount that the credit union will invest in used-car loans. The reason is: that used car loans are riskier investments.
Secondly, car loans should make up at least 15% of the portfolio.
Thirdly, unsecured loans should make up no more than 25% of the portfolio.
Then, at least 10% of the portfolio should be in bank CDs.
Afterward, the total amount invested is $5,000,000.
Finally, all investments should be positive or zero.
Set Objective: C132
By Changing Variable Cells: D125:D129
Subject to the Constraints: D125>=D126*3, D130=C122, F128<=0.25, F129>=0.1, F132>=0.15
Set Objective: P146
By Changing Variable Cells: C140:C179
Subject to the Constraints: C140:C179<=4, C140:C179=integer, C140:C179>=1, Q141:Q144=0
Select a Solving Method: Evolutionary

Finally, the following image displays the solution to the first exercise.

Thank you for reading this article. By completing these Excel Solver exercises , we hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. Moreover, if you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!

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

Hi Mohammed Hassen ,

Thank you so much.

Thanks Regards ExcelDemy