Excel Skills for HR and Recruiting Jobs
By ExcelReady TeamUpdated 5 min read
The short answer
The most useful Excel skills for HR and recruiting are counting headcount with COUNTIFS, totalling payroll with SUMIFS, calculating tenure and time to hire with DATEDIF, TODAY and date subtraction, looking up employee records with XLOOKUP or VLOOKUP, flagging eligibility with IF and AND, and cleaning names and dates so reports stay accurate.
Key takeaways
- Most HR questions are counts and totals by group: headcount by department, payroll by team, hires by month.
- DATEDIF(start, end, "Y") gives full years of service; with TODAY() it stays correct every day.
- IF with AND turns an eligibility rule, like rating and hire date, into a Yes/No column you can fill down.
- Employee data is sensitive, so share summaries rather than full lists when that's all someone needs.
On this page+
The Excel skills for HR and recruiting work are mostly about people data: counting headcount with COUNTIFS, totalling payroll with SUMIFS, working out tenure with DATEDIF, looking up records and flagging who meets a rule. This guide is for anyone applying to HR assistant, HR coordinator, people operations or recruiting roles who wants to practise the tasks those teams actually do in a spreadsheet.
What HR and recruiting teams use Excel for#
HR teams often keep or export employee and candidate data into Excel to answer questions and build reports. Common files include:
- Employee rosters: name, ID, department, hire date, salary, manager.
- Recruiting trackers: candidates, stage, source, application and offer dates.
- Leave and absence logs, training records and review schedules.
- Monthly reports: headcount, new hires, leavers and payroll by department.
The examples below use a small roster with names in column A, departments in B, hire dates in C, salaries in D and ratings in E, rows 2 to 7.
| Name | Dept | Hire Date | Salary | Rating |
|---|---|---|---|---|
| Rachel Adams | Sales | 12 Mar 2018 | 72000 | 4 |
| Ben Carter | Finance | 1 Jul 2020 | 85000 | 3 |
| Chloe Davis | Sales | 20 Feb 2023 | 61000 | 5 |
| Daniel Evans | IT | 7 Nov 2016 | 98000 | 4 |
| Fatima Hassan | HR | 17 May 2021 | 64000 | 3 |
| George Ito | Sales | 23 Sep 2019 | 69500 | 3 |
The core Excel skills for HR: headcount with COUNTIFS#
COUNTIFS counts the rows that meet every condition you give it, in range and criteria pairs.
=COUNTIFS(B2:B7,"Sales")returns 3, the Sales headcount. Add a second pair to narrow it:
=COUNTIFS(B2:B7,"Sales",E2:E7,">=4")returns 2: Rachel and Chloe are in Sales with a rating of 4 or more. Put the department in a cell, say H1, and use
=COUNTIFS(B2:B7,H1), so one formula works for any department. To count hires in 2023, use the date column twice:
=COUNTIFS(C2:C7,">="&DATE(2023,1,1),C2:C7,"<"&DATE(2024,1,1)) returns 1. See the
COUNTIFS function guide and the
guide to COUNTIFS and SUMIFS with multiple conditions.
Recruiting trackers work the same way. If candidate names are in column A and their stage (Applied, Interview, Offer,
Hired) is in column C, =COUNTIFS(C2:C50,"Interview") counts the people at interview stage. A simple conversion rate
is a count divided by a count: =COUNTIFS(C2:C50,"Hired")/COUNTA(A2:A50) gives the share of all candidates who were
hired. Format it with Ctrl+Shift+% (Mac: Control+Shift+%) rather than multiplying by 100. COUNTA counts names, so
blank rows at the bottom of the range don't lower the rate.
Payroll totals with SUMIFS#
SUMIFS adds numbers that meet conditions, and the range to add comes first:
=SUMIFS(D2:D7,B2:B7,"Sales")returns 202500, the Sales payroll (72000 + 61000 + 69500). For an average salary by department, AVERAGEIFS uses
the same order: =AVERAGEIFS(D2:D7,B2:B7,"Sales") returns 67500.
Tenure and time to hire with dates#
Excel stores dates as numbers, so you can subtract them and measure gaps.
- Days between two dates:
=D2-C2gives the number of days, later date first. - Full years of service:
=DATEDIF(C2,TODAY(),"Y")counts complete years from the hire date to today. It updates every day, so the report stays correct. - Years and months:
"Y"gives full years and"YM"the months left over, so you can show "7 yrs, 11 mos". - Working days to hire:
=NETWORKDAYS(C2,D2)counts Monday-to-Friday days between an application date and an offer date, counting both ends.
DATEDIF takes the start date first, then the end date. If the start is later, it returns #NUM!. Excel doesn't suggest DATEDIF as you type, but it works. See the DATEDIF function guide and how to count days between two dates.
Eligibility flags with IF and AND#
HR rules often have two parts, such as "rating of 4 or more and hired before 1 January 2024". IF with AND turns that into a Yes/No column:
=IF(AND(E2>=4,C2<DATE(2024,1,1)),"Yes","No")In F2 this returns Yes for Rachel. Fill it down to F7 with Ctrl+D (Mac: ⌘D). Daniel and Chloe are also Yes; Ben, Fatima and George are No because their rating is 3. Use OR when either condition is enough.
Looking up and cleaning employee records#
Lookups pull one person's details by ID or name. With IDs in A and salaries in E of a larger roster:
=XLOOKUP(H1,A2:A13,E2:E13,"Not found")shows "Not found" instead of #N/A when someone types an ID that doesn't exist. XLOOKUP needs Excel 2021 or Microsoft
365; =IFERROR(VLOOKUP(H1,A2:E13,5,FALSE),"Not found") does the same in any version.
Names copied from forms and job boards are often messy. =PROPER(TRIM(A2)) removes extra spaces and fixes the case, so
" rachel ADAMS" becomes "Rachel Adams". Clean names before you look them up, or lookups will miss.
People data is sensitive. When a manager only needs headcount or totals, send a summary rather than the full roster.
How to prepare for an HR Excel test#
HR Excel tests commonly use a roster like the one above and ask a manager's questions without naming functions. Practise until you can do each of these in under two minutes: a lookup by ID, a headcount, a payroll total, a count with two conditions, a tenure column and an eligibility flag. The guide to what Excel assessments test and how they're scored explains the common formats. To show these skills to employers, see how to show Excel skills on your resume, and compare the overlap with Excel skills for sales and operations.
Practise it#
Count and total with several conditions in COUNTIFS and SUMIFS, measure tenure in days between dates: DAYS, DATEDIF and TODAY, then put it all together in the HR roster interview drill. The exercise below is a tenure calculation from that lesson's data.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
HR needs each person's full years of service on the date of their last review. Click D2 and use DATEDIF from the hire date in B2 to the review date in C2, counting complete years.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Full Years | ||||
| 2 | |||||
| 3 | |||||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 |
Tip: DATEDIF takes the start date first, then the end date, then a unit in quotes.
Practise this in a lesson
Frequently asked questions
What Excel skills does an HR assistant need?
Accurate data entry, sorting and filtering, COUNTIF and COUNTIFS for headcount, SUMIFS for payroll totals, date calculations for tenure, and a lookup like VLOOKUP or XLOOKUP. Pivot tables are a useful next step.
How do I calculate years of service in Excel?
Use =DATEDIF(hire_date,TODAY(),"Y") for full years up to today. Swap TODAY() for a fixed date to measure tenure on a specific day, such as the end of the year.
How do I count employees by department in Excel?
Use =COUNTIF(dept_range,"Sales") for one condition, or COUNTIFS for several, such as =COUNTIFS(dept_range,"Sales",rating_range,">=4"). Point to a cell with the department name to make one formula reusable.
Why doesn't DATEDIF show up when I type it?
Excel supports DATEDIF for compatibility but doesn't suggest it as you type. Type the whole formula yourself and it works.