Data Analytics – The Human Resource Approach – Part 2/2 [Free Templates]


Measuring employee turnover doesn’t have to be a nuisance. This post provides you with two free templates and a short tutorial on how to measure a few aspects associated with employee turnover.


In Part 1 of this 2-part series we had a brief discussion about the definition and application of Data Analytics, especially in HR.

This is the final installment of the 2-part series on basic Data Analytics in HR.

If you’ve not had the chance to read the 1st installment, please feel free to read it before we continue (it’s not mandatory).

As promised in the 1st installment, the theory portion is now done and dusted. In this post, we will only focus on the practical application of a basic data analytics.

The analytics performed in the template provided, is not of an advanced nature and forms a very good basis for the practical application of future data analytics.

You will be provided with a decent amount of information (within the template) to practice analytical techniques, not mentioned in this post. So feel free to play around.

In this post, we won’t be utilizing over the top statistical formulas that have the potential to fry your computer’s CPU (that’s a topic for another post).

For now, we will focus on:

  • Data Collection;
  • Basic Data Analysis/Analytics; &
  • Data Visualization.

To follow along, you might want to pop over to my tutorial on the “=countif” function in Excel (not mandatory).

You can download the free templates provided with this post here:

The spreadsheet has been pre-populated with data, but you are more than welcome to delete the data and enter your own.


Before we jump in:

  • All information contained in the Excel template/workbook is purely fictional. Should there be any resemblance to anyone you may or may not know – within this template – it is purely coincidental.
  • The template may only be used for educational purposes and I accept no responsibility or liability for any losses incurred as result of using this template.


What Process will we follow?

That’s a very good question 🙂

Before we kick off our process, we first have to establish what it is that we aim to measure/analyze:

We aim analyze the reasons behind turnover in our fictional Company

Remember, it’s very important to establish the purpose of your analysis before you begin. This prevents you from spending hours and hours on collecting the incorrect data.

Now that we’ve established the purpose of initiative, let’s have a look at our 3-step process.

Our process consists of three basic steps:

  1. Data Collection: With the assistance of our Exit Interview Questionnaire;
  2. Data Analysis: With the assistance of our Turnover Analytics workbook; &
  3. Data Visualization: Also with the assistance of our Turnover Analytics workbook.

In simpler terms:


Step 1: Data Collection

The Exit Interview Questionnaire

Our data collection process starts when an employee is terminated (and no, not like the “I’ll be back” terminated).

We collect information from the employee through an Exit Interview Questionnaire.

The Exit Interview Questionnaire (EIQ) measures 5 Dimensions and is “linked” to our Turnover Analytics Workbook (via Dimension Codes – A1, A2, A3, etc).

The five dimensions being measured in our EIQ are as follows:

  1. Dimension A: Direct Leadership
  2. Dimension B: Teamwork and Collaboration
  3. Dimension C: Career and/or Job Satisfaction
  4. Dimension D: Company Values
  5. Dimension E: Reason for Leaving (technically not a dimension)

Each category contains a set of questions that the employee needs to respond, by making use of a rating scale.

Let’s look at Dimension A as an example:

  1. Dimension A aims to measure the influence of the employee’s direct leadership/line manager, on the employee’s decision to leave the organization.
  2. The employee is asked as set of questions, relating to this dimension.
  3. The employee is then required to indicate his/her response by either making a “X” or a “check-mark” in the appropriate block.
  4. The Dim. Code column contains the code that “links” our Turnover Analytics workbook to our EIQ (more on this later).
  5. As mentioned in my previous post, we will only gather Quantitative (yes/no/numbers based) data and not Qualitative (open ended date) data during our exercise.

I would strongly recommend that, if you develop a questionnaire/survey of this nature, to make provision for Qualitative data. Qualitative data could form the basis for future Quantitative data collection, and as such, is very important.

Data Capturing/Data Storage

Assuming we’ve interviewed several employees, over several months, or even years, we should have quite a bit of information at our disposal.

