HR Excel Interview Questions With Worked Answers

HR Excel interview questions tend to come back to one kind of sheet: a list of employees with IDs, departments, start dates, status and pay. The questions ask you to count people, find one person, measure service, work out dates and summarise salaries. This guide gives ten realistic questions with worked answers on one small table, so you can practise the formulas and the way you explain them.

What HR Excel interview questions test#

HR questions check whether you can answer everyday people-data requests accurately. The formats vary by employer: some ask you to describe a formula, some give you a laptop and a file, and some send an online test before the interview. Whatever the format, the skills overlap:

• Counting with conditions: headcount by department, status or location.
• Lookups: find an employee's details from an ID.
• Dates: tenure, probation ends, anniversaries.
• Pay: averages and totals by group, raises.
• Clean data: duplicates, extra spaces, inconsistent names.

If you're not sure where you stand, take the Excel level test first: it shows which of these areas need work. For the wider skill set behind HR roles, see Excel skills for HR jobs.

The sample employee table#

All answers use this table in A1:F8. The report date, 31 October 2026, is in H1.

Headcount questions#

1. "How many active employees are in Sales?"

=COUNTIFS(C2:C8,"Sales",E2:E8,"Active")

The answer is 2 (Aisha and Farid). Carmen is in Sales but has left, so the second condition removes her. Explain that COUNTIFS counts rows where every condition is true. The COUNTIFS function guide has the full syntax.

2. "Give me the headcount for every department."

Use a pivot table: Dept in Rows, Status in Columns, and Count of ID in Values. Or list the departments in J2:J4 and fill down =COUNTIFS($C$2:$C$8,J2,$E$2:$E$8,"Active"). Saying both options shows you know when each fits.

3. "What share of staff have left?"

=COUNTIF(E2:E8,"Left")/COUNTA(E2:E8)

That's 2 ÷ 7 = 29% of the people on this list. If the interviewer asks for a turnover rate, mention that it is usually leavers in a period divided by the average headcount in that period, not a share of a list. The guide to HR analytics in Excel works through it.

Lookup questions#

4. "Find the department of the employee whose ID is in H2."

=XLOOKUP(H2,A2:A8,C2:C8,"Not found")

If H2 holds E105, the answer is HR. The fourth argument shows "Not found" instead of #N/A for a missing ID. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, say you'd use =VLOOKUP(H2,A2:F8,3,FALSE) or INDEX MATCH. The XLOOKUP function guide covers the other arguments.

5. "The lookup returns #N/A, but I can see the ID. Why?"

Give the usual causes in order: an extra space in the ID (fix with TRIM), an ID stored as a number in one list and as text in the other, or a lookup range that doesn't cover every row. This troubleshooting question checks experience more than syntax.

Date and tenure questions#

6. "How many full years has each person worked here?"

=DATEDIF(D2,$H$1,"y")

Aisha gets 7. Dev started on 5 November 2018, so on 31 October 2026 he also has 7 completed years, not 8: his anniversary is a few days away. Point out that you used a report date in a cell instead of TODAY(), so the report doesn't change every day. The DATEDIF function guide lists the units.

7. "Show tenure as years and months."

=DATEDIF(D5,$H$1,"y")&" years, "&DATEDIF(D5,$H$1,"ym")&" months"

For Dev, the result is 7 years, 11 months. "ym" returns the months left over after the full years.

8. "When does Elena's six-month probation end?"

=EDATE(D6,6)

The answer is 22 November 2023. EDATE adds whole months and handles month lengths for you. Format the cell as a date.

Pay and data-cleaning questions#

9. "What is the average salary of active Sales staff?"

=AVERAGEIFS(F2:F8,C2:C8,"Sales",E2:E8,"Active")

The answer is (52,000 + 43,000) ÷ 2 = 47,500. The range to average comes first, then the condition pairs.

10. "Give Sales a 3% raise in a new column."

=IF(C2="Sales",F2*1.03,F2)

Aisha's new salary is 53,560. Better still, put 3% in a cell and point to it, so the rate can change without editing the formula. Interviewers like hearing that.

Data-cleaning follow-ups are common too: find duplicate IDs with =COUNTIF($A$2:$A$8,A2)>1, remove extra spaces with TRIM, and fix capitals with PROPER. For "what if" style questions, see Excel scenario interview questions.

How to answer well in the interview#

A correct formula is only half the answer. Use this routine for each question:

1. Repeat the question in your own words, including conditions such as "active only".
2. Name the function and why: "COUNTIFS, because there are two conditions."
3. Point to cells, not typed values, so the answer updates.
4. Check one result by hand, such as counting the Sales rows yourself.
5. Mention an edge case: missing IDs, leavers, or dates in the future.

For a broader set of general questions, read 25 Excel interview questions, and for harder ones, the advanced Excel interview questions. To rehearse under time pressure, try the intermediate Excel mock test.

Practise it#

The fastest way to get ready is to answer these questions in a real sheet. The HR roster interview drill gives you a timed-test style employee list with lookups, counts and tenure, and checks every answer instantly. Strengthen the basics with the COUNTIFS and SUMIFS lesson and the XLOOKUP lesson, then try the tenure exercise on this page.