Excel for HR Assistants: Headcount, Hiring, Leave & Payroll Prep
20 hands-on labs for HR assistants and coordinators at a 60-person company: employee list cleanup and XLOOKUP, headcount, tenure, birthdays and anniversaries, time-to-hire, cost per hire, leave balances, overtime for payroll, compa-ratio, turnover, training hours and an HR dashboard, then names and email addresses, dynamic lists, two-key pay scale lookups, probation and contract end dates, headcount on any date, salary changes with back pay, input rules and a monthly HR report.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This beginner course follows the HR assistant of a fictional 60-person wholesaler through 20 labs. You clean the employee list and look up departments, count headcount and FTE, calculate tenure, list birthdays and anniversaries, track time-to-hire and cost per hire, work out leave balances, prepare overtime for payroll, check compa-ratios, turnover and training hours and build an HR dashboard. The second half adds everyday tools: splitting names and building email addresses, FILTER, SORT and UNIQUE lists, pay-scale lookups by grade and step, probation and contract end dates with EDATE, headcount on any date from start and leaving dates, salary changes with back pay, input rules with data validation and a monthly HR report with a consistency check and a combo chart. Every cell is checked instantly; all people and figures are fictional.
Lessons
- Employee List: Clean Names and Look Up Department and ManagerFree
- Headcount by Department with COUNTIF and COUNTIFS Locked
- Tenure from Start Dates: DATEDIF and YEARFRAC Locked
- Birthdays and Work Anniversaries This Month Locked
- Hiring Pipeline: Time-to-Hire and Open Roles Locked
- Recruiting Cost per Hire by Source Locked
- Leave Balances: Accrual, Days Taken and Overdrawn Leave Locked
- Timesheets for Payroll: Regular Hours, Overtime and Week Pay Locked
- Salary Bands and Compa-Ratio Locked
- Employee Turnover Rate by Month and for the Half-Year Locked
- Training Hours Tracker: Hours per Department and per Head Locked
- HR Dashboard: Headcount Chart and Turnover Alerts Locked
- Names and Email Addresses: Splitting Full Names, Building Logins & a Salutation Line Locked
- Dynamic HR Lists: FILTER by Department, SORT by Start Date & UNIQUE Locations Locked
- Pay Scale Lookups with Two Keys: Grade and Step, Part-Time Salaries & Next Year’s Step Increases Locked
- Fixed-Term Contracts & Probation: End Dates with EDATE, Days Left & a 30-Day Reminder List Locked
- Headcount on Any Date: Active Employees, Joiners and Leavers per Month from Start and Leaving Dates Locked
- Salary Changes for Payroll: Percentage Raises, Effective Dates & Back Pay Locked
- Input Rules for the Employee List: Drop-Down Lists, Allowed Hours & FTE, and Counting Old Errors Locked
- Final Project: The Monthly HR Report – Headcount Movements, a Consistency Check, Turnover & a Combo Chart Locked
Learner reviews
Be the first to review this course.