Kpiloom
KPIs

HR Dashboard: 12 HR Metrics and How to Build One in Excel

The 12 HR metrics worth tracking, a formula for each, a simple layout, and a step-by-step way to build an HR dashboard in Excel or Google Sheets.

By the Kpiloom team · · 6 min read


An HR dashboard is one screen that answers the questions a founder or HR lead keeps asking: how many people do we have, who is leaving, how long hiring takes and what it all costs. Most small teams already hold this data in a payroll export, a leave register and a hiring sheet. The problem is that it sits in three places, so nobody sees the pattern until a good manager resigns or a key role stays open for two months.

This guide lists the 12 HR metrics worth tracking, gives a formula for each, and then walks through building the report in Excel step by step. You can follow it with your own data or start from a ready template.

The 12 HR metrics, with formulas

The first eight are the core cards. The last four add depth once the basics are in place.

#MetricFormulaWhat it tells you
1HeadcountCOUNT of employees with status = ActiveSize of the team at period end
2Attrition rateLeavers ÷ Average headcountShare of people who left
3Time to hireAVERAGE of (Offer accepted − Opening date)How long a seat stays empty
4Open positionsCOUNT of open requisitionsHiring workload right now
5AbsenteeismDays absent ÷ Scheduled working daysTime lost to unplanned absence
6Training hoursSUM of training hours loggedHow much the team is learning
7eNPS% Promoters − % DetractorsWhether staff would recommend you
8Avg salaryTotal payroll ÷ HeadcountPay level and payroll pressure
9Early attritionLeavers with under 12 months' service ÷ All leaversWhether hiring or onboarding is failing
10Offer acceptance rateOffers accepted ÷ Offers madeHow competitive your offers are
11Cost per hire(Agency fees + Ads + Referral bonuses) ÷ HiresWhat each new joiner costs to find
12Overtime hoursSUM of hours above the standard weekHidden strain on the team

How to read the core eight

Headcount on its own says little. Put it next to open positions: a team of 60 with 9 open roles is running 15% short, and that shows up later as overtime and absence.

Attrition rate is best viewed monthly and as a rolling 12-month figure. A single bad month in a small company can be two people. The rolling number smooths that out and shows the real trend.

Time to hire matters most for roles that block revenue. If sales hires take 45 days while support hires take 20, the bottleneck is clear.

Absenteeism above roughly 3–4% of scheduled days is worth a look, but compare it with your own history first. A spike in one department usually points to a manager or workload problem, not a company-wide one.

eNPS is a survey score from −100 to +100. Run it quarterly with the same question so the numbers are comparable. The trend is more useful than any single score.

Avg salary should be read with attrition. Rising leavers in a team paid below the company average is a strong signal.

What a good layout looks like

People read a dashboard top to bottom, so put the answers first and the detail later:

  1. Top row: 6–8 KPI cards, each with this month, last month and the change.
  2. Middle: a headcount trend line (hires and leavers as bars underneath), plus attrition by department.
  3. Bottom: a hiring funnel (applications, interviews, offers, joins) and an absence chart by month.
  4. Filters: department, location and month, set with slicers or drop-downs.

Keep it to one screen. If the team has to scroll, the bottom half rarely gets read.

How to build an HR dashboard in Excel, step by step

Step 1: one employee table. Create a sheet with one row per employee: ID, name, department, location, join date, exit date (blank if active), monthly salary and status. Convert it to an Excel Table (Ctrl + T) so formulas grow with new rows.

Step 2: event tables. Add separate tables for leave (employee ID, date, type, days), hiring (role, department, opening date, offer date, accepted date, source) and training (employee ID, course, hours, date).

Step 3: a calculation sheet. Pick the month in one cell, then calculate each metric with COUNTIFS, SUMIFS and AVERAGEIFS. For example, active headcount at month end is a COUNTIFS on join date up to the month end and exit date blank or after it.

Step 4: the dashboard sheet. Link each KPI card to the calculation sheet. Add a line chart for headcount by month and a bar chart for attrition by department. Use conditional formatting to color the change arrows green or red.

Step 5: filters. Build a PivotTable from the employee table, add slicers for department and location, and connect the slicers to every pivot on the sheet.

Step 6: protect and refresh. Lock the calculation cells, leave the data tables open for editing and add a short "how to update" note at the top. Each month the HR lead pastes in new rows and presses Refresh All.

The full build takes a few evenings the first time. Our general guide on how to create a dashboard in Excel covers the chart and slicer steps in more detail.

HR dashboard examples by industry

The core metrics stay the same, but the emphasis changes with the business:

BusinessMetric to watch firstWhy
IT servicesAttrition rate and time to hireBillable staff leaving directly cuts revenue
ManufacturingAbsenteeism and overtime hoursMissed shifts stop the line
Hospital or clinicOpen positions and overtimeRota gaps affect patient care
Retail chainEarly attritionStore staff often leave in the first months

If you'd rather start from a finished file, there is an Excel HR template built for IT services firms, a workforce and absence tracker for factories in Excel, a staffing dashboard for hospitals in Google Sheets and a store people report for retail in Google Sheets. Each one comes with sample data, so you can see how the cards and charts behave before pasting in your own records.

Excel or Google Sheets?

Excel suits a single HR lead working on a laptop with payroll exports. Google Sheets is easier when several managers need to view or update the same file, because it stays in one shared link. The formulas in this guide work in both. You can browse every HR template for Excel and Sheets or go straight to the Excel HR templates.

Common mistakes to avoid

  • Counting headcount on the wrong date. Pick month end and stick to it.
  • Mixing voluntary and involuntary exits. Track them separately; they mean different things.
  • Too many cards. Eight is plenty. Move the rest to a second sheet.
  • No owner. One person should refresh the file on a fixed day each month.

Employee data is personal. Keep the file access-controlled, share summary views rather than salary detail, and follow your local data protection and employment rules. This guide is general information, not legal or HR compliance advice.

Get the dashboards from this guide

IT Services HR Dashboard

Manufacturing HR Dashboard

Frequently asked questions

What should an HR dashboard include?

Start with eight cards: headcount, attrition rate, time to hire, open positions, absenteeism, training hours, eNPS and average salary. Add a headcount trend, attrition by department and a hiring funnel underneath.

How often should HR metrics be updated?

Monthly is enough for most small and mid-sized teams. Pick a fixed day, use the month-end headcount, and let one person own the refresh so the numbers stay consistent.

Can I build an HR dashboard in Google Sheets instead of Excel?

Yes. COUNTIFS, SUMIFS and AVERAGEIFS work the same way in Google Sheets, and a shared link makes it easier for several managers to view the same report.

Skip the build. Start with a ready dashboard.

Templates for Excel and Google Sheets, with live previews.

Browse dashboards

Dashboards related to this article

Keep reading