Level 2 · Check and summarise headcount and pay in Excel

What the sheet holds

HR’s export has one row per employee, with names removed:

column	holds
A	employee_id
B, C	department and title
D	manager_id: the employee_id of their manager, empty for the CEO
E, F	hire date and yearly salary

Removing names is a good habit for any people data that leaves HR. It does not make the sheet anonymous, though: the only Executive earning 285,000 is obviously the CEO.

Step 1: do the reporting lines hold together?

Every manager_id should be another row’s employee_id. XLOOKUP finds it and returns that person’s title; its fourth argument decides what to show when nothing matches:

G2   =IF(D2="","no manager",XLOOKUP(D2,$A$2:$A$61,$C$2:$C$61,"missing"))

The IF in front handles the CEO, whose manager cell is empty. Then two counts and a range check:

J2   =COUNTIF(G2:G61,"no manager")
J3   =COUNTIF(G2:G61,"missing")
J4   =MIN(F2:F61)
J5   =MAX(F2:F61)

One “no manager” and no “missing” means the org chart is complete. A monthly salary typed into a yearly column would show up in J4 as a number near 4,000.

Step 2: headcount and payroll by department

COUNTIF counts the rows whose department matches the label; SUMIF adds their salaries:

J8   =COUNTIF($B$2:$B$61,I8)
K8   =SUMIF($B$2:$B$61,I8,$F$2:$F$61)
L8   =K8/J8

Fill them down to row 16 and total in row 17. If the totals do not come to 60 people and the sum of column F, a department label is misspelled or missing.

Step 3: span of control, mean and median

Span of control is the number of people who report directly to someone. Turn the question around: an employee’s direct reports are the rows whose manager_id is their id.

H2   =COUNTIF($D$2:$D$61,A2)

Most rows show 0. The twelve that do not are the managers, and MAX gives the widest span, 7.

Then the pay summary. The mean of all 60 salaries is 99,317, the median 81,000. The gap comes from a few senior salaries: the CEO, the COO and the department heads. For “what does a typical person earn”, the median is the honest number.

Pitfalls

• XLOOKUP without a fallback shows #N/A, which no COUNTIF of a word will catch.
• Unlocked ranges slide when you fill down; lock every data range with $.
• The average of a two-person group gives away both salaries to anyone who knows one. Keep small groups out of any report that goes beyond Finance and HR.