An HR dashboard in Excel is a single sheet that shows the month's key people numbers (headcount, exits, attrition, hiring and time to fill), with a breakdown by department and a trend over the year, all calculated from an employee list that you update each month. You can build one in under an hour. This guide gives you a free template that already works, then explains how it is built, so you can adapt it or make your own with pivot tables and slicers.
Download the HR Dashboard Excel Template — free, no sign-up. Pick a month from the dropdown and every number and chart updates.
What an HR Dashboard Should Show
A useful monthly HR dashboard answers four questions for a business head: how many people do we have, how many did we lose, how many did we hire, and where are the problems? That translates into a small set of measures:
| Measure | What it answers |
|---|---|
| Closing headcount | How many people are on the books at month-end |
| Exits and monthly attrition | How many people left, relative to the size of the workforce |
| Voluntary share of exits | How much of the loss was people choosing to leave |
| Hires and average time to fill | How fast the business is replacing and growing capacity |
| Headcount, exits and gender mix by department | Where the numbers are concentrated |
| Attrition trend across the year | Whether things are getting better or worse |
Resist the urge to add more. A dashboard with six well-chosen numbers gets discussed; one with thirty gets filed. The HRBP KPIs and scorecard covers a wider set of measures if you need to choose different ones.
What's in the Template
| Sheet | What it does |
|---|---|
| Dashboard | Month selector, six headline numbers, a department table, a headcount-by-department chart and a 12-month attrition trend |
| Employees | One row per employee: ID, department, gender, location, join date, exit date, exit type |
| Hiring | One row per requisition: ID, department, date opened, date filled |
| Monthly | Opening headcount, closing headcount, exits and attrition for each month, which feeds the trend chart |
The sample data is illustrative. Delete it and paste in your own; the formulas cover up to 999 employees and 499 requisitions, and new rows are picked up automatically without refreshing anything. The template uses formulas rather than pivot tables for that reason: a pivot table needs refreshing every time the data changes, and a forgotten refresh is the most common cause of a wrong HR dashboard.
Formulas for Headcount and Attrition
Everything on the dashboard comes from two ideas: who was employed on a given date, and who left in a given period. With join dates in column E and exit dates in column F of the Employees sheet:
Headcount on a date counts people who had joined by that date and either have no exit date or left after it:
=COUNTIFS(E:E,"<="&D4,F:F,"")+COUNTIFS(E:E,"<="&D4,F:F,">"&D4)
where D4 holds the month-end date. Opening headcount is the same formula using the last day of the previous month.
Exits in the month count exit dates between the first and last day of the month:
=COUNTIFS(F:F,">="&C4,F:F,"<="&D4)
Monthly attrition is exits divided by the average of opening and closing headcount. You can check any month's figure with the attrition rate calculator, which also explains annualising.
A built-in check: opening headcount plus hires minus exits should equal closing headcount every month. If it does not, there is a data problem: usually a missing join date or an exit date entered before a join date. The template's sample data reconciles for all twelve months, so if yours does not after you paste it in, the issue is in the data, not the formulas.
Building It Yourself with Pivot Tables and Slicers
If you prefer pivot tables, or need to slice by several fields at once, the steps are:
- Convert the data to a table. Click inside the employee data and press Ctrl+T. A table grows automatically as you add rows, so pivots built on it pick up new data on refresh.
- Add helper columns. Add "Active at month-end" (1 or 0, using the headcount logic above for your reporting date) and "Exit month" (the exit date formatted as a month), so the pivot has fields to count and group by.
- Insert pivot tables. Insert → PivotTable from the table. Build one for headcount by department (Department in Rows, sum of "Active at month-end" in Values) and one for exits by month (Exit month in Rows, count of Emp ID in Values).
- Add charts. Select each pivot and insert a PivotChart: a bar chart for departments and a line chart for monthly exits or attrition.
- Add slicers. Select a pivot, then PivotTable Analyze → Insert Slicer, and choose Department, Location or Gender. Use Report Connections on each slicer to link it to all pivots so one click filters everything.
- Refresh every month. Data → Refresh All after pasting the new data. This step is easy to forget; put it in the monthly checklist.
Can it be automated? Partly. Power Query (Data → Get Data) can pull the monthly export from your HRMS or payroll system and clean it the same way every month, so the only manual step becomes Refresh All. Full automation without any refresh needs a reporting tool connected directly to the HR system.
Turning the Dashboard into Business Insight
A dashboard is only useful if someone acts on it. When presenting it to a business head, lead with what changed and why, not with the numbers in order. For example, using the template's illustrative data: attrition ran at about 3% a month from January to April, fell to 1.5% from May to July, rose back to 3% in August and September, and settled at 1.5% for the rest of the year, while headcount moved from 60 to 66. The useful conversation is about what caused the two spikes, not about any single month's percentage.
Three habits make dashboards more useful: show the same measures every month so trends are visible, add a one-line comment against anything that moved, and end with one recommendation. For building these analyses into a regular practice, HR Calcy's HR analytics training covers data preparation and dashboards in more depth.
Frequently Asked Questions
How do I create an HR dashboard in Excel?
Keep an employee list with join and exit dates, calculate headcount, exits and attrition for the reporting month with COUNTIFS formulas or pivot tables, add a department breakdown and a monthly trend, and chart the results. Using an Excel table for the data and a month selector on the dashboard makes it reusable every month.
What metrics should an HR dashboard include?
A focused monthly HR dashboard usually includes closing headcount, exits, monthly attrition, the voluntary share of exits, hires, average time to fill, a breakdown of headcount and exits by department, and an attrition trend over the year. Add others only if the business will act on them.
Can I automate an HR dashboard in Excel?
Partly. A formula-based dashboard updates automatically when new rows are added, and Power Query can import and clean the monthly HRMS or payroll export so only a single Refresh All is needed. Full automation without any manual step requires a reporting tool connected directly to the HR system.
By Vishvass Yadav, PGDM-HR (XLRI Jamshedpur), 17 years in Indian HR and payroll. Last reviewed 10 October 2026. Template data is illustrative.