Excel for HR Professionals in 2020:
8 excel features that will cut your work from hours to minutes

people

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.

Hands-ON

Get the accompany Excel spreadsheet to follow the examples in this article.

HR meets Excel

Here are the most important Excel features that are beneficial to your data analysis:
  • Filtering data
  • TODAY
  • DATEDIF
  • COUNTIF
  • SUMIF
  • VLOOKUP
  • PivotTables
There is a Reddit thread posted in 2016 discussing the most common Excel tasks related to the HR field that is still relevant today, especially on the use cases of Excel in the Human Resource industry.

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.

create-table

Step 2: Under {Table Tools} Design | Properties | Table Name, rename the table name as "Emp".

rename-table

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.

filter-greater-equal-than

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.

clear-filters


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.

=TODAY()
today-function


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")
calculate-age-this-year


Use Case: Calculate length of service

=DATEDIF(<start_date>, TODAY(), "y")
calculate-service-years


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:

countif-function

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

sumif-function

6. Data Validation

The Data Validation feature is used to create a dropdown list.

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

vlookup

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.

insert-pivottable

Step 2: Click on Existing Worksheet, and click on the cell where you want the report to be.

create-pivottable

Step 3: In the PivotTable Fields window, do the following:

  • Drag "Dept" to Row Labels
  • Drag "Name" to Values
  • Drag "Salary" to Values
pivottable-report

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.

Hands-ON

Get the accompany Excel spreadsheet to follow the examples in this article.

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.