How to excel in human resource management?
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(
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(
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( 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". 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. Some particular Excel functions can help you and the rest of HR accomplish data analysis more quickly. Here are a few to know. Filtering data allows you to temporarily hide rows of data that don't meet your criteria, greatly reducing the amount of data displayed in a spreadsheet. You may want to filter data if many of your company's employees work in different countries and you only want to view American employees' data. You can easily filter out all other countries if the data set includes a country column. Here's how to use the filter feature. First highlight all the columns with data or select the whole spreadsheet. You can select all the columns in the spreadsheet by clicking inside the area highlighted in green, or you can select only the columns with data, which are highlighted in blue. Next, enable the Filter feature by selecting the Data menu, then Filter from the Sort and Filter section of the Ribbon. Once you've enabled the Filter feature, a down arrow icon appears next to each entry in the first row of data. Click on the down arrow in the Country column and deselect all countries except United States. Doing so will hide the rows where the country is anything but United States. Click OK. You can also use this feature to perform other actions like sorting your data. The data validation feature is most useful when you want to create a picklist to ensure that any other employees using your spreadsheet only enter valid data in a consistent format. For example, you may have a column in your spreadsheet for the countries in which your organization has offices. You can create a picklist to ensure everyone enters the country name the same way. Otherwise, you may end up with a variety of data entries that have the same meaning but are written differently, such as US, USA and United States. These data variations could make data analysis unnecessarily complex. To use the Data Validation feature, select Data from the menu, then pick Data Validation from the Data Tools section. A small window will open. In the window's Allow section, select List. In the Source section, you can either enter a list of values to allow, or you can reference a collection of cells that contains the series of items to display in your list. To reference a range, click the up arrow to the right of the Source field and then select the cell range. Then press Enter to accept the range. The example in Figure 6 displays a hardcoded list of countries. When another user clicks on a cell that contains a picklist, a down arrow will appear next to the field. If they click on the down arrow, they will see a list of valid entries and will only be able to select those entries. You can use PivotTables to quickly analyze data. The tool allows you to drag and drop column names and use predefined functions to perform mathematical computations. HR staff might use PivotTables to compute salaries by country or department or break data down by country and department, among other uses. To set up a PivotTable, all your columns must have a column header, such as Country, Salary or First Name. If one of the column headers is blank, an error will appear. Figure 7 shows an example of salary and country data. To use the PivotTable feature, follow the steps below. 1. Select Insert from the menu, then click on PivotTable in the Tables section of the Ribbon. The window pictured in Figure 8 will then appear. 2. In the Table/Range section, select the data you want to use in your PivotTable. You can select whole columns or highlight the specific section of data you want to analyze. For example, you could select the range A1:F11 for the data pictured in Figure 7. 3. In the Choose where you want the PivotTable to be placed section, indicate where you want to place your PivotTable. The best practice is to put it on its own sheet, since the PivotTable will grow and shrink based on the settings you entered and may overwrite data if you place it near your source data. 4. Next, select how you want to analyze your data. The PivotTable Fields section should display automatically as soon as you click in the PivotTable. If you don't see it, right-click on the PivotTable and select Show Field List. Figure 9 displays the PivotTable Fields window. 5. Section 1 displays the columns of data you can use based on your selection above. You will drag and drop some or all the column headings into one of the four boxes below, which are items 2 through 5. 6. If you want to filter the data, you can drag a column heading into section 2. For example, if you only want to display data for the United States, you could drag and drop the column heading Country into section 2. The filter section will appear at the top of your PivotTable, and you can select United States. 7. For section 3, you can specify what data you want displayed in each PivotTable column. For example, you may want to place the label Department in this section so each department displays across the top of your PivotTable. 8. For section 4, determine what data you want to display on each row. You may want to place Departments in section 4 instead of section 3 so the departments are placed on each row of the table instead of in separate columns. 9. You use section 5 to perform a mathematical equation with your data. For example, you may want to drag and drop the Salary column heading into this section so you can see the total of every employee's salary. You can also use other mathematical functions in this column. For example, you may choose Count to count the number of employees. Figure 10 shows an example of how you might format your PivotTable to begin analyzing the data.,
Related Questions
More Questions
