Includes the Excel file to provide your students to learn these essential Excel skills.
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.
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.