Excel Hacks for HR Professionals

Small businesses use Excel for a variety of HR admin such as managing timesheets, employee absences, managing payroll, or defining contractor relationships. It makes sense for a new business to utilise excel to its maximum potential especially if the business doesn’t have time or budget to research and purchase a recruitment software solution.

With this in mind, we have put together the top Excel hacks for HR professionals to simplify your HR processes and save you hours in admin time. You can download our Excel Hacks for HR professionals Excel Template here, that includes all the examples and formulas referenced in this article.

Top 8 Excel Hacks for HR Professionals

The following is a list of the most important Excel features most-used by HR professionals that will help cut your admin time from hours to minutes. Follow the instructions detailed below for guidance on how to use each one.

‍

List of Top Excel hacks for HR professionals:

#1: Filtering data

#2: SUMIFS Formulas

#3: COUNTIFS Formulas

#4: TODAY Formulas

#5: DATEDIF Formulas

#6: Data Validation

#7: VLOOKUP

#8: PivotTables

#1. Filtering Data

One of the most common HR tasks is to have the ability to filter down employee details in an employee database. You may wish to filter employees by start date, salary or department depending on your needs.

Sample use cases for filtering:

• Tracking attendance in an employee attendance report

• Tracking employees by department

• New employees onboarding report

• Completion status of performance appraisal

• Filtering employees with a salary of €4,500 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, rename the table name as "Emp".

Step 3: Click on the arrow next to "Salary", go to the Number Filter > Greater Than Or Equal To > Enter 4,500 as the criteria and click OK.

Step 4: You will see only the employees that do not match the criteria will be filtered out.

Step 5: To clear filter, simply click anywhere in the Excel Table. Go to Data > Sort & Filter > Clear.

‍

#2. SUMIF Function

While the COUNTIF function (detailed in #3 below) counts if a criteria is satisfied, the SUMIF function adds values if certain criteria is met. A sample use case is detailed below, calculating the total salary expense of each department in a company.

=SUMIF(<range>, <criteria>, <range_to_sum>)

#3. COUNTIF Function

The COUNTIF function counts if a criteria is satisfied.

=COUNTIF(<range>, <criteria>)

In this examples we have a table of employee data, with the columns — Name, Department, and Salary.

‍

Use Case: New hire count

Step 1: Convert your employee data to an Excel table (See example outlined 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 F2 to the other departments.

#4. TODAY Function

HR professionals are often required to calculate date ranges with reference to today's date. An example of this in practice could be calculating the age of employee and their length of service in the company.

The TODAY function will adjust itself automatically so that you will always have today's date in formulas.

=TODAY()

#5 DATEDIF Function

The DATEDIF function in Excel gives the number of days, months, or years between two different dates.

=DATEDIF(<start_date>, <end_date>, unit)

We have a few options for unit:

• “y” – number of completed years

• “m” – number of completed months

• “d” – number of days

• “ym” – after ignoring years, the number of completed months

• “yd” – after ignoring years, the number of days

Use Case: Calculating an employee's age or length of service

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

#6 Data Validation

The Data Validation feature in Excel 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 pop-up, under Allow choose List from the drop down.

Step 3: Under Source, select the range where the user can pick the names 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 a certain employee works in

The column_num_in_table  is 2 in the example as we want the second column of the table — "Dept".

#8 Pivot Tables

Most basic excel users are not aware of the power of the pivot tables feature. Pivot tables once learned, are a great feature that enable users to extract raw data and organise it in tabular form. For example in the HR context, it is common practice to search through a table to match employee names. A simple use case for HR professionals using pivot tables could be to examine salary expense and employee count for each department. PivotTables are also a go-to for creating quick and easy hiring reports for management.

‍

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 to place the report.

Step 3: Do the following in the PivotTable Fields  window:

1. Drag "Dept" to Row Labels

2. Drag "Name" to Values

3. Drag "Salary" to Values

‍

Now that you are well-equipped with the Excel features for your HR related tasks, you will be able to save hours of your precious time on a daily basis! Remember to download our Excel Hacks for HR professionals Excel Template here, that includes all the real use case examples and formulas listed in this article.

How a Recruitment Software Can help save you more time

Although Excel is a great entry level solution for companies to manage their hiring, as a company grows it is a significant time drain for HR. Hiring information can easily get lost or misinterpreted when spread over many different tools such as emails or spreadsheets.

Occupop is a beautifully simple recruitment software that helps your HR team focus on the work that matters with its intuitive design, features, and integrations which remove cumbersome learning curves, makes it accessible to everyone at work and saves hours of tedious HR admin work. If you think Occupop could be a good small business recruitment software solution for you, feel free to try it out with a free 14-day trial.

INSERT-CTA