HR sometimes has to deal with a lot of information which are all moving wheels. Employees come and go, some are suspended and some retire or get retrenched. Employee records are critical to the organisation and their accuracy can be in doubt due to the moving parts. Most HR problems are found too late.
An unfair dismissal claim lands, and the disciplinary file is missing. A labour inspector asks for employment contracts that were never signed. An internal audit finds that someone who resigned six months ago is still being paid.
In almost every case, the warning signs were already sitting in the HR department’s own records. Nobody was measuring them.
Every HR function, whether it serves 15 people or 1,500, needs to cover the same eight areas. In each one, a small amount of data analysis tells you whether you are in control or exposed. You don’t need an HR system or a data team. A well-organised Excel workbook and an hour a month will do.
Here are the eight areas, what typically goes wrong in each, and the numbers worth tracking.
1. Contracts & Recruitment
What goes wrong: People start work before contracts are signed. Fixed-term contracts expire, and the employee keeps working without a renewal. Vacancies take months to fill.
What to track:
- % of employees with a signed, current contract
- Contracts expiring in the next 60 days
- Average time to hire (vacancy opened to start date)
How to analyse it: Keep a staff list with a “Signed contract” column (Yes/No) and a “Contract end date” column.
- Contract coverage:
=COUNTIF(D2:D500,"Yes")/COUNTA(D2:D500) - Contracts expiring soon:
=COUNTIFS(E2:E500,">="&TODAY(),E2:E500,"<="&TODAY()+60)
What good looks like: 100% coverage, and no contract reaching its end date without a decision.
2. Personnel Records
What goes wrong: Files are missing copies of ID, qualifications, signed policies or next-of-kin details. When a dispute or audit comes, the evidence isn’t there.
What to track:
- File completeness % for each employee
- Number of incomplete files
How to analyse it: List your required documents as columns (ID copy, contract, CV, certificates, next of kin, signed code of conduct). Enter 1 if the document is on file and 0 if it isn’t.
- Completeness per employee:
=SUM(C2:H2)/6 - Number of incomplete files:
=COUNTIF(I2:I500,"<1")
What good looks like: 95% or more of files complete. Anything below 80% is a red flag.
3. Payroll
What goes wrong: Leavers stay on the payroll, pay rises go through without approval, and two “employees” are paid into the same bank account. Payroll is usually the biggest cost in the business and one of the easiest places to hide fraud.
What to track:
- Month-on-month change in gross pay, and what explains it
- Employees whose pay moved by more than a set threshold
- Duplicate bank account and ID numbers
How to analyse it: Put last month’s and this month’s payroll side by side.
- % pay change per employee:
=IF(C2=0,"",(D2-C2)/C2) - Duplicate bank account flag:
=IF(COUNTIF($E$2:$E$500,E2)>1,"Duplicate","")
Then reconcile the total: last month’s gross pay, minus leavers, plus joiners, plus or minus pay changes, should equal this month’s gross pay. If it doesn’t, something is unexplained.
What good looks like: Every change explained and approved before the pay run.
4. Leave & Attendance
What goes wrong: Leave balances build up into a large hidden liability, absenteeism goes unnoticed, and leave is taken but never recorded.
What to track:
- Absenteeism rate
- Employees with excessive leave balances
- Leave liability in money
How to analyse it:
- Absenteeism rate: days absent ÷ (headcount × working days in the period)
- Leave liability per employee:
=F2*G2(days owed × daily rate) - Employees over the cap (e.g. 30 days):
=COUNTIF(F2:F500,">30")
What good looks like: Absenteeism below about 3%, and nobody sitting on an unusually large leave balance.
5. Performance & Training
What goes wrong: Appraisals are skipped, so pay rises and promotions have no documented basis. The training budget is spent, but nobody knows who was trained or whether it helped.
What to track:
- % of appraisals completed on time
- Training hours per employee
- Staff with no training in the last 12 months
How to analyse it:
- Appraisal completion:
=COUNTIF(H2:H500,"Completed")/COUNTA(H2:H500) - Training hours per employee: total training hours ÷ headcount
What good looks like: 90% or more of appraisals completed, and every employee trained at least once a year.
6. Discipline & Grievance
What goes wrong: Cases drag on for months, procedures aren’t followed, and outcomes aren’t documented. These are exactly the cases that turn into lost labour disputes.
What to track:
- Open cases
- Average days to close a case
- Cases open longer than your policy allows
How to analyse it: Keep a case register with an opened date, a closed date and a status.
- Days open:
=IF(H2="",TODAY()-G2,H2-G2) - Open cases:
=COUNTIF(I2:I200,"Open") - Overdue cases (e.g. over 30 days):
=COUNTIFS(I2:I200,"Open",J2:J200,">30")
What good looks like: Cases closed within the timeframe your policy sets, with every step documented.
7. Health, Safety & Wellbeing
What goes wrong: Injuries are treated at the first aid box but never reported. Near misses go unrecorded, so the same hazard causes a serious accident later.
What to track:
- Incident rate per 100 employees
- Near misses reported
- Open corrective actions
How to analyse it:
- Incident rate:
=Incidents/Headcount*100 - Compare the incident register with the first aid register. Every injury in one should appear in the other.
What good looks like: A falling incident rate, and a healthy number of near misses being reported. Zero near misses usually means under-reporting, not a perfectly safe workplace.
8. Policy & Compliance
What goes wrong: Policies are years out of date, staff never signed that they read them, and statutory returns are filed late.
What to track:
- Policies past their review date
- % of staff who have acknowledged key policies
- Statutory returns filed on time
How to analyse it: List each policy with its last review date.
- Next review date (every 2 years):
=EDATE(C2,24) - Status:
=IF(TODAY()>D2,"Overdue","Current")
What good looks like: No overdue policies, and every return filed by its due date.
Keeping Track: Build a One-Page HR Scorecard
Measuring once is useful. Measuring every month is what gives you control.
Bring one headline number from each area onto a single HR Scorecard sheet with these columns:
| Area | Metric | Target | Last Month | This Month | Trend | Status |
|---|
For a metric where higher is better, a simple red-amber-green status formula is:
=IF(E2>=C2,"Green",IF(E2>=C2*0.9,"Amber","Red"))
Add conditional formatting so each status shows in its colour, and you have an HR dashboard that management can read in 30 seconds.
Four habits that make it work:
- Keep one source of truth. Keep one staff master list and link everything to employee numbers.
- Keep a monthly rhythm. Update the scorecard on the same day every month, ideally before payroll is approved.
- Watch the trend, not just the number. A file completeness rate that falls from 95% to 88% matters more than either number on its own.
- Give every metric an owner. A number with nobody responsible for it won’t improve.
Not Sure Where to Start?
You don’t need all eight areas measured perfectly by next month. Start by finding out where your biggest gaps are.
Our free HR Health Check does that in about 15 minutes. You answer 25 plain-language questions across these same eight areas, get a red-amber-green score for each one, and receive an action plan built automatically from your answers. Add an owner and a date to each action, and you have a plan ready for management.
It’s an Excel workbook with no macros, works in Excel 2007 and later, and can be used in any country.
👉 Download the free HR Health Check:
https://eunoiadata.com/product/free-hr-audit-checklist-self-assessment-excel-hr-health-check-eunoia/