Our Turnover Analytics workbook contains 3 sheets

  1. Input_Turnover_Data: This sheets is used to capture the data from our EIQ.
  2. Display_Turnover_Data: Contains the basic analytics that we applied to our data and forms the basis for visualizing our data.
  3. Data_Lists: Doesn’t do much other than storing values for drop-down lists contained in our workbook (don’t delete it).

Input_Turnover_Data (sheet)

Our input sheet makes provision for a few basic areas.

  1. The unique “Response ID” assigned to each employee that completes a questionnaire (read more about this in the EIQ);
  2. Basic information pertaining to the employee;
  3. Columns containing the Dim. Codes that correlate with the EIQ Dim. Codes; &
  4. Input area for the values associated with the EIQ (see below)
  1. In Dimension A, Question A1 (dim. code) the employee indicate that he/she feels that his/her direct line manager “always” demonstrated fair and equitable treatment by making an “X” under “Always” (score of 3)
  2. In Dimension A, Question A2 (dim. code) the employee indicate that he/she feels that his/her direct line manager “sometimes” provide the employee with ample training to do his/her job (score of 2)
  3. The score associated with the employee’s response is captured on the spreadsheet. If the response value is between “1″ and “3” (both numbers included) the cell will turn light green, indicating that the value is acceptable – With the exception of Dimension E, which can only accept a value of “0” or “1”.
  4. If the captured value is higher than “3”, the cell will turn light red to indicate that the value is not acceptable.

Before we continue to our next step, make sure that the Input_Turnover_Data (sheet) contains some data. (If you downloaded the template it should already contain data).


Step 2: Data Analysis

This is the fun part.


Once you completed the “Input_Turnover_Data”, the “Display_Turnover_Data” sheet will automatically generate the required values.

The “=countif” function and “named ranges” is used quite extensively in this sheet.

For more on “=countif” and “named ranges” you are welcome to check out this article: Dashboard Automation

Let’s have a look at Dimension A as an Example:

  1. Our first step was to conduct the exit interview, by using the Exit Interview Questionnaire (EIQ).
  2. We then captured the responses under each dimension in the “Input_Turnover_Data” sheet.
  3. The values (associated with each response) were counted by Excel (via =countif function) and summarized in the “Display_Turnover_Data” sheet.

If you have a look at dimension “A1” (bottom left) you will see that two respondents indicated that their line manager “always” demonstrated fair and equitable treatment. Hence the associated value to their response is “3” (in the Input_Turnover_Data sheet).

On the “Display_Turnover_Data” sheet, inline with dimension “A1”, you will note that 2 respondents gave the same response. Excel therefore counts the amount of “3” responses, which in this case is “2”.

Below is an extract of the formula used by Excel:


In other words:

If dimension A1 on the input sheet is = "Always" from the output sheet, 
then count the number of "always" responses in the input sheet, 
and display them in the output sheet.

As simple as that 🙂

As long as you complete the input data sheet (in accordance with the Exit Interview Questionnaire), Excel will manage the rest.

Because we have access to our raw data (Input_Turnover_Data), we can also summarize our data by means of PivotTables instead of using the “=countif” function. Why not give it a go?

Once we’ve completed Step 3, you’ll also be able to analyse your data a little better.


Step 3: Data Visualization

With Data Visualization, we aim to make our data a little easier to read and understand. This way we can share our findings with a larger audience, without them having to sift through countless amounts of either raw or summarized data.

In this workbook we’ve made use of basic 3-D Clustered Column charts to visualize our data.

You can find the 3D- Clustered Column charts at the bottom of the Display_Turnover_Data sheet.


How to insert a 3-D Clustered Column Chart

  1. Select the data you wish to “visualize” (in this example we are using Dimension B in the Display_Turnover_Data sheet)
  2. Click on “Insert” on the ribbon
  3. Click the “Insert Column or Bar chart” button on the ribbon/
  4. Click the “3-D Clustered Column Chart” option and you’re done.
  5. You should see the graph generate in the background as you however over the various buttons

You can now go ahead and resize your graph, change the styling, add axis titles, etc.


I hope this post was both informative and educational.

I would love to hear your comments and suggestions in the comment section below or via InMail.

Until next time…

Leave a Reply

Your email address will not be published.