Excel Functions: practice exercises
A large set of exercises on the functions people use every day: SUM, AVERAGE, COUNTIF, SUMIF, IF with AND and OR, text functions such as LEFT, MID and CONCAT, and date and time functions. The sample data looks like real work, with orders, staff lists and schedules, so you see why each function exists. Solve a task in the live sheet, get an instant check, and open a hint only when you are stuck. A good place to build real formula fluency.
85 exercises · level: Confident User
Start with the first task Study the course: Excel Functions →
Showing 85 of 85
Math and Counting
In D7, calculate the total amount of the purchase.
In C7, calculate how many items were bought in total.
In E2, find the class average score.
In E3, find the lowest score.
In E4, find the highest score.
In E2, count how many employees have a number in the sales column (B).
In E3, count how many employees are in the list.
In E4, count how many cells in B2:B9 are empty.
In D2:D6, round the discounted price to whole dollars.
In E2:E6, round the discounted price up to the next $10.
In F2:F6, round to the nearest $5, which is handy for price tags.
In C2:C6, calculate how many full boxes you get (the box size is in H1).
In D2:D6, calculate how many units are left outside the full boxes.
In E2:E6, calculate how many boxes are needed in total (a partly filled box counts too).
In C2:C5, calculate how many liters of paint are needed (area × the rate in H1), rounded to one decimal place.
In D2:D5, calculate how many cans to buy (the can size is in H2). A part can still means a whole can.
In E2:E5, calculate the painter's pay (area × the rate in H4), rounded to the nearest $5.
In H6, calculate how many whole cans the budget in H5 buys (round down).
The painter worked H7 minutes. In H8, calculate the full hours, and in H9 the minutes left over.
Logic
In C2:C8, write "Pass" if the score is at least the passing score (F1), otherwise "Fail".
In C2:C8, assign a grade by the scale using nested IFs.
In D2:D8, do the same with the IFS function.
In E2:E7, pay a bonus of 500 if sales are at least the target AND tenure is at least 1 year, otherwise 0.
In F2:F7, write "Yes" if sales are less than half the target OR tenure is under a year. Otherwise leave it empty ("").
In D2:D5, check whether the codes in A and B match, including case.
In E2:E5, work out the region: Seattle and Denver are "West", Chicago is "Central", and everything else is "East".
In F2:F5, look up the city's warehouse in the lookup table G2:H3, and write "none" for cities without a warehouse.
In F2:F7, enter the shipping cost: 0 if the order total is $200 or more, otherwise 15.
In G2:G7, assign a status with IFS: $500 or more is "Gold", $200 or more is "Silver", otherwise "Bronze".
In H2:H7, write "Yes" if the order is from Austin AND weighs 5 kg or less, otherwise "No".
In I2:I7, write "Review" if the total is over $1,000 OR the weight is over 30 kg, otherwise "OK".
In J2:J7, calculate the average item price (total / items). A pre-order has 0 items, and it should show 0, not an error.
Conditional Calculations
In E2:E4, count how many deals each manager has.
In F2:F4, calculate each manager's revenue.
In E6, count how many deals are worth more than $800.
In F2, calculate the total of Emma's deals in Boston.
In F3, count how many deals in Chicago are worth more than $1,000.
In F4, find the average deal amount in Denver.
In F5, find Daniel's largest deal.
In E3, count how many items cost more than the threshold in E1. Refer to the threshold by its cell.
In E4, add up the prices of all items that start with "Tea".
In E5, count how many items contain the word "coffee".
Change the threshold in E1 to 25. The answer in E3 should recalculate by itself.
In H2, count how many trips Sam made.
In H3, calculate the revenue for the "Economy" fare class.
In H4, count how many "Economy" trips Ian made.
In H5, find the average fare for all classes that start with "Comfort".
In H6, calculate Max's revenue only for trips longer than the limit in J1. Refer to the limit by its cell.
In H7, find the longest trip (in km) in the Downtown district.
Text
In C2:C5, build "First Last" with a space between the names.
In D2:D5, write the last name in capital letters.
In B2:B5, extract the country code: the first 2 characters of the SKU.
In C2:C5, extract the city code: 3 characters, starting from the 4th.
In D2:D5, extract the number: the last 5 characters.
In G2:G5, pull out the username: everything before the "@". The usernames have different lengths, so you need FIND.
In D2:D5, tidy up the names: no extra spaces, and each word capitalized.
In E2:E5, keep only the digits of the phone number: remove the spaces, parentheses and hyphens.
In F2, join all the cities with a comma and a space, skipping the empty ones.
In D2:D5, build a line like "Rent: $2500.00" (the amount with a dollar sign and two decimals).
In E2:E5, show the day of the week as a word.
The numbers in G2:G5 are stored as text (see how they are pushed to the left?). In H2:H5, turn them into real numbers.
In D2:D6, make the city code: the first 3 letters, in capitals.
In E2:E6, write the phone number without hyphens, as one string.
In F2:F6, extract the first name: everything before the first space.
In G2:G6, build the badge with CONCAT: the first name in capitals, " from ", then the city. For example: "DANIEL from Boston".
In H2:H6, write the name in short form: the first name followed by the middle and last initials, for example "Daniel J.B.".
Dates and Time
In D2:D5, calculate how many days each order was in transit.
In E2:E5, show the month number the order was placed, and in F2:F5 the year.
In G2, build the date December 31, 2026 with the DATE function.
In H2, calculate how many days there are from the date the first order was placed (B2) to the year end (G2).
In C2:C5, calculate the complete years of service as of the report date in G1.
In D2:D5, find the date the probation period ends: 3 months after the hire date.
In E2:E5, show which day of the week each person started (1 = Monday … 7 = Sunday).
In G2, find the last day of the month of the report date: payday.
In D2:D4, calculate the finish date of each stage, skipping weekends and the holidays in G2:G5.
In H6, calculate the number of workdays in December 2026 (from H2 to H4, taking the holidays into account).
In D2:D5, calculate the time worked (clock out − clock in).
In E2:E5, convert it to hours as a number.
In F2:F5, calculate the pay: hours × the rate in I1.
In D2:D6, calculate the date the book is due back: the checkout date plus the loan period in days.
In F2:F6, calculate the days late: if the book was returned after the due date, the difference between the dates, otherwise 0.
In G2:G6, show the day of the week of the checkout as a number (1 = Monday … 7 = Sunday).
In H2:H6, calculate how many workdays (no Saturdays or Sundays) fall between checkout and the due date.
In J3, calculate how many hours per day the library is open (the times are in J1 and J2), as a number.
In J4, find the last day of the month in which the last book (B6) was checked out. That is the report date.
No exercises match these filters.