Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task

Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Excel VLOOKUP Function, Filter, Conditional formatting + Pivot Table Task
Grade Levels
Resource Type
Product Rating
File Type

Unknown Format

Be sure that you have an application to open this file type before downloading and/or purchasing.

21 KB|3 pages
Share
Product Description
Includes the Excel file to provide your students to learn these essential Excel skills.

Task includes:
1. Sorting the records by last name alphabetically in ascending order (A-Z).

2. Sorting the NUM column (A) in Ascending order from lowest to highest number.

3. Use VLookup to find the hourly rate for each of the staff employed by Eastern School District. (*use the hourly rate worksheet)

4. Calculate the Gross Pay of each employer based on their hourly rate of pay and hours worked. (= hrly rate * hrs worked = gross pay)

5. Calculate the Average, MIN/MAX for hourly rate & gross pay.

6. Filter: Filter Column E (School) to show only those teachers employed at Westminster school. Save this as westmin results.

7. Clear the filter results and return to the original payroll sheet.
Filter results in column H (Job Title) to show only substitute teachers. Save as sub_teachers.

8. Clear the filter results and return to the original payroll sheet.

Conditional Formatting:
9. Use conditional formatting to highlight all teachers who have worked greater than (> 25 hours)
Highlight these in red.

10. Use conditional formatting to highlight all teacher assistants. Highlight these in yellow.

11.The district manager would like to know which school had the greatest number of casual teachers employed during this pay period.

Use a PIVOT Chart/Graph to work out which school employed the most number of casual staff.

*Hint: Pivot Chart fields include NUM, SURNAME, SCHOOL, HRS WORKED.

** Please note: Pay rates may need to be updated to reflect local wage rates and currencies.



Total Pages
3 pages
Answer Key
N/A
Teaching Duration
N/A
Report this Resource
Loading...
$2.00
Digital Download
More products from Creative Teaching Ideas
Product Thumbnail
Product Thumbnail
Product Thumbnail
Product Thumbnail
Product Thumbnail
Teachers Pay Teachers

Teachers Pay Teachers is an online marketplace where teachers buy and sell original educational materials.

Learn More

Keep in Touch!

Sign Up