Using Excel for HR Analytics

Excel for HR analytics. Now, HR analytics has to do with the ability to use data or human resources data to aid decision making. Or in some other places, some other interesting definition of HR analytics is the ability to use data to solve people's problems. Now, in order for you to do that, you must get familiar with analytical tools and one very interesting analytical tool that is available to everybody for use is Microsoft Excel. So we're going to be looking at how to use Microsoft Excel for analytical purposes as it related to HR analytics and a very good place to start for me is to look at what we call statistics in HR. So what are the interesting statistics that you get familiar with as an HR professional? that would be useful for analytical purpose. One of such is base measure. So when we're talking about base measure, we're talking about basic count. Another very typical example is workforce headcount. That's a measure that you should be familiar with as an HR person. That's a starting point. Number of employees or the headcount, the numbers of hours, hires, how many individuals were hired, new hires, number of exits, how many individuals left the organization, what's the date of birth of your new employees. These are typical data you normally capture in your employee record form. Now when you now start to use this basic count in calculation, then we'd arrive at what we call a calculated measure. The calculated measure asks to do with. You use zine. your base count in a formula or in a calculation. So if I have date of birth and I have a date of reference then I can find employee age. If I know the number of exits and the head count then I can use that to get my turnover rate. If I know the salaries of each employee then I can divide by the total numbers of employees and I can get the average employee salary and as well as the male to female. ratio now all of this will be practicalizing using ms excel which is important for you to have the theoretical understanding so that when we start to do the calculation in excel it's clear what we're trying to achieve Another interesting statistical measure you should get familiar with is attributes. So now we've calculated the employee age and we want to find out the age group or the age band in terms of baby boomers, generation X, millennials and generation Z. Trying to understand the demographics of the age within the organization, we can do that by using what we call attributes. When you classify certain age together to create an age band you could also do that attribute by using gender or by looking at the experience level of the employees or their department or their qualification whichever becomes applicable now also very interesting application of statistics in excel and even in relation to human resources and hr analytics is your measure of central tendency talking about your mean median and mode which you're going to be using in a couple of minutes to drive home some calculation in hr and it relates into hr analytics because the foundational part of hr analytics is gathering data then starting to analyze the data to draw insight from the data to drive your decision so let's get into excel for some practical session now okay so uh we're in excel at the moment and i'm using office 365 for this illustration so the very first thing I want us to illustrate is the average so let's look at this typical example we have salaries annual salaries of employees in Excel which has been formatted for accounting purposes now what is the average annual salary of employees with the following salaries now to do that in Excel we're going to be using the mean or the average function and that is given by writing average you will see it is already auto completing you double click on it average it asks you what are the numbers you want to find the average since I'm trying to get average annual salary of employees with the following salary all I need to do is to highlight the entire range of data that they the salaries are found and if I close bracket and I enter what you have here is the average salary of these six different salaries so that's very makes it very easy to do in Excel now another interesting part of Excel that you must get familiar with is what we call the today function now Excel are HR professionals are the residents of dates I'm talking about date of birth of employees, talking about date of entry or date they were hired, talking about exit date, retirement date, probation and the rest. So date is very critical. Now one classic Excel function that HR professionals must get familiar with is the TODAY function. And it is written as TODAY equals to. Anytime you're writing a formula in Excel, it will start with equals to. It starts with TODAY. And you can already see the today function returns the current date formatted as a date. If I double click on this and I say today function open bracket close bracket and I press enter, it is giving me today's date as at the time of this recording is 18th of October 2020. Now, if I open this document tomorrow, it is still going to return today's date. Now, how does this become useful? This becomes useful when I'm looking at calculating age of employees. So if I'm about to calculate employee age, for example, this today's date functionality will become useful. So let's look at some calculations here. So we won't now get the age of employees, employees given all these dates of birth. So I've formatted this, this date functionality has been formatted. Now it's critical to recognize that the way your laptop, the date on your laptop is shown determines how the date will show in Excel. So take for example when I use the today's date functionality here. you will notice that it is giving the day before month before year now and it is separating it by a space that's the way my own laptop is configured now for you that you're using maybe an american based laptop or uk based laptop the way data written on your laptop might differ so you need to recognize that the excel functionality of today function only keeps what the date format on your laptop but you could edit the date format for that okay so you can actually change the format of the date by coming here and clicking within the numbers tab group of tabs and it will bring up a dialog box where you have the date this is the functionality at the moment and i can make it give me the day the month in terms of number text and the year and if I do this this is what you see that's exactly what has been done in all of the ones that you have here it's been formatted to show the date in that format now for me to calculate the age in Excel typically if I was doing this normal calculation what I would have done was to pick the year and the year of birth and the year where i want to get the age from with reference to that year and subtract them and gives me an answer but in excel the calculation is a bit different and in excel you start with equals to because it's a formula and all you need to do is to subtract the two dates from each other and it's better you subtract the later date from the earlier date meaning the date of birth subtracted from today's date now you would notice that I have used today's date functionality here, which means if I open this formal document tomorrow to be with reference to today's date and any formula you do with reference to that date in getting the age would always be correct at any time that you open the document. So let's look at it now. So if I do equals to now today's date will subtract date of birth from there. now whatever you see here would be an answer in days but we want to find the date of birth in years so that means that we would need to do a further division of dividing the days because excel will give you the the first when you subtract two dates in excel it will give you the numbers of days between the two dates now this you will now divide by 365. 365. if you do that and you enter it to now give you the nearest it will now convert the days to years this is now 44 and you're now seeing some decimals at the end you need to take those decimals out so usually the best way to do that is to use your integer function now to use the integer function you put the entire formula in brackets such that it will convert the decimal to the nearest whole number and you will notice that before i added the integer function let me take the integer function off before i added the integer function you're seeing it as 44.45 usually when you're dealing with dates we usually round down dates we don't round it up so when i added the integer function what the integer function will give is the nearest whole number which is now 44 years now if i use the autofill functionality you will now notice that it's the formula is replicated for all of it so if this was in a table and then in an employee personnel form form i can as well calculate the entire age of everybody in the organization with one single formula using the autofill function all down and if i now wanted to find the average workforce age now the formula for the average workforce age is given given as the total numbers of each employee's age, you add them up and divide by the total numbers of employees. So that becomes a derived measure and a calculated measure. So if I do all of that and I use the average functionality, what is the numbers? I will pick the cell range and I do enter. You will now see that the average employee age would be 47 in this scenario. Now if I go a little bit further and I wanted to calculate the median median is talking about the median number median is giving us median in Excel and you would take the resale range and you enter that's the average that's the median in itself the average of the salary was given as 4.575 while the median which is the median number is 4 million. If I wanted to get the mode which is the most occurring number in Excel, all I need to do is to use the mode functionality and I like the entire data and it will give me the most occurring number which you will notice from here is 3.5 million per annum. So what is the median annual salary of employees with the following salary?

