HR Tracker

The HR Tracker can be used to track sickness, absent staff, holidays, and lateness. Basically, whatever you want to track you can use conditional formatting in Microsoft Excel to help analyse it.

Conditional Formatting

Each word or letter can be formatted to display a colour in Excel. In this case, blue = sickness, red = lateness, black = absent and mustard for holidays. The black colour will require the font to be white otherwise the letter will not be seen.

Countif Function

The Countif function is a great function to count the occurrence of a text string within a range. In this example the Countif function counts the number of each letter type.


The whole point of HR tracing is to track and summarise data trends and act upon the results. Each month of HR data is tracked and added up. The Sum function is used to add all three-monthly totals.

Charting Data

The charts in the HR tracker represent three months of sickness, holidays, absence and lateness. Each person has their own HR chart which is colour coded to match the conditional formatting in the HR calendar.

Excel courses can be found here

Online courses can be found here

The example shown only shows data for two people and for only three months. If your organisation is much larger, a sheet per person may be required. Alternatively, a database model, such as Microsoft Access might be better. For small companies, Excel should be fine. The Bradford factor is mentioned in the video. This is a tool to capture people that tend to go off on the sick, as and when, they want. The Bradford factor multiplies sick episode by sick episode by the total days sick. A sick episode is one day of sickness, three continuous days off sick, would still be one episode.

Leave a Reply

You must be logged in to post a comment.


“The course was really thorough and Steve was very knowledgeable and explained things very well. I’ve not used Microsoft Project before but now I feel very confident that I’ll be able to use it for what I need to do in my job”.

Craig Forster
Estimator, John F Hunt Regeneration Ltd

“Steve’s background in IT training clearly provided him with great organisational and managerial skills which he put to good use at Second Chance Enterprises. His ability to see the long term strategy as well as manage the day to day management is one of his biggest strengths.”

Chriss Graham
Project Coordinator at Daisy Group

“I studied with Steve when we completed our CiPD CTP together. During the course I found Steve to be very personable and also very professional.  Steve was first on my list of suppliers. Steve was very competitively priced and very accommodating.”

Simon Snowdon
European Training Manager at HP Enterprise Services

“Steve delivered a number of Microsoft Office Training Sessions to a range of employees across Newcastle College. The service provided by Steve is second to none. I have no hesitations in recommending him to other employers.”

Christine Jackman
Learning Manager(North) - Winner of Training Programme of the Year NECCA