HR analytics with AI: headcount, turnover rate and salary analysis from one employee list
Headcount by department, annual turnover, why one team keeps losing people, and pay within each level. A worked example in GPTExcel, with the prompts and a one-page summary for leadership.
Most HR questions from leadership, such as how many people we have, who's leaving and whether pay is fair, can be answered from one employee list. You don't need an HR analytics platform. You need the list, the right calculations, and someone to point out what stands out. This post works through all of it in GPTExcel.
The data
employees_2026.xlsx: 224 people who worked at the company at some point between 1 January and 30 September
2026, one row each, with department, level (L1 to L4), location, hire date, exit date (blank if they're still
here) and salary. The names are made up.
Including the people who left matters: without their rows you can't measure turnover.
1. Headcount by department
What's our headcount by department, and how has it changed this year?
| Department | 1 Jan | Hired | Left | 30 Sep | Change |
|---|---|---|---|---|---|
| Engineering | 52 | 9 | 4 | 57 | +5 |
| Sales | 38 | 16 | 14 | 40 | +2 |
| Operations | 34 | 4 | 3 | 35 | +1 |
| Customer Support | 30 | 7 | 6 | 31 | +1 |
| Marketing | 18 | 3 | 2 | 19 | +1 |
| Finance | 12 | 1 | 1 | 12 | 0 |
| Total | 184 | 40 | 30 | 194 | +10 |
The company grew from 184 to 194 people. Sales stands out: it hired 16 people but grew by only 2, because 14 left.
2. Turnover rate by department
What's our turnover rate by department?
Turnover is leavers divided by average headcount. For nine months of data, scale it to twelve so it compares with the annual figures people quote:
| Department | Annual turnover |
|---|---|
| Sales | 47.9% |
| Customer Support | 26.2% |
| Marketing | 14.4% |
| Operations | 11.6% |
| Finance | 11.1% |
| Engineering | 9.8% |
Company-wide, turnover is 21.2% a year. Sales is more than twice that, and Customer Support is next.
3. Why are people leaving Sales?
Why are people leaving Sales?
The data can't tell you why someone resigned, but it can show who leaves and when. Two patterns stand out:
- They leave early. 11 of the 14 Sales leavers left in their first year. The median Sales leaver stayed 3.8 months, against 32.6 months for leavers elsewhere.
- Entry-level pay is lower. Sales L1s earn a median of $44,100, against $55,200 for L1s in other departments: 20% less for the same level.
New joiners on the lowest pay leaving within months is a clear pattern. Exit interviews and the Sales L1 pay band are the places to look next.
4. Are salaries consistent within each level?
Are salaries consistent within each level?
For current employees:
| Level | People | Lowest | Median | Highest |
|---|---|---|---|---|
| L1 | 80 | $41,400 | $51,200 | $60,700 |
| L2 | 73 | $52,500 | $63,800 | $77,400 |
| L3 | 25 | $65,800 | $84,100 | $98,500 |
| L4 | 16 | $81,900 | $105,100 | $125,100 |
Mostly, yes, with 8 exceptions: people paid more than 15% below their level's median. Four of them are Sales L1s, the same gap behind the turnover. The other four are three people in Operations (one each at L1, L3 and L4) and one in Customer Support. Each is worth a review before the next pay round.
The 15% threshold is a choice, not a rule. Ask for 10% or 20% and GPTExcel reruns it.
5. A summary for leadership
Summarize this for the leadership meeting
The answer is a short people update: headcount up from 184 to 194, but Sales losing people almost as fast as it hires them, with the evidence in four bullets and three proposed actions:
- Review the Sales L1 pay band before Q4 hiring.
- Exit interviews for every leaver in their first year.
- A pay review for the 8 people well below their level's median, in the next pay round.
A note on employee data
An employee list is personal data. Before you upload it anywhere, check your company's policy, and remove columns the analysis doesn't need (names, for example, if you only need counts and pay). GPTExcel doesn't train on your files; see the privacy policy for how files are stored and deleted.
Try it with your own list
Export your HRIS or payroll list with a row for everyone employed during the period, including leavers, and ask "What's our headcount by department, and how has it changed this year?". More in the AI Chat guide, and the same approach for spending in Budget vs actual analysis.