HR Data Analysis in Excel: A Step-by-Step Guide

You are here:
HR Data Analysis in Excel: A Step-by-Step Guide

Microsoft Excel is the market leader when it comes to data analysis, both in HR and other business functions. While it is no substitute for an HR Information System and does not offer the most advanced people analytics capabilities, it is the all-time favorite for quick analysis and data visualization.

Excel provides HR professionals with a dynamic, relatively easy-to-use analysis tool. This article showcases some lesser-known Excel tools and functions that will help you power up your HR data analysis capabilities.

Contents
HR data analysis process
Step 1: Data cleaning
Step 2: Data analysis
Step 3:  Storytelling
Step 4: Dashboarding

HR data analysis process

This article goes through four common steps in any HR data analysis project. 

  1. Cleaning your data
  2. Analyzing your data
  3. Storytelling
  4. Dashboarding

A subset of Kaggle’s IBM HR Analytics Attrition dataset is used for the examples. If you want to put your own Excel skills to the test, the data is available to download. 

Step 1: Data cleaning

Every HR data analysis project starts with data. Sometimes you receive data from a colleague or client. At other times you query the database yourself.

Related (free) resource ahead! Continue reading below ↓

People Analytics Resource Library

Download our list of key HR Analytics resources (90+) that will help you improve your expertise and initiatives. Your one-stop-shop for People Analytics!

If you are lucky, the data is immediately ready for analysis, but more often than not, your data may be contaminated, contains duplicates, inconsistencies, or other errors. So, before you even start analyzing, you always want to check the quality and accuracy of your HR data.

Data cleaning – also called data cleansing or data scrubbing – is often the most laborsome phase of an HR data analysis project.

1. Excel Tables

You might be tempted to start working on your data from the moment you open Excel. However, this could mean missing out on utilizing the great functionality that Excel’s built-in tables offer.

Excel Tables are handy containers for your data. They ensure that the values in a row stay together, which prevents you from accidentally scrambling the data when you sort and filter columns. 

To make a table, click on any cell, then go to Insert > Table and select the range. Or use CTRL + T as a shortcut.

Excel Tables Example

Excel adds out-of-the-box formatting to its tables, as well as advanced filtering and slicing functionality. 

Upskill Become an
HR Business Partner 2.0
Boost your business acumen, data literacy, and analytical skills
with just 40 hours of learning. 100% online & self-paced.
Download Syllabus
Excel formatting

Your work will become much more organized when you use Excel Tables for your analyses. 

2. Remove duplicates

At times, your data tables may contain duplicate information. Maybe a specific employee appears twice in your database due to an administration error. Or, an employee has a dual contract, and therefore two records exist.

If you are working with Excel Tables, the Table Design tab contains a ‘Remove Duplicates’ button. It removes any rows that contain duplicate values on the selected columns. 

Fortunately, there are no duplicate rows in our dataset. 

Removing Duplicate Values Example
No Duplicate Values Found

However, if you were to select the ‘Department’ column only, Excel will remove all duplicates (such as Research and Development) and retain only the first rows in which it finds unique values.

Remove Duplicate Values by Column
Duplicate Values Replaced Example

3. Sorting

Sorting is one of the most common tools of data management. You can sort your table by one or more columns in Excel, in ascending or descending order, or create a custom sort.

In Excel Tables, each column has a sortable filter included in its header. 

Sorting Example

Under Home > Sort & Filter, you find Custom Sort, allowing more advanced, multi-column sorting. 

HR 2025
Competency Assessment

Do you have the competencies needed to remain relevant? Take the 5 minute assessment to find out!

Start Free Assessment
Multi-column Sorting Example
Sort by Multiple Columns

4. Converting data

Sometimes, numeric data is accidentally imported as text in Excel. This causes problems in your analysis, as Excel sorts text data differently. 

Also, many advanced analyses, like correlation analysis, require that you provide numerical data. There are several handy functions to switch between data types in Excel:

VALUE

The VALUE function helps convert numbers stored as text to actual numeric data. Note that Excel, by default, aligns text strings to the left. Numeric data is default right-aligned. This gives you a visual cue as to how Excel interprets your data.

TEXT

The TEXT function helps turn numeric data into text strings in a specific number format. This neat overview shows the many ways in which you can format text numbers in Excel. 

TEXT and VALUE Example

FIXED

The FIXED function also returns numeric data as text, giving full control over formatting options like thousand separators and decimals.

FIXED Function Example

DOLLAR

Finally, the DOLLAR function converts numbers to text and applies currency formatting.

5. Text manipulation

Excel provides many functions that help with “manipulating” text data.

PROPER

UPPER and LOWER help you set the case of your text strings. But have you heard of the PROPER function? 

It capitalizes the first letter in a text string and any other letters in the text that follow any character other than a letter (also known as title case). All other letters are converted to lowercase letters.

PROPER Function Example

LEFT & RIGHT

