Using pivot tables in Excel: HR Analytics in Practice

Microsoft Excel is an extraordinary powerful tool used across the globe for a wide range of data analytics work and is considered best and low cost for quickly building a wide variety of highly specialized time-saving workflow tools. Excel has several features that perform quick analysis and create data summaries to generalize trends like Pivot tables, conditional formatting, data formatting, filtering, built-in formulae (sum, count, sumif, countif etc), conditional and logical functions, charting, visual basic macros.

In this course we will learn about one of the most powerful Excel feature that allows you to create quick summaries, sort, reorganize, group, count, total or average data stored in a database. You can reorganize columns into rows and rows into columns and create a lot of data analysis tables that can be incredibly useful in deriving useful and actionable insights from your data.

HR analytics is an exciting new field, it allows HR professionals to harness the power of the data that they have access to. While this course is targeted at the HR Analyst, it will be equally useful for you whether you are an HR professional, student or working in any field that requires decision making.

Below are few examples a company or a user might use Excel for and these are just examples, excel is very flexible and robust so the uses are endless:

Determine employee headcount at group level like location, business unit, department, project, function, job type, job family, skill set;

Run employee attrition or turnover analysis by using exits report from HRIS;

Explore different reasons of employee exits and identify top and bottom reasons and assist HR leaders in policy design;

Calculate rewards and recognition budget and its allocation at group level; Keeping track of sales by employees from month to month or quarter to quarter;

Monitoring employee billing sheets to ensure that none are late;

Keeping track of employee engagement activities and generate department level summaries;

Calculating hours worked per employee for monthly payroll;

Calculating benefits premiums from month on month monthly;

Creating department-wise or location-wise summaries on employee costs vs revenue generated;

Calculate employee productivity at sub-granular level and generate reports for business heads;

Assign projects and tasks to employees in a centralized location and analyse the output at group level.

Use our course to quickly make sense of large tables of data and make impressive summary statistics that make sense out of chaos.

In this course we will take you step by step through the process of creating simple pivot tables and then slowly add layers and complexity to your summaries. You should be able to follow along with us by downloading the dataset that is available with this course.

By the end of this course, you would be comfortable with this feature in Excel, and should be able to apply your knowledge to real life datasets that you have access to.

Of course, we are always here to help you with your queries, so don't hesitate to reach out to us in the QnA section!





Happy Learning!



