Bisht frnp Adam

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.

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:

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.

‍

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(, , )

The COUNTIF function counts if a criteria is satisfied.

=COUNTIF(, )

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

‍

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.

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()

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

=DATEDIF(, , 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(, TODAY(), "y")

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.

‍

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(,

import React, { useState } from 'react';

import { useLazyQuery } from '@apollo/client';

function DelayedQuery() {

  const [dog, setDog] = useState(null);

  const [getDog, { loading, data }] = useLazyQuery(GET_DOG_PHOTO);

  if (loading) return <p>Loading ...</p>;

  if (data && data.dog) {

    setDog(data.dog);

  }

  return (

    <div>

      {dog && <img src={dog.displayImage} />}

      <button onClick={() => getDog({ variables: { breed: 'bulldog' } })}>

        Click me!

      </button>

    </div>

  );

}