HR Analytics with Excel – Compensation Quartiles


In today’s fast-paced business landscape, making informed decisions is crucial for success. When it comes to Human Resources or Compensation data – it’s even more important that you have the right data and analysis to back up decisions.  

In the next series of videos, I’m walking through some examples of how we can use excel to drive smarter, more strategic HR practices and equip you with insights to bring your data analysis skills to the next level.  

1. How to Calculate Quartiles for Salary Ranges

Understanding how to divide your salary ranges into quartiles can be an effective way to identify pay distribution patterns, pinpoint potential disparities, and discover areas where your organization excels or needs improvement. In this first video, we’ll go over the basics of quartiles in compensation analysis:

2. How to Calculate Percentile Rank within a Range

In this video, we’ll review how to assign each employee to a quartile, by using the PercentRank formula in Excel. Understanding how an employee’s pay ranks within their salary range is valuable information. For HR professionals like you, the challenge becomes how to review all employees at once when there are so many that it’s hard to keep track of. Quartiles can help us slot employees into categories (the 4 quartiles) and review the distribution of employee pay, but stay tuned for the next videos below, where we’ll show how to visually represent this information, in charts!


Now that you have calculated quartiles and percentiles, how can this data be used to support effect data based insights and decisions?  In the videos below, I’ll go through how you can turn this type of data into visually appealing stacked bar charts and/or scatter charts.  


3. Data Visualization with Stacked Bar Charts in Excel

When you’re faced with too much data, it’s true that breaking down complex information into simpler parts can make all the difference. That’s exactly what a stacked bar chart can do – transform extensive data into a clear and easily understandable visual representation. There’s just a couple more steps to summarizing our quartile data, before we can get the chart how we want it, but be sure to follow along here:

4. Data Visualization with Scattered Charts in Excel

With scatter charts you can break down complex information in a way that makes it fun and easy to understand. Scattered charts can take all data points and plot them in 1 visual chart. In this example, we’re comparing the employee’s quartiles to their performance ratings. This can be an easy way to quickly check for trends – for example are all high performers paid in the top quartiles? Or are there many high performers in lower quartiles?

Get a FREE Comp Metric Cheatsheet here: