# 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.

## 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.

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.

=TODAY()

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

=DATEDIF(<start_date>, TODAY(), "y")

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.