Why Excel Remains the Go-To HR Tool for Small and Medium Businesses
Dedicated HR software platforms offer compelling feature sets, but they come with significant costs, implementation overhead, and ongoing subscription fees that are difficult to justify for organisations with fewer than 50 employees. For small and medium-sized businesses, a well-designed set of Excel workbooks can handle the three most common HR administrative tasks — leave tracking, payroll calculation, and performance review management — with far less complexity and cost than enterprise HR systems.
This guide builds practical, functional Excel solutions for each of these tasks, with formulas, formatting techniques, and data structures that make them maintainable and reliable in real-world use. All features described are available in Office 2024 Professional Plus, Office 2021 Professional Plus, and Office 2019 Professional Plus.
Important note on data protection: HR data contains sensitive personal information. Any Excel workbooks storing employee data should be password-protected, stored in access-controlled locations, and handled in accordance with UK GDPR requirements. Do not store employee data in publicly accessible shared drives or cloud locations without appropriate access controls.
Building a Leave Tracker
Recommended Structure
An effective leave tracker needs three sheets within a single workbook:
- Employee Register — Employee ID, name, department, contract type, annual leave entitlement (days), leave year start date.
- Leave Requests — Log of all leave requests with: EmployeeID, RequestDate, LeaveType (Annual/Sick/Parental/TOIL), StartDate, EndDate, WorkingDays, Status (Approved/Pending/Rejected), ApprovedBy.
- Leave Summary — One row per employee showing entitlement, days taken, days remaining for the current leave year.
Calculating Working Days (Excluding Weekends and Bank Holidays)
The NETWORKDAYS function counts working days between two dates, automatically excluding weekends. For UK-specific calculations that also exclude bank holidays:
=NETWORKDAYS(StartDate, EndDate, BankHolidayRange)The BankHolidayRange should be a named range containing all UK bank holidays for the leave year. Create a dedicated “Bank Holidays” sheet with a column of UK bank holiday dates (available from the UK Government website at gov.uk). Name the range BankHolidays via Formulas > Name Manager > New.
The formula for working days in the Leave Requests sheet becomes:
=NETWORKDAYS(D2, E2, BankHolidays)Calculating Leave Taken (SUMIFS by Employee and Leave Type)
In the Leave Summary sheet, the days of approved annual leave taken for each employee:
=SUMIFS(LeaveRequests[WorkingDays],
LeaveRequests[EmployeeID], A2,
LeaveRequests[LeaveType], "Annual",
LeaveRequests[Status], "Approved",
LeaveRequests[StartDate], ">="&DATE(YEAR(TODAY()),4,1),
LeaveRequests[StartDate], "<"&DATE(YEAR(TODAY())+1,4,1))This SUMIFS formula sums the WorkingDays column where the EmployeeID matches, the leave type is Annual, status is Approved, and the start date falls within the current leave year (assumed to run April to April — adjust the month numbers for your organisation's leave year).
Remaining Entitlement
=Entitlement - DaysTaken - CarryOverAdd conditional formatting to highlight employees with fewer than 5 days remaining (orange warning) or who have taken more leave than their entitlement (red alert).
Visual Calendar View
For a team-level visual calendar showing who is on leave when, create a grid with employees in rows and dates in columns. In each cell, use a formula that checks whether each employee has approved leave on each date:
=IF(COUNTIFS(LeaveRequests[EmployeeID],$A2,
LeaveRequests[Status],"Approved",
LeaveRequests[StartDate],"="&C$1)>0, "L", "")Apply conditional formatting to highlight cells containing "L" in a colour (e.g., green for annual leave, amber for sick leave if you add a separate check). This gives a visual team availability map that managers can check at a glance.
Building a Payroll Calculator
Note: The following is illustrative. Always verify payroll calculations with a qualified accountant or payroll professional, and ensure compliance with HMRC requirements.
Basic Payroll Sheet Structure
A UK payroll workbook needs:
- Employee reference data: National Insurance number, tax code, NI category, pension scheme enrolment
- Pay period data: gross pay, hours worked (for hourly workers), overtime, bonuses, expenses
- Deductions: PAYE income tax, National Insurance contributions, pension contributions (employee and employer), student loan deductions, other deductions
- Net pay calculation
Gross Pay Calculation
For salaried employees:
=AnnualSalary/12For hourly workers with overtime:
=(RegularHours*HourlyRate)+(OvertimeHours*HourlyRate*1.5)National Insurance Contributions (2025/26 rates)
Employee NI (Class 1) for the 2025/26 tax year uses the following bands (verify current rates with HMRC):
- Below Primary Threshold (€1,048/month): 0%
- Primary Threshold to Upper Earnings Limit (€4,189/month): 8%
- Above Upper Earnings Limit: 2%
=IF(GrossPay<=1048, 0,
IF(GrossPay<=4189, (GrossPay-1048)*0.08,
(4189-1048)*0.08 + (GrossPay-4189)*0.02))Income Tax (PAYE) — Basic Rate Calculation
For employees on a standard 1257L tax code (€12,570 personal allowance):
MonthlyPersonalAllowance = 12570/12 = 1047.50
TaxableIncome = MAX(GrossPay - 1047.50, 0)
=IF(TaxableIncome<=3141.67, TaxableIncome*0.20,
3141.67*0.20 + (TaxableIncome-3141.67)*0.40)This handles basic (20%) and higher (40%) rate. Additional rate (45%) applies above €125,140 annually. Always confirm with HMRC tables for the current tax year, and account for adjusted tax codes which move the personal allowance threshold.
Pension Contributions (Auto-Enrolment)
For automatic enrolment pensions, contributions are calculated on qualifying earnings between the lower (€6,240/year, €520/month) and upper (€50,270/year, €4,189.17/month) thresholds.
QualifyingEarnings = MAX(MIN(GrossPay, 4189.17) - 520, 0)
EmployeeContribution = QualifyingEarnings * 0.05
EmployerContribution = QualifyingEarnings * 0.03Net Pay
=GrossPay - EmployeeNI - IncomeTax - EmployeePension - OtherDeductionsBuilding a Performance Review Tracker
Sheet Structure
A performance review workbook typically includes:
- Review Schedule — Employee, review date, reviewer, review type (annual/mid-year/probation), status (Scheduled/Completed/Overdue)
- Review Records — Individual review outcomes with ratings, objectives set, development actions, notes
- Ratings Dashboard — Summary of ratings by department, manager, and review period
Rating Scale and Consistency
Use a consistent numeric scale (e.g., 1-5) with defined descriptors stored in a reference table. Drop-down validation lists for rating cells (covered in a separate guide) prevent free-text entry that makes aggregation impossible. Keep rating definitions visible on a reference sheet so all reviewers use the same standards.
Identifying Overdue Reviews
In the Review Schedule sheet, flag overdue reviews with a formula:
=IF(AND(ReviewDate<TODAY(), Status"Completed"), "OVERDUE", "")Apply red conditional formatting to cells containing "OVERDUE". Sort by this column periodically to ensure no reviews fall through the cracks.
Objective Tracking
For each review, record 3-5 objectives set for the next period with owners, target dates, and a current status. A simple COUNTIF summary on the dashboard can show the proportion of objectives rated On Track, At Risk, or Not Started across the organisation, giving HR a quick health check on goal achievement without reading every individual review.
Dashboard with Conditional Formatting
On the dashboard sheet, use AVERAGEIFS to calculate average ratings by department:
=AVERAGEIFS(ReviewRecords[Rating],
ReviewRecords[Department], A2,
ReviewRecords[ReviewType], "Annual",
ReviewRecords[ReviewYear], 2026)Apply a colour scale (green-yellow-red) to the average rating cells so the dashboard provides an immediate visual signal of team performance patterns.
Protecting Sensitive HR Workbooks
HR workbooks must be protected against accidental editing and unauthorised access. Use three levels of protection:
- File password — Set via File > Info > Protect Workbook > Encrypt with Password. Prevents anyone without the password from opening the file.
- Sheet protection — Select the cells that should remain editable (input fields), unlock them via Format Cells > Protection > Uncheck Locked, then protect the sheet via Review > Protect Sheet. Formula cells and headers become read-only.
- Workbook structure protection — Review > Protect Workbook prevents sheets from being added, deleted, or renamed.
Combining these three layers ensures that only authorised users can open the file, only intended cells can be edited, and the structure cannot be accidentally or maliciously altered. Store the files in a location with access controls appropriate to their sensitivity.
Automating Repetitive HR Reporting Tasks
Once your HR workbooks are structured with consistent data layouts and named ranges, many routine reporting tasks can be semi-automated using Excel's built-in tools.
Monthly Payroll Summary with SUMIFS
A departmental payroll summary that aggregates gross pay, tax, NI, and net pay by department can be built using SUMIFS formulas that reference the payroll data sheet. These update automatically whenever payroll data is refreshed:
=SUMIFS(PayrollData[GrossPay], PayrollData[Department], A2, PayrollData[Month], $B$1)Where A2 contains the department name and B1 contains the month number. Changing B1 to a different month updates the entire summary table for that period.
Staff Turnover Analysis
Track employee start and end dates in your Employee Register. Calculate monthly headcount and turnover with COUNTIFS:
Active at month end: =COUNTIFS(StartDate,""&EOMONTH(A2,0))+COUNTIFS(StartDate,"<="&EOMONTH(A2,0), EndDate,"")Where the empty string in the last criterion captures employees with no recorded end date (still employed). Plot monthly active headcount as a line chart for a visual trend of workforce size over time.
Sickness Absence Rate
Calculate the Bradford Factor for each employee — a scoring system that weights frequent short-term absences more heavily than infrequent long-term absences:
Bradford Factor = (Number of absence spells)^2 × Total days absentTrack this quarterly. Employees with Bradford Factors above the organisational threshold (typically 200-400 depending on policy) trigger a review conversation. Automating the calculation eliminates the manual tracking burden and ensures consistent application of the policy.
Probation Review Reminders
Add a column to the Employee Register calculating the probation review due date: =StartDate + 90 (or your probation period in days). Apply conditional formatting to highlight employees whose review is due within the next 14 days. Sort by this column weekly as a simple action list for HR without requiring calendar entries or separate reminders.
Using Excel for Employee Learning and Development Tracking
Beyond leave, payroll, and performance reviews, HR teams frequently need to track training completion, qualifications, and professional development activities. Excel provides a practical framework for this without dedicated LMS infrastructure.
Training Matrix
A training matrix tracks which employees have completed which required training courses. Structure it as a table with employees in rows and required training courses in columns. Each cell records the completion date (or "N/A" for roles that do not require that training).
Apply conditional formatting: red for cells that are empty but required (employees who have not completed mandatory training), amber for completions older than a defined refresh period (e.g., fire safety training completed more than 3 years ago), and green for current completions. The EDATE function calculates the expiry date from the completion date and the required refresh interval: =EDATE(CompletionDate, 36) for 3-year validity.
Status = IF(CompletionDate="", IF(Required="Y", "OVERDUE", "N/A"),
IF(EDATE(CompletionDate, RefreshMonths)<TODAY(), "EXPIRED", "Current"))Qualification Expiry Tracker
Professional qualifications — first aid certificates, forklift licences, accountancy CPD hours — often expire and require renewal. Track qualification name, issue date, and expiry date in a dedicated sheet. Calculate days until expiry: =ExpiryDate-TODAY(). Apply a colour scale: green for >90 days remaining, amber for 30-90 days, red for <30 days or already expired. Sort by days remaining to identify the most urgent renewals at a glance.
Excel HR Templates: Making Them Work in Practice
The most carefully designed HR workbook will fail if it is not adopted consistently. Three practical considerations determine whether an Excel HR solution succeeds in day-to-day use:
Single Source of Truth
Every HR metric should be calculated from a single source dataset. If leave data exists in three different spreadsheets maintained by different managers, they will diverge and create discrepancies. Centralise all HR data in one workbook (or one set of linked workbooks with a clear master data source) and make all reports draw from that single source rather than maintaining parallel copies.
Restricting Access While Allowing Input
HR data is sensitive. Use Excel's protection features (sheet protection with selectively unlocked input cells, workbook password protection) to ensure that employees can see their own data — if you create self-service views — while preventing unauthorised access to the full dataset. Store master HR workbooks in a folder accessible only to the HR team, with summary reports exported or shared to appropriate audiences without the underlying data.
Audit Trail for Changes
Excel does not natively log who changed what in a shared workbook. For HR data where change history matters (payroll adjustments, leave balance corrections), log changes manually in a dedicated audit log sheet: each row records the date, the user making the change, the field changed, the old value, and the new value. This provides accountability and a correction trail without requiring database-level change tracking infrastructure.
Seasonal Workload Planning
HR leave data can feed directly into a capacity planning analysis. By comparing approved leave in a specific period against the team's normal staffing level, you can calculate the effective headcount available for each week or month. Represent this as a bar chart alongside planned project workload to identify periods of potential under-resourcing — enabling proactive conversation about leave scheduling rather than reactive crisis management when a project hits a critical period with half the team on holiday.
