Excel for HR: Headcount, Tenure, Leave and Salary Formulas
Excel formulas for HR teams: headcount by department, tenure with DATEDIF, leave balances, salary bands with lookups, turnover rate and clean employee lists.
By Ayoub Farid · · 1 min read
HR teams handle employee lists, leave, salaries and reports, often exported from an HRIS and cleaned in Excel. These formulas cover the common tasks.
Headcount
- Total active:
=COUNTIF(F2:F500,"Active") - By department:
=COUNTIFS(C2:C500,H2,F2:F500,"Active"). See COUNTIFS. - New hires this year:
=COUNTIFS(D2:D500,">="&DATE(YEAR(TODAY()),1,1))
Tenure
=DATEDIF(D2,TODAY(),"y")&" y "&DATEDIF(D2,TODAY(),"ym")&" m"Tenure bands for reports with IFS. See DATEDIF.
Age
=DATEDIF(E2,TODAY(),"y")See calculate age. Handle personal data carefully and only where it's needed.
Leave balance
=Entitlement-SUMIFS(Leave!$D:$D,Leave!$A:$A,A2,Leave!$C:$C,"Annual")Salary bands
Band table sorted by minimum salary, then:
=XLOOKUP(G2,$M$2:$M$8,$N$2:$N$8,,-1)See XLOOKUP.
Turnover rate
=Leavers/AVERAGE(StartHeadcount,EndHeadcount)Format as %.
Clean employee lists
Names in caps with extra spaces: =PROPER(TRIM(A2)). Duplicate IDs: =COUNTIF($A$2:$A$500,A2)>1. See TRIM and remove duplicates.
Frequently asked questions
What Excel skills do HR professionals need?
COUNTIFS, SUMIFS, DATEDIF, lookups, data cleaning, pivot tables and basic charts.
How do I calculate employee tenure in Excel?
=DATEDIF(start_date,TODAY(),"y") for years, plus "ym" for months.
How do I calculate turnover rate in Excel?
Divide leavers by average headcount for the period.
How do I count employees by department?
=COUNTIF(department_range,"Sales") or COUNTIFS with a status condition.