Sometimes you only need part of a text string, such as the first couple of characters or the last ones. 

In those cases, you can use LEFT and RIGHT, which respectively allows you to extract any number of characters from the start (LEFT) or end (RIGHT) of a text string. 

For instance, maybe you don’t want to use the full gender labels but only the first letter. 

LEFT and RIGHT Functions
LEFT and RIGHT Functions Example 2

SUBSTITUTE

Sometimes you want to replace specific parts of a text. For instance, you might want to rename the “Sales” department to the “Commercial” department. 

In Excel, you can use the SUBSTITUTE function to replace specific parts of a text string with an alternative text. 

SUBSTITUTE Function Example

As you can see, this also changed the Job Role for the “Sales representative” on row 23.

SUBSTITUTE Function Explained Further Example

6. Find and Replace

You might encounter situations where you need to find and/or replace data values in your workbook. For instance, you might be looking for a specific employee, department, or job role. 

You can use the shortcut CTRL + F to open up the FIND menu. This allows you to input the data value you are looking for, and Excel will show you all occurrences of it in your worksheet. 

If you press CTRL + H, this opens the FIND & REPLACE menu. 

This allows you to not only locate the occurrences of data values but also enables you to replace them with another value. You could try to rename the ‘Sales’ department with this approach as well.

CTRL + H Function Example

If you open the advanced options, you can even replace all occurrences of a value across your entire workbook.

Find and Replace on Sheet or Workbook Example

7. Paste special

Another great feature of Excel is the Paste Special functionality. 

Data values in Excel are often formatted in an explicit way — think of typeface, color, or font size. When copying and pasting values, sometimes you want to retain this formatting. At other times, you want to keep only the raw data. And again, in another situation, you might only want the formatting. 

However, you can use Paste Special to instruct Excel to do exactly what you want with the copied data and its formatting. You will find the functionality when you click the right mouse button after you’ve copied any data.

Paste Function Example

For instance, you only want to keep the raw data:

Paste Raw Data Example

Another handy trick is to paste the data transposed, which converts the rows into columns and vice versa:

Paste Data Transposed Example

Did you know you can even paste the raw results with any formulas you have copied? Or you can copy your selection as an image, including the formatting. This great functionality is handy when you want to add an Excel Table to an email or a presentation. 

Step 2: Data analysis

Once you have cleaned your data and are confident in its quality, you are ready to start analyzing your data.

First, you might want to calculate descriptive analytics that summarizes your data to assist in uncovering insightful patterns. These insights can help to make important decisions on how to improve your HR processes and policies as well as evaluate their business impact.

Excel offers a lot of functions for data analysis. PivotTables are one such function that can really take your data analysis capabilities to the next level. If you want to find out more about how to use PivotTables, AIHR has provided step-by-step instructions on how to work with PivotTables.

Below, let’s unpack some additional tools and functions you can use in Excel and pivot tables to help you with your data analysis: 

1. Slicers and Filters

Slicers are clickable buttons that filter your Excel PivotTables. You can add a slicer by selecting any cell in a table, then going to Table Design > Insert Slicer.

Slices and Filters Function Example

Excel will prompt you to select one or more columns that you would like to create a slicer for. Let’s select ‘Job Role’.

Create a Slicer Example

The created slicer will provide a visual tool to quickly slice and filter your data table. 

Slicer Visual Tool Example

2. IF THEN

Often in HR data analysis projects, you want to insert your business logic into the data. This is where IF THEN statements can be very useful.

IF

The IF function can be used to test a logical statement, and produce a data value based on its result. 

For example, employees in your company who have been working for over 4 years in your company are classified as tenured. This information is contained in the values YearsAtCompany column, but it is not explicit yet. It’s not easy to filter or slice for that tenured status currently.

You could use Excel’s IF function to create a new column containing this information, which you can then insert into a slicer for quick filtering and analysis. 

Excel IF Function Example

COUNTIF & SUMIF

This IF-THEN logic can be expanded for more advanced analyses, and this is where the COUNTIF and SUMIF functions come in handy. 

The COUNTIF function allows you to count all records in a selection that meet a certain condition. For example, you could count the number of tenured employees.

The SUMIF function allows you to summate all values in a selection that meet a certain condition. For example, you could calculate the total monthly salary for tenured employees.

COUNTIF Function Example

This logic extends to the COUNTIFS and SUMIFS functions, which can count and sum values for records that meet multiple conditions, like employees being tenured and working as a director. 

3. PivotTable

PivotTables is a power tool for any analyst using Excel. PivotTables allow you to skip a lot of the manual programming and focus on the data analysis. 

If your data is in an Excel Table already, it’s easy to create a PivotTable. Select any data cell in your table and then Table Design > Summarize with PivotTable.

PivotTable Example

Once you press OK, an empty PivotTable is created on a new worksheet. 

Create a PiviotTable Example

In the PivotTable Fields menu, you can now drag your columns to the respective boxes to create the summary table you need for your analysis.

