Indzara - Simple and Effective Templates | Calculating Number of Employees easily using formulas in Excel - HR KPIs - HR Metrics @indzara | Uploaded 2 years ago | Updated October 03 2023
Step-by-step tutorial on how to calculate # of employees in a company using formulas in Excel. I will take 4 common scenarios of input data and provide 4 simple Excel formulas to handle them.

00:00 Introduction to HR KPIs
01:04 Scenario 1: Data has only active employees (COUNTA)
05:08 Scenario 2: Data has both active and future employees (TODAY, COUNTIF)
07:22 Scenario 3: Data has all employees with a status column (COUNTIF)
09:04 Scenario 4: Joiners and Leavers data are in two separate datasets (COUNTIF, TODAY)

Tutorial: https://indzara.com/2021/09/hr-kpi-number-of-employees/

Visit https://indzara.com/hr for Excel templates and tutorials related to HR Analytics, HR metrics, HR KPIs and HR Dashboards.

#hranalytics #hranalysis #hrmetrics #hrkpis #excel #peopleanalytics

I would love to hear from you if your company’s data is in one of these 4 types or if you have a different data structure. Please let me know in the comments.

Excel tutorial

Scenario 1: Only Active Employees in our dataset
This is the simplest scenario where you are already given only active employees in your dataset.
Formula: =COUNTA(T_EMP[ID])
In this case, we use the COUNTA function and choose the ID column in our T_EMP table (I had named the table T_EMP)

Scenario 2: Active and Future Employees in our dataset
If your dataset has employees who have not started yet, then our previous formula will result in inflating the count of employees. We need to only count when the employee’s start date is today or before today.
In this case, we use the COUNTIF function and choose the Start Date column in our T_EMP table (I had named the table T_EMP) with a condition less than or equals TODAY.

Scenario 3: All Employees in our dataset with a Status column
Sometimes, you may receive a dataset where there is a Status column which will say ‘Active’ or ‘Inactive’ for each employee. All the employees regardless of when they started and if/when they left will be included.
In this case, we use the COUNTIF function and choose the Status column in our T_EMP table (I had named the table T_EMP) with a condition equal to “Active”.

Scenario 4: Joiners and Leavers in 2 different datasets
Assuming, we have the joiners (hires) and leavers (exits) in 2 separate datasets as shown below.
I have named the Joiners table T_EMP_JOINS and the leavers table T_EMP_EXITS.
In this case, we use the COUNTIF function and count all the employees in the T_EMP_JOINS table with start date less than or equals Today, and then subtract the leavers (count of employees in T_EMP_EXITS table with Exit date less than or equals Today).

I hope this was useful. In your company, how do you calculate the number of employees? I would love to hear from you in the comments section.
If there is a specific metric or KPI you would like me to do a video about, please let me know.

Visit https://indzara.com/hr for Excel templates and tutorials related to People Analytics, HR Analytics, Workforce Analytics, Talent Analytics, HR metrics, HR KPIs and HR Dashboards.