Excel Functions Practice Worksheet 📊
This worksheet is designed to help you practice some of the most common
and useful functions in Microsoft Excel. Use the sample data tables provided
for each section to complete the tasks.
Part 1: Basic Arithmetic and Statistical Functions
Use the following dataset for questions 1-5.
Sales Data for Q3 | Salesperson | Region | July Sales | August Sales |
September Sales | | :--- | :--- | :--- | :--- | :--- | | Maria | North | $5,200 |
$4,800 | $5,500 | | John | South | $3,900 | $4,200 | $4,100 | | Priya | East |
$6,100 | $5,900 | $6,300 | | Chen | West | $4,500 | $4,700 | $4,600 | | Sofia |
North | $5,300 | $5,600 | $5,400 |
1. SUM: In a new column named "Total Sales," calculate the total sales
for each salesperson for the third quarter (July, August, and
September).
o Formula Hint: =SUM(range)
2. AVERAGE: In the next column, "Average Monthly Sales," calculate the
average monthly sales for each salesperson.
o Formula Hint: =AVERAGE(range)
3. MAX: Below the "Total Sales" column, find the highest total sales
amount achieved by any single salesperson.
o Formula Hint: =MAX(range)
4. MIN: Below the "Average Monthly Sales" column, find the lowest
average monthly sales.
o Formula Hint: =MIN(range)
5. COUNT: Find the total number of sales entries for July.
o Formula Hint: =COUNT(range)
Part 2: Logical and Lookup Functions
Use the following datasets for questions 6-8.
Student Scores | Student Name | Score | | :--- | :--- | | Alex | 88 | | Ben |
65 | | Chloe | 92 | | David | 75 | | Evelyn | 59 |
Grading Scale | Minimum Score | Grade | | :--- | :--- | | 90 | A | | 80 | B | |
70 | C | | 60 | D | | 0 | F |
6. IF: In a new column called "Pass/Fail," use the IF function to display
"Pass" if a student's score is 60 or higher, and "Fail" otherwise.
o Formula Hint: =IF(logical_test, "value_if_true", "value_if_false")
7. VLOOKUP: In another new column called "Letter Grade," use the
VLOOKUP function to assign a letter grade to each student based on
the "Grading Scale" table.
o Formula Hint: =VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup]). Remember to use TRUE for the
range lookup to find the closest match.
8. COUNTIF: Below the student data, count how many students received
a "B" grade.
o Formula Hint: =COUNTIF(range, "criteria")
Part 3: Text and Date Functions
Use the following dataset for questions 9-11.
Employee Information | First Name | Last Name | Employee ID | | :---
| :--- | :--- | | Michael | Scott | EMP-001 | | Dwight | Schrute | EMP-002 | | Pam |
Beesly | EMP-003 |
9. CONCATENATE (or CONCAT): Create a "Full Name" column by
combining the "First Name" and "Last Name" columns. Make sure to
include a space between the names.
o Formula Hint: =CONCATENATE(text1, " ", text2) or
=CONCAT(text1, " ", text2)
10. RIGHT: Create a "Numeric ID" column by extracting the 3-digit
number from the "Employee ID" column.
o Formula Hint: =RIGHT(text, num_chars)
11. TODAY: In any empty cell, display the current date.
o Formula Hint: =TODAY()
