Excel for HR Professionals in 2022:
8 excel features that will cut your work from hours to minutes
In her book, Vault Guide to Human Resources Careers, Susan Strayer describes HR as “the glue that holds people and an organization together”.
It is so common to be overwhelmed by the number of Excel related tasks you need to do on a daily basis — e.g. employee attendance tracking, data analysis, and key performance indicator.
So to help you in your work, I've put together a bunch of useful Excel functions, HR use cases, and a downloadable Excel spreadsheet of the examples mentioned.
HR meets Excel
- Filtering data
- TODAY
- DATEDIF
- COUNTIF
- SUMIF
- VLOOKUP
- PivotTables
1. Filtering Data
The most common HR task is to drill down and filter an employee database.
Use Case: Find the employees with salary $3000 or more.
Step 1: Highlight the data and hit Ctrl + T to format as an Excel table.
Step 2: Under {Table Tools} Design | Properties | Table Name, re name the table name as "Emp".
Step 3: Click on the arrow next to "Salary", go to Number Filters | Greater Than Or Equal To. Enter 3000 as criteria and press OK.
Step 4: You can see that only those employees that do not match the criteria will be filtered out.
Step 5: To clear filter, simply click on anyway in the Excel Table. Go to Data | Sort & Filter | Clear.
Tip: Filtering can be used in other HR areas such as:
- Tracking time and attendance in an employee attendance report
- Active employees report
- New employees onboarding report
- Completion status of performance appraisal
2. TODAY function
Often, Human Resource professionals are required to calculate dates with reference to today's date, such as calculating the age of employee and length of service.
The TODAY function recalculates itself so that you will always have today's date in formulas.
If you are using many TODAY functions in your spreadsheet however, you may experience performance issues. If so, it is best recommended to use the TODAY function once, and have other cells refer to that cell.
3. DATEDIF function
The DATEDIF function calculates the number of days, months, or years between 2 dates.
=DATEDIF(<start_date>, <end_date>, unit)
We have a few options for unit:
- “y” – no. of completed years
- “m” – no. of completed months
- “d” – no. of days
- “ym” – after ignoring years, the no. of completed months
- “yd” – after ignoring years, the no. of days
Use Case: Calculate employee's age
=DATEDIF(<start_date>, TODAY(), "y")
Use Case: Calculate length of service
For more information on the DATEDIF function, refer to this explanation by Microsoft.
4. COUNTIF function
The COUNTIF function counts if a criteria is satisfied.
=COUNTIF(<range>, <criteria>)Supposed we have a table of employee data, with the columns — Name, Department, and Salary.
Use Case: New hire count
Step 1: Convert employee data set to an Excel table (See example in "Filtering Data").
Step 2: To count the number of employees in the Finance department, we can use:
Step 3: Copy the formula in cell E2 to the other departments.
5. SUMIF function
While COUNTIF function counts if a criteria is satisfied, the SUMIF function adds if the criteria is met.
=SUMIF(<range>, <criteria>, <range_to_sum>)
Use Case: Calculating the total salary expense of each department
6. Data Validation
The Data Validation feature is used to create a dropdown list.
Step 1: Highlight the cell to be the dropdown list, and go to Data | Data Tools | Data Validation.
Step 2: In the Data Validation window, under Allow choose List.
Step 3: Under Source, select the range where the user can pick the labels from.
7. VLOOKUP function
The VLOOKUP function allows us to create formulas that match some text.
For example, it is very common to search through a table to match employee names.
=VLOOKUP(<lookup_value>, <table>, <column_num_in_table>, FALSE)
Use Case: Finding the department of an employee
The column_num_in_table is 2 for the example above as we want the second column of the table — "Dept".
8. Pivottables
PivotTables is a quick and easy way to create simple reports.
For example, it is very common to search through a table to match employee names.
Use Case: Recreate the total salary expense and employee count for each department
Step 1: Highlight the table and go to Insert | Tables | PivotTables.
Step 2: Click on Existing Worksheet, and click on the cell where you want the report to be.
Step 3: In the PivotTable Fields window, do the following:
- Drag "Dept" to Row Labels
- Drag "Name" to Values
- Drag "Salary" to Values
Conclusion
Now that you are well-equipped with the Excel features for your HR related tasks, I'm sure you will be able to save hours of your precious time on a daily basis!
If you like this article, then check out the Defeat Excel handbook below. It contains clear and concise solutions to your most frequently asked questions. No more struggling, no more Googling.
Also check out how to perform calculations with dates and see Excel handles dates behind the scenes.
Get The Defeat Excel Handbook Today!
50+ Frequently asked how-tos
No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.
PDF Format
Easily search through the book to find the solution you need. Plus it works offline too.
Effective solutions
No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.