Intermediate Excel practice
You can already write a formula, copy it down and make a simple decision with IF. This path teaches a sheet to answer questions about its own data: how much did one region sell, how many tickets are still open, which price belongs to this product code, what is the average when a few values are extreme.
The exercises move from conditional sums and counts to lookups, tiers and error handling, then to ranking, text clean-up and dates. They use small business datasets, and each formula is checked on other data as well, so it has to answer the question and not only today's numbers.
What you will be able to do
- Total, count and average only the rows that meet a condition, and combine several conditions, including a window of dates.
- Look up a value by its code with VLOOKUP or XLOOKUP, and show a clear message when the code is missing.
- Place a value in a tier with IFS, and test data with ISNUMBER and ISBLANK before you calculate with it.
- Choose between the average and the median, and rank values with RANK.
- Clean and split text, and turn imported text into numbers.
- Work out tenure and renewal dates with DATEDIF, EDATE and EOMONTH.
Skills covered
- Absolute references
- Criteria
- Dates
- COUNTIF
- INDEX and MATCH
- LARGE
- VLOOKUP
- XLOOKUP
- AVERAGE
- AVERAGEIF
- AVERAGEIFS
- Comparison operators
- Complete months
- Complete years
Common mistakes to watch for
- Ranges that do not line up
- SUMIFS and COUNTIFS need the range you test and the range you add to be the same size and to match row by row. A range that is one row short returns a wrong answer or an error.
- Replacing an error with zero
- A zero hides the fact that something was not found. Show a message that says what happened, and wrap only the lookup you expect to fail.
- Reporting an average of skewed data as typical
- When a few values are far larger than the rest, the average describes none of them well. The median is the fairer summary.
- Leaving a lookup table unlocked
- A lookup copied down moves its table with it unless the table is anchored with dollar signs, and rows near the bottom start returning nothing.
The exercises, in order
Total Revenue for One Region
Add up only the values that meet a condition, using a formula that reads the data instead of repeating a number you worked out by hand.
Average Order Value with AVERAGEIF and AVERAGEIFS
Average only the rows that meet one or several conditions, and choose AVERAGEIF or AVERAGEIFS accordingly.
Largest and Smallest Deals with MAXIFS and MINIFS
Find the largest or smallest value among only the rows that meet one or more conditions.
Count Support Tickets with COUNTIF and COUNTIFS
Count rows that meet one or several conditions, and write criteria for 'not equal to' and 'starts with'.
Look Up a Product's Price
Retrieve a value from a table by searching for a key, so the answer follows the key in the cell instead of a fixed row.
Volume Discount Tiers with IFS
Choose between several results with IFS, order the tests correctly, and handle values that sit exactly on a boundary.
Typical Salary: AVERAGE, MEDIAN, LARGE and SMALL
Choose between the average and the median when data has an outlier, and pick the nth largest or smallest value with LARGE and SMALL.
Sales Leaderboard with RANK
Rank values against a fixed list, understand how tied values are ranked, and compare first and second place.
Split Email Addresses into Username and Domain
Extract part of a text by finding a separator, instead of counting characters by hand.
Format Numbers and Dates as Text with TEXT
Convert numbers, percentages and dates to formatted text with TEXT, and join them with other text using &.
Clean Imported Amounts with TRIM, SUBSTITUTE and VALUE
Combine TRIM, SUBSTITUTE and VALUE to turn imported text into numbers that can be summed.
Find an Employee by ID with XLOOKUP
Retrieve values that sit to the left of the lookup column, using XLOOKUP, or INDEX with MATCH, when VLOOKUP cannot.
Handle Missing Prices with IFERROR
Replace an Excel error with a useful result using IFERROR, and understand what IFERROR hides.
Flag Bad Data with ISBLANK and ISNUMBER
Use ISBLANK and ISNUMBER inside nested IF functions to classify data, and count the rows that do not pass.
Employee Tenure in Years and Months with DATEDIF
Measure the time between two dates in complete years and months using DATEDIF.
Subscription Renewal Dates with EDATE and EOMONTH
Move a date forward by a number of calendar months with EDATE, and find the last day of a month with EOMONTH.
Build a Region by Product Sales Summary with SUMIFS
Total values that meet two conditions, and lock the right parts of each reference so a single formula can be copied across a whole table.
Profit and Margin by Product
Turn unit figures into profit and margin, and understand why an overall margin is not the average of the product margins.
Related topic guides
- Excel IF Function Practice
- Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
- Excel SUMIF, COUNTIF and SUMIFS Practice
- Excel Text Functions Practice: TRIM, LEFT, TEXT and More
- Excel Data Analysis Practice: RANK, MEDIAN, SUMPRODUCT and More
- Next: Advanced: Lookups, Finance and Analysis
- All learning paths
- Browse all exercises