Suppose you want to analyze the overall salary spend per department. By setting the ‘Department’ column as the row and the ‘MonthlyIncome’ as the values, Excel automatically populates the PivotTable with the results you need for your analysis. 

PivotTable Fields Example
Creating a PivotTable Example 2

This prevents you from having to input this calculation manually by programming three SUMIF functions. 

The COUNTIF logic can be implemented by adding the ‘MonthlyIncome’ to the ‘Values’ box, and then selecting the drop-down arrow to change the Value Field Settings. 

Implementing COUNTIF in PivotTable Example

This then opens up a menu where you can substitute the SUM logic, for the COUNT logic. Now you can view the size of each department.

Substitute SUM for COUNT Example
Substitute SUM for COUNT Example 2

Similarly, you can use PivotTables to quickly calculate other group-level summary statistics, like the average value, or the minimum or the maximum value per group.

PivotTables offer great versatility, allowing you to create complex, customizable reports in a matter of minutes. 

For instance, below illustrates a comparison of the monthly salaries between the ‘HR’ and ‘Sales’ departments for different ‘Job Levels’. The table shows that salary policies seem to be similar across departments, though the minimum salaries are consistently lower in ‘Sales’. 

PivotTable Provide Comparisons Example

Step 3:  Storytelling

Showing data and presenting it in neat tables will only get you so far when it comes to influencing decisions. Your insights can have a greater impact when you use storytelling and data visualization. 

Storytelling is the art of using words and actions to reveal the elements and images of a story while encouraging the listener’s imagination. Data visualization refers to representing information (i.e., data) in the form of a chart or diagram for enhanced interpretation. 

Try to tell the story of how the problem you examined influences the daily experience of employees and the bottom-line performance of the business. Show visually what the problem is and what impact your proposed solution could have. 

This will assist your decision-makers in quickly understanding the problem and assessing the right approach and solution. 

1. Start with the audience

An analysis is meaningless if your target audience doesn’t understand its insights. Using storytelling and data visualization can help your audience process your insights quickly and simply. 

Consider how familiar your audience is with the concepts being represented and whether they have the technical capacity to interpret any visualizations you showcase. 

2. Pick the right chart

In Excel, you can find data visualization options under Insert > Charts

Charts Example

There are many different ways to visualize your data. Excel can create pie charts, bar charts, line graphs, scatterplots, waterfalls, and many others. Each of these charts works best for a certain type of data and a specific audience.

It’s good practice to start with your main message and choose the chart that best communicates that message to your audience. 

Want to show change over time? Use a line graph! 

Line Graph Example

Want to show the proportion of a group to the whole? Use a pie chart! 

Pie Chart Example

Want to compare multiple groups? Use a bar chart or column chart!

Bar Chart Example

Need some tips for choosing the right chart? Use a chart picker like this one:

3. Color

Color can be an extremely powerful asset for your storytelling. For instance, you can use color to highlight a specific data point that is central to your insights.

In Excel, this is as simple as double-clicking a data point, navigating to the paint bucket, and changing the color.

Using Color in Graphs Example

Doing so can visually alter the story your chart conveys. 

Bringing Charts to Life with Color Example


In general, it’s good practice to use color sparingly and functionally to aid insight and storytelling.

Step 4: Dashboarding

Dashboards combine charts, tables, and KPIs in a useful overview to aid decision-making. Dashboards can be static or interactive – where users can select or change the way data is displayed.

Before you start building a dashboard, it’s often best to start with some questions. 

  • What is the main decision process that the dashboard is used in? 
  • What decisions are made? 
  • And what information is needed to determine the right course of action?
  • Is this a one-off information need, or does the dashboard need updating on a regular basis? 
  • Are insights needed on a company-level, division-wise, or for other subgroups?

Knowing the exact purpose and requirements of the dashboard helps you build the tool that has the largest impact on your business. 

Shortcuts and skill-building tools

Building a dashboard can be quite complex. However, AIHR offers a comprehensive course on metrics and dashboards to help you learn more about these important elements of your analysis. 

Looking for a quick win? AIHR has built a Resource Library that offers many tools for you to use, such as a headcount dashboard and handy downloadable Excel tools that save you from doing much of the work outlined in this article!

AIHR's Head Count Dashboard Tool

Want to further improve your analysis skills? AIHR offers an in-depth People Analytics Certificate Program to help you develop your analytics skill set. 

To conclude

Excel provides a powerful tool for HR to use when performing data analysis. This article discusses some easy-to-use tools that can help you to begin to see patterns and develop meaningful insights into your data. We encourage you to download the dataset and to practice these various tools and functions so that you can develop insights into your HR data that can inform better people and business decisions.

Subscribe to our weekly newsletter to stay up-to-date with the latest HR news, trends, and resources.

Are you ready for the future of HR?

Learn modern and relevant HR skills, online

Browse courses Enroll now