Now, in order for you to do that, you must get familiar with analytical tools and one very interesting analytical tool that is available to everybody for use is Microsoft Excel. So we're going to be looking at how to use Microsoft Excel for analytical purposes as it related to HR analytics and a very good place to start for me is to look at what we call statistics in HR. So what are the interesting statistics that you get familiar with as an HR professional?

that would be useful for analytical purpose. One of such is base measure. So when we're talking about base measure, we're talking about basic count.

Another very typical example is workforce headcount. That's a measure that you should be familiar with as an HR person. That's a starting point. Number of employees or the headcount, the numbers of hours, hires, how many individuals were hired, new hires, number of exits, how many individuals left the organization, what's the date of birth of your new employees. These are typical data you normally capture in your employee record form.

Now when you now start to use this basic count in calculation, then we'd arrive at what we call a calculated measure. The calculated measure asks to do with. You use zine.

your base count in a formula or in a calculation. So if I have date of birth and I have a date of reference then I can find employee age. If I know the number of exits and the head count then I can use that to get my turnover rate.

If I know the salaries of each employee then I can divide by the total numbers of employees and I can get the average employee salary and as well as the male to female. ratio now all of this will be practicalizing using ms excel which is important for you to have the theoretical understanding so that when we start to do the calculation in excel it's clear what we're trying to achieve Another interesting statistical measure you should get familiar with is attributes. So now we've calculated the employee age and we want to find out the age group or the age band in terms of baby boomers, generation X, millennials and generation Z.

Trying to understand the demographics of the age within the organization, we can do that by using what we call attributes. When you classify certain age together to create an age band you could also do that attribute by using gender or by looking at the experience level of the employees or their department or their qualification whichever becomes applicable now also very interesting application of statistics in excel and even in relation to human resources and hr analytics is your measure of central tendency talking about your mean median and mode which you're going to be using in a couple of minutes to drive home some calculation in hr and it relates into hr analytics because the foundational part of hr analytics is gathering data then starting to analyze the data to draw insight from the data to drive your decision so let's get into excel for some practical session now okay so uh we're in excel at the moment and i'm using office 365 for this illustration so the very first thing I want us to illustrate is the average so let's look at this typical example we have salaries annual salaries of employees in Excel which has been formatted for accounting purposes now what is the average annual salary of employees with the following salaries now to do that in Excel we're going to be using the mean or the average function and that is given by writing average you will see it is already auto completing you double click on it average it asks you what are the numbers you want to find the average since I'm trying to get average annual salary of employees with the following salary all I need to do is to highlight the entire range of data that they the salaries are found and if I close bracket and I enter what you have here is the average salary of these six different salaries so that's very makes it very easy to do in Excel now another interesting part of Excel that you must get familiar with is what we call the today function now Excel are HR professionals are the residents of dates I'm talking about date of birth of employees, talking about date of entry or date they were hired, talking about exit date, retirement date, probation and the rest. So date is very critical. Now one classic Excel function that HR professionals must get familiar with is the TODAY function.

