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.
- Cleaning your data
- Analyzing your data
- Storytelling
- 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.
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 adds out-of-the-box formatting to its tables, as well as advanced filtering and slicing functionality.
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.
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.
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.
Under Home > Sort & Filter, you find Custom Sort, allowing more advanced, multi-column sorting.
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.
FIXED
The FIXED function also returns numeric data as text, giving full control over formatting options like thousand separators and decimals.
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.
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.
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.
As you can see, this also changed the Job Role for the “Sales representative” on row 23.
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.
If you open the advanced options, you can even replace all occurrences of a value across your entire workbook.
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.
For instance, you only want to keep the raw data:
Another handy trick is to paste the data transposed, which converts the rows into columns and vice versa:
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.
Excel will prompt you to select one or more columns that you would like to create a slicer for. Let’s select ‘Job Role’.
The created slicer will provide a visual tool to quickly slice and filter your data table.
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.
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.
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.
Once you press OK, an empty PivotTable is created on a new worksheet.
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.
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.
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.
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’.
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
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!
Want to show the proportion of a group to the whole? Use a pie chart!
Want to compare multiple groups? Use a bar chart or column chart!
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.
Doing so can visually alter the story your chart conveys.
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!
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.
Weekly update
Stay up-to-date with the latest news, trends, and resources in HR
Learn more
Related articles
Are you ready for the future of HR?
Learn modern and relevant HR skills, online
