Excel Skills Every HR Manager Needs

Core HR formulas

Calculate tenure in years

=DATEDIF(start_date, TODAY(), "Y")
-- Returns the number of complete years since the start date
-- E.g. =DATEDIF(B2, TODAY(), "Y") for employee in B2

Headcount by department

=COUNTIF(department_column, "Sales")
-- Count employees in a specific department

=COUNTIFS(department_column, "Sales", status_column, "Active")
-- Count active employees in Sales

Average salary by grade

=AVERAGEIF(grade_column, "Grade 3", salary_column)
-- Average salary for employees at Grade 3

Days of annual leave remaining

=annual_entitlement - SUMIF(employee_id_column, this_employee_id, days_taken_column)
-- Total entitlement minus days already taken

Useful HR date formulas

NETWORKDAYS — count working days between two dates (excludes weekends)
WORKDAY — calculate a date N working days from a start date
EDATE — add or subtract months from a date (useful for probation end dates)
EOMONTH — last day of a month (useful for monthly headcount snapshots)

Organising HR data

Store all employee data in a single Excel Table with one row per employee. Use VLOOKUP or XLOOKUP to pull employee data into separate calculation sheets. Use pivot tables for headcount reports by department, grade, or location.

💡 Protect sensitive columns

For salary data in shared files: Review → Protect Sheet → uncheck the specific columns with sensitive data. Only people with the password can edit those cells.

Practice what you just learned

ExcelPro has 880+ hands-on exercises across 9 tracks — type real formulas, get instant feedback.

Start practising free →

Excel skills everyHR manager needs