And it is written as TODAY equals to. Anytime you're writing a formula in Excel, it will start with equals to. It starts with TODAY.

And you can already see the today function returns the current date formatted as a date. If I double click on this and I say today function open bracket close bracket and I press enter, it is giving me today's date as at the time of this recording is 18th of October 2020. Now, if I open this document tomorrow, it is still going to return today's date. Now, how does this become useful?

This becomes useful when I'm looking at calculating age of employees. So if I'm about to calculate employee age, for example, this today's date functionality will become useful. So let's look at some calculations here. So we won't now get the age of employees, employees given all these dates of birth. So I've formatted this, this date functionality has been formatted.

Now it's critical to recognize that the way your laptop, the date on your laptop is shown determines how the date will show in Excel. So take for example when I use the today's date functionality here. you will notice that it is giving the day before month before year now and it is separating it by a space that's the way my own laptop is configured now for you that you're using maybe an american based laptop or uk based laptop the way data written on your laptop might differ so you need to recognize that the excel functionality of today function only keeps what the date format on your laptop but you could edit the date format for that okay so you can actually change the format of the date by coming here and clicking within the numbers tab group of tabs and it will bring up a dialog box where you have the date this is the functionality at the moment and i can make it give me the day the month in terms of number text and the year and if I do this this is what you see that's exactly what has been done in all of the ones that you have here it's been formatted to show the date in that format now for me to calculate the age in Excel typically if I was doing this normal calculation what I would have done was to pick the year and the year of birth and the year where i want to get the age from with reference to that year and subtract them and gives me an answer but in excel the calculation is a bit different and in excel you start with equals to because it's a formula and all you need to do is to subtract the two dates from each other and it's better you subtract the later date from the earlier date meaning the date of birth subtracted from today's date now you would notice that I have used today's date functionality here, which means if I open this formal document tomorrow to be with reference to today's date and any formula you do with reference to that date in getting the age would always be correct at any time that you open the document. So let's look at it now.

So if I do equals to now today's date will subtract date of birth from there. now whatever you see here would be an answer in days but we want to find the date of birth in years so that means that we would need to do a further division of dividing the days because excel will give you the the first when you subtract two dates in excel it will give you the numbers of days between the two dates now this you will now divide by 365. 365. if you do that and you enter it to now give you the nearest it will now convert the days to years this is now 44 and you're now seeing some decimals at the end you need to take those decimals out so usually the best way to do that is to use your integer function now to use the integer function you put the entire formula in brackets such that it will convert the decimal to the nearest whole number and you will notice that before i added the integer function let me take the integer function off before i added the integer function you're seeing it as 44.45 usually when you're dealing with dates we usually round down dates we don't round it up so when i added the integer function what the integer function will give is the nearest whole number which is now 44 years now if i use the autofill functionality you will now notice that it's the formula is replicated for all of it so if this was in a table and then in an employee personnel form form i can as well calculate the entire age of everybody in the organization with one single formula using the autofill function all down and if i now wanted to find the average workforce age now the formula for the average workforce age is given given as the total numbers of each employee's age, you add them up and divide by the total numbers of employees. So that becomes a derived measure and a calculated measure.

So if I do all of that and I use the average functionality, what is the numbers? I will pick the cell range and I do enter. You will now see that the average employee age would be 47 in this scenario. Now if I go a little bit further and I wanted to calculate the median median is talking about the median number median is giving us median in Excel and you would take the resale range and you enter that's the average that's the median in itself the average of the salary was given as 4.575 while the median which is the median number is 4 million. If I wanted to get the mode which is the most occurring number in Excel, all I need to do is to use the mode functionality and I like the entire data and it will give me the most occurring number which you will notice from here is 3.5 million per annum.

So what is the median annual salary of employees with the following salary?

Transcript for:Using Excel for HR Analytics

Transcript for:
Using Excel for HR Analytics