headcount-report

95% of HR professionals fall into the trap of many columns when they prepare their headcount reports.

What is a headcount report?
A headcount report presents the number of people employed by the company at a given time, broken down into one or more of the following categories: job grades, department, age, gender, race, nationality, educational level, Salary, location, years of service, etc.

The trap of many columns

Most of the HR records that I have encountered in my projects demonstrated one pattern. They like to put the employee’s family details together with the employee’s personal data. And because they did that, they have to create many columns to capture the family details such as name, relationship, gender, DOB, etc. They would create a set of columns for the spouse, 1st child, 2nd child, all the way to the 5th child or more.

And for unmarried employees, a set of columns for the father and another for the mother. Sometimes, a set of columns is reserved for the siblings’ details too. Imagine the number of columns the HR professional has to work on. It significantly increases the chance of making a mistake in updating the HR records.

And because of this trap, they have to struggle with a simple headcount report which should take only 5 seconds to prepare.

The correct way to capture employees’ data

Instead of putting all the details into one worksheet, HR should split the details up into 3 different worksheets.

The 3 worksheets headcount report solution

The first worksheet should contain company-related details about employees, such as employee number, name, department, date in the department, designation, work location, etc. This should be the main worksheet and should be captured every month. Employees may switch to another department, relocate, etc.

The second worksheet should contain employees’ personal details such as personal ID number, nationality, date of birth, gender, highest qualification, marital status, etc.

The third worksheet captures the family members’ details such as name, relationship, marital status, contact number, gender, date of birth, nationality, etc. The personal details are similar to the second worksheet.

With the 3 worksheets, you will be able to create any headcount report instantly using Excel Power Query and Pivot Table, including the report to compare the actual headcount against the budget.

If you already have the 3 worksheets but do not know how to create the report instantly, come to our HR Excel course and I will show you how. You will be blown away by what you missed out all this while.