Companies have HRIS, payroll software, ATS and reporting tools. And yet, in almost every HR department, there is still an Excel file open at all times: recruitment tracking, preparing pay rises, payroll checks, the headcount export for the executive committee. Using it efficiently saves hours every month, and it is a skill recruiters test more and more. This article explains why HR still lives in Excel, presents the basic functions to master, shows how to use pivot tables for HR reporting, walks through a practical case on headcount and turnover, and helps you show your level on your CV.
Why HR still lives in Excel
Tools do not always talk to each other
The HRIS holds employee files, the payroll software holds pay, the ATS holds applications, the time-tracking software holds absences. To answer a simple question (“how many departures within six months of hiring, by site?”), you often need to combine exports from several tools. Excel remains the fastest tool for doing so.
Ad hoc requests
A director wants a pay rise simulation, a manager a leave report for their team, the CSE works council an indicator for its next meeting. These requests arrive without warning and do not justify a development in the HRIS. A well-built file lets you answer them in an hour.
Checks
In payroll, comparing two months, checking contributions or spotting an unusual payslip very often goes through a table. The spreadsheet is the checking tool par excellence.
An expected skill
Almost all HR job ads ask for “proficiency in Excel”. Behind this phrase, recruiters expect a few specific functions, which they can check at interview or with a short test.
The basic functions to master
Looking up information: XLOOKUP
XLOOKUP 🔗 (RECHERCHEX in French Excel) finds a value in a table from a key: an employee’s site from their ID number, their base salary from a payroll export. It is a clear improvement on the older VLOOKUP, because it searches any column and handles missing values cleanly. If your company uses an older version of Excel, also know VLOOKUP and the INDEX and MATCH combination.
Counting and adding by criteria
- COUNTIFS (NB.SI.ENS) counts the rows that meet several criteria: the number of permanent executive contracts at a site.
- SUMIFS (SOMME.SI.ENS) adds by criteria: the payroll cost of a department.
- AVERAGEIFS (MOYENNE.SI.ENS) calculates a conditional average: average length of service by category.
These three functions cover a large part of day-to-day reporting.
Working with dates
Length of service, age, contract duration, the end date of a probationary period: HR handles dates constantly. DATEDIF calculates a gap in years, months or days; EOMONTH (FIN.MOIS) returns the last day of a month; NETWORKDAYS (NB.JOURS.OUVRES) counts working days between two dates, useful for certain deadlines.
Cleaning exports
Software exports often contain extra spaces, ID numbers stored as text or names in capitals. TRIM, VALUE, PROPER and the text-to-columns tool save valuable time before any analysis.
Spotting anomalies: conditional formatting
Conditional formatting automatically colours cells according to a rule: a salary below the minimum of the pay scale, a probationary period ending within fifteen days, a duplicate ID number. It is the simplest tool for making a check safe.
Pivot tables for HR reporting
What they are for
A pivot table 🔗 summarises a database in a few clicks: headcount by site and contract, starters and leavers by month, training hours by department. It lets you change angle without rewriting formulas.
Preparing a clean base
A good pivot table starts with a well-built base: one row per employee (or per event), one column per item of information, clear headers, no merged cells, dates in the right format. Format your base as a table: new rows will be picked up automatically.
The most common analyses
- headcount on the last day of the month, by site, contract and category;
- breakdown by sex and age band, useful for gender equality obligations;
- starters and leavers by month and by reason;
- average length of service by department;
- completion of the training plan by division.
Slicers and charts
Slicers let you filter a pivot table with one click (by site, by year); pivot charts turn the result into a visual. For an executive committee, a simple chart is often better than a large table.
Practical case: headcount and turnover
The situation
You have an export from your HRIS: one row per employee, with the ID number, site, type of contract, start date and, where relevant, leaving date. You are asked for the headcount on 31 December and the year’s turnover, by site.
Step 1: prepare the base
Check that dates are recognised as dates, remove duplicate ID numbers and add two columns: “present on 31/12” (yes if the start date is on or before 31 December and the leaving date is empty or later) and “left during the year” (yes if the leaving date falls within the year).
Step 2: calculate headcount
A pivot table with the site in rows and the count of “present on 31/12 = yes” as values gives you headcount by site. With COUNTIFS, you get the same result in a single cell.
Step 3: calculate turnover
There are several definitions. Dares, the French Ministry of Labour’s statistics department, uses in its publications on labour movements a turnover rate equal to the average of the entry rate and the exit rate, relative to headcount; you can find it, for example, in its study “For ten years, turnover has been in step with economic activity” 🔗. In practice: add the year’s starters and leavers, divide by two, then divide by the average headcount for the period. With fictional figures, a site that recorded twelve starters and ten leavers for an average headcount of one hundred and twenty people obtains (12 + 10) / 2 / 120, a little over nine per cent.
Step 4: present it
Always write the definition used under the figure: some companies only count voluntary departures, others exclude the end of fixed-term contracts. Two figures calculated differently cannot be compared. The same reasoning applies to management controllers, whose Excel functions expected in finance 🔗 partly overlap with yours, especially for tracking payroll costs.
Showing your Excel level on your CV
Replace “proficient in Excel”
“Proficient in Excel” or “Excel: advanced” say nothing. Name two or three specific functions or uses instead:
Excel: pivot tables, XLOOKUP, COUNTIFS, conditional formatting.
Prove it in an experience line
Better still, show a concrete use:
Built a monthly dashboard of headcount, starters and leavers for three sites from HRIS exports (pivot tables), presented to the executive committee.
Stay honest
If you do not know macros or Power Query, do not list them. Recruiters sometimes set a short exercise at interview; it is better to claim a solid level on the essentials than an expert level you could not demonstrate.
Going further
If reporting interests you, people analytics and HRIS roles naturally extend these skills: our article on the people analytics CV and the one on the HRIS CV describe what is expected, and the people analytics analyst CV example shows how to present them. To format your own CV, create it for free: the editor handles the presentation, and the PDF stays readable by screening software, which recognises “pivot tables” written out in full better than a drawn skill gauge.
Sources
- Microsoft, XLOOKUP function 🔗 and creating a pivot table 🔗.
- Dares, For ten years, turnover has been in step with economic activity 🔗: definition of the labour turnover rate.
Frequently asked questions
Do you need to know macros for an HR role?
Rarely. Macros and VBA are useful in some reporting or very high-volume payroll roles, but most HR roles mainly require lookup functions, conditional functions and pivot tables.
Excel or Google Sheets?
The essential functions exist in both, sometimes with different names. State the tool you use, and mention that you also work with the other one if that is the case.
How can you progress quickly?
Start from a real need in your role (recruitment tracking, a payroll check) and build the file yourself. The vendor’s tutorials cover each function; the key is to practise on real data, while respecting its confidentiality.
Do recruiters run Excel tests?
Some do, especially for payroll, personnel administration or reporting roles. The test usually covers a lookup, a conditional calculation and a pivot table.