To report on staff absence well, keep a consistent record of every absence (date, reason, type and return date), then report on days taken per person, by leave type, over a chosen period, with remaining balances for context. Spreadsheets manage this until year end or a dispute, when the missing audit trail bites. A dedicated leave tracker builds the report as bookings happen and exports it to CSV in one click.
Why leave and absence records matter
Recording absence is not box-ticking. ACAS recommends keeping records of staff absence precisely because they let you manage fairly, support people properly and spot patterns early. Good records also do specific, practical jobs:
- Statutory Sick Pay. You need to know the dates of each sickness absence to work out SSP correctly. Since April 2026, SSP is payable from the first qualifying day, which means more short absences are recorded, not fewer.
- Fit notes. For a sickness absence of more than seven calendar days, an employee should normally provide a fit note (statement of fitness for work). You need the absence dates to know when that threshold is crossed.
- Fair, defensible decisions. If an absence pattern ever leads to a capability or disciplinary process, or a tribunal claim, contemporaneous records are your evidence that you acted consistently and reasonably.
- Holiday entitlement. You need a reliable count of leave taken and remaining so nobody under-takes their statutory minimum or over-books beyond their allowance.
The thread running through all of these is the same: the value is in the record being complete and consistent, not in how it looks. That is exactly where ad-hoc tracking falls down.
What a good absence report shows
A useful report answers a manager's real questions without a spreadsheet rebuild each time. In practice, that means five things:
| The report should show | So you can |
|---|---|
| Days taken per person | See who has taken what at a glance, and who has barely taken any leave. |
| A breakdown by leave type | Separate annual leave from sickness, unpaid, compassionate and the rest. |
| A period you choose | Report on this leave year, last year, a calendar year, or a custom range. |
| Remaining holiday balance | Give context: 12 days taken means more for someone on 22 than on 28. |
| A filter and an export | Narrow to one department, then hand payroll or a manager a clean CSV. |
One quiet but important test: the report must reconcile with the calendar and with each person's days remaining. If the report says someone took 14 days but their balance implies 11, nobody will trust either number. Totals that are computed from the same approved bookings the calendar shows are the only ones worth reporting.
The hidden cost of the spreadsheet
A shared spreadsheet is the default for a reason: it is free, familiar and fine for a five-person team in month one. The trouble is what it cannot do, and it tends to fail at the worst moment, usually year end or during a disagreement about who booked what.
- No audit trail. A spreadsheet cannot tell you who changed a cell, or when. If a booking vanishes or a balance looks wrong, there is no history to check.
- Formulas break silently. One inserted row, one dragged formula, one mistyped date, and the totals are wrong with no error message. You only notice when they do not add up.
- Versions multiply. The master file gets emailed, copied and edited in parallel. Within a quarter, nobody is sure which copy is current.
- Aggregation is manual. Producing a per-person, per-type report for a date range means filtering, copying and re-summing by hand, every time someone asks.
- It is hard to secure. A workbook that holds sickness reasons is sensitive personal data sitting in inboxes and shared drives, which is a genuine data protection problem (more on that below).
A spreadsheet only stays accurate if everyone updates it perfectly, forever. Book Time Off records every request and decision as it happens, keeps approvals in one place instead of scattered inboxes, and shows each person's days used and days remaining at a glance. The report you need later is assembling itself in the background.
→ Start free trialSickness data and your duties
This is the part spreadsheets handle worst. Information about a worker's health, including the reason behind a sickness absence, is special category data under UK GDPR. The ICO is clear that employers must take extra care with it.
- Lawful basis plus a condition. You need a lawful basis to process the data and an additional condition for special category data, for example because the processing is necessary for employment obligations.
- Minimise and restrict. Record only what you need, and limit who can see the reason for an absence. Not everyone who can see that someone is off needs to know why.
- Keep it secure and not too long. Health data should be held securely and kept no longer than necessary.
A single workbook listing every sickness reason, emailed around and openable by anyone with the link, runs against all three. A system that separates "who is off" from "why", and restricts the reason to the people who need it, is much easier to keep compliant. Book Time Off, for instance, lets a leave type be marked private so it shows to colleagues as a plain "Off" with no reason attached. That same privacy carries through to the Teams digest and calendar feeds, covered in our Microsoft Teams and Outlook leave management guide.
How a dedicated tool handles it
A dedicated leave tracker turns reporting from a monthly chore into a button. Because every booking is approved and stored in one place, the figures are always current and always reconcile with the calendar. The job becomes: choose a period, choose a department, read the totals, export.
| Name | Holiday | Sick | Unpaid | Total | Holiday left |
|---|---|---|---|---|---|
| Emma R. | 11.5 | 2 | 0 | 13.5 | 16.5 |
| James K. | 8 | 0 | 1 | 9 | 14 |
| Maya P. | 14 | 1 | 0 | 15 | 8 |
| All people | 33.5 | 3 | 1 | 37.5 |
The example above is the admin reports view in Book Time Off. It summarises approved leave taken per person, broken down by each leave type (the recorded ones like sickness and unpaid as well as annual leave), over a period you choose: this leave year, last leave year, this calendar year or a custom range. You can filter to one department, see per-type and grand totals, and export the whole view to CSV. Half-days show as 0.5, and a "holiday left" column gives each balance for context.
In Book Time Off, the admin reports view shows approved leave taken per person and per leave type for any period, filtered by department, and exports to CSV in one click. There are also CSV exports for team members, leave records and departments, so payroll gets the figures in a format it can use without retyping.
→ Start free trialReporting on absence trends
Totals are the foundation, but the more interesting question is usually about patterns over time. Two principles keep trend-spotting both useful and fair:
- Look at frequency, not just days. Frequent short absences are harder to cover than one long, planned one. Counting separate spells alongside total days surfaces that.
- Treat any flag as a prompt, not a verdict. A pattern is a reason to have a supportive conversation, never an automatic trigger for action, and you must always allow for disability, pregnancy and other protected characteristics.
If you want a single frequency-weighted score, the Bradford Factor is the best-known approach, and you can work one out with our Bradford Factor calculator. Just remember the legal cautions in that guide: a score flags a pattern, it does not explain it. Book Time Off keeps the clean per-person, per-type record that any trend analysis depends on; for visual trend charts you would pair it with a dedicated analytics tool or the calculator above.
A reporting checklist for managers
Whatever tool you use, a good monthly or quarterly reporting routine looks like this:
Capture the date reported, the leave type, and the return date, the same way every time, so the totals can be trusted.
Pick the leave year, last year or a custom range, and filter to the team or department you are reviewing.
Check days taken per person, split by leave type, and glance at remaining balances for context. Note anyone who has taken very little.
Send payroll the CSV it needs, and keep the reason for any sickness restricted to those who need it.
Where a pattern stands out, open a supportive conversation and consider reasonable adjustments. The report starts the discussion; it does not finish it.
For the wider absence process this reporting feeds into, see the Absence Management UK guide, and if you are still running everything on a workbook, our piece on how to manage staff holidays without spreadsheets covers the move in more depth.
Sources
Frequently asked questions
What absence records should a UK employer keep?
Keep a record of every absence: the date it was reported, the reason given, the type of leave, and the date the person returned. ACAS recommends recording absence so you can spot patterns, manage fairly and support staff. You will also need records to administer Statutory Sick Pay, to act on fit notes for absences over seven days, and to defend a decision if a dispute or tribunal claim arises. Where you record the reason for sickness, that is health data, so handle it under UK GDPR.
What should a good staff absence report show?
At a minimum: days taken by each person, broken down by leave type (annual leave, sickness, unpaid and so on), over a period you choose, with remaining holiday balances for context. Being able to filter by department and export the figures matters too, so payroll, a manager or an auditor can each get the view they need. The report should reconcile with the calendar and with each person's days remaining, otherwise the numbers are not trustworthy.
Why are spreadsheets risky for tracking staff leave?
Spreadsheets have no audit trail, so you cannot prove who changed what or when. Formulas break silently, versions multiply by email, and a single sort or deleted row can corrupt a year of data. They also struggle with data protection: a workbook holding sickness reasons is special category health data, and an emailed copy is hard to secure. For a small team it works until it does not, usually at year end or during a dispute.
Is health and sickness absence data covered by GDPR?
Yes. Information about a worker's health, including the reason for a sickness absence, is special category data under UK GDPR. The ICO expects employers to have a lawful basis and an extra condition for processing it, to keep it secure, to limit who can see it, and to keep it no longer than necessary. A shared spreadsheet of sickness reasons sitting in an inbox is exactly the kind of handling the ICO warns against.
How do I report on absence trends?
Start with clean, consistent records so the totals are reliable. Look at frequency (how many separate spells) as well as total days, because frequent short absences disrupt cover more than one long one. Tools like the Bradford Factor can flag frequency-weighted patterns, but treat any score as a prompt for a conversation, not a verdict, and always allow for disability, pregnancy and other protected characteristics. The reliable foundation is a per-person, per-type record you can filter by period.
Can I export staff leave data to CSV for payroll?
With a dedicated leave tracker, yes. Book Time Off, for example, has an admin reports view that summarises approved leave taken per person and per leave type over a chosen period, filterable by department, with a one-click CSV export of the whole view. It also exports team members, leave records and departments as CSV, so payroll gets the figures in a format it can use without anyone retyping anything.