HR KPIs and a Monthly HR Dashboard in Excel (Free Template)
HR KPIs and a Monthly HR Dashboard in Excel (Free Template)
Most HR teams in small and medium companies report activity, not results. For example, they report “we hired 5 people this month”, but they cannot say how many of them left within 90 days, or what absence costs the company.
This guide explains the ten HR KPIs that matter most for a small or medium company, how to calculate each one, and how to review them every month. It also includes a free Excel dashboard that calculates everything from a few monthly inputs.
An HR KPI is a number that tells management whether the people side of the business is improving or getting worse. Therefore, a good dashboard is short, uses the same definitions every month and shows the trend, not just one month.
| KPIs in the template10 | Inputs per month14 | Review cycleMonthly | FileExcel |
The 10 HR KPIs and their formulas
| KPI | Formula | What it tells you |
|---|---|---|
| Average headcount | (start + end of month) ÷ 2 | The base for most other KPIs |
| Turnover rate | all leavers ÷ average headcount | How many people you are losing |
| Voluntary turnover rate | resignations ÷ average headcount | How many people choose to leave |
| Early turnover | new hires who left within 90 days ÷ new hires | The quality of hiring and onboarding |
| Absence rate | absence days ÷ (average headcount × working days) | Lost working time |
| Training hours per employee | training hours ÷ average headcount | Investment in skills |
| Overtime hours per employee | overtime hours ÷ average headcount | Workload and staffing gaps |
| Payroll cost per employee | payroll cost ÷ average headcount | The cost side of headcount |
| Average time to fill | total days to fill ÷ positions filled | Recruitment speed |
| Open positions | open positions at month end | The hiring backlog |
Keep in mind. A KPI is only useful when the definition stays the same. For instance, decide once whether a leaver counts in the month of resignation or in the month of their last working day, and then apply it every month.
How to build the monthly routine
1
Collect
In the first three working days of the month, collect headcount, movements, absence, training, overtime and payroll from the previous month.
2
Enter
Enter the 14 figures in the yellow cells of the “Monthly Data” sheet. The dashboard updates automatically.
3
Read the trend
Compare this month with the last three months, not with one month only. Then mark any KPI that moved sharply.
4
Act
Agree on one action per red flag, for example exit interviews for high voluntary turnover, and follow it up next month.
What the numbers look like
52.5
Average headcount
1.9%
Monthly turnover
3.5%
Absence rate
35 days
Time to fill
These are the sample figures in the template: a company that starts the month with 52 employees, hires 2 and loses 1. As a result, the turnover rate is 1.9% for the month. Replace them with your own data.
In practice: High early turnover usually points to the hiring and onboarding process, not to the people who left. For example, if three of every ten new hires leave within 90 days, review the job adverts, the interview questions and the first weeks on the job. Our 90-day onboarding plan is a good place to start.
FAQ about HR KPIs
How do you calculate employee turnover rate?
Divide the number of leavers in the period by the average headcount of the same period, then multiply by 100. The average headcount is usually (start + end) ÷ 2.
What is a good absence rate?
It depends on the industry and the type of work. Therefore, track your own trend month by month and investigate any sharp increase.
How many KPIs should an HR dashboard have?
For a small or medium company, eight to twelve KPIs are enough. More than that usually means nobody reads the dashboard.
Can I build an HR dashboard in Excel?
Yes. A simple input sheet and a calculation sheet with data bars, like the free template here, is enough for most small and medium companies.
Summary
In short, pick a few KPIs, define them once, enter the data every month and look at the trend. Then turn every red flag into one clear action. The Excel template does the calculations, so your time goes into the decisions.
Want an HR dashboard built for your company?
Careerians designs HR dashboards and the monthly reporting routine behind them, connected to your payroll and attendance data.
Read also:
Onboarding: The First 90 Days Plan for New Hires (Checklist)
How to Write an HR Policy Manual for Your Company (with Template)
How to Build a Fair and Motivating Salary Structure in Your Company
10 HR Steps Every New Company in Egypt Must Take
More explainers on our YouTube channel: Careerians on YouTube
The KPI formulas are standard HR analytics definitions. The sample figures in the template are illustrative; use your own company data.
Tag:HR analytics, HR dashboard, HR KPIs
