Microsoft Excel isn’t a substitute for your core HR management system or advanced people analytics, but as a day-to-day tracking and ad hoc reporting tool for HR, it is hard to beat. Using human resources formulas and functions in Excel to answer questions and make better decisions will help you along your road to becoming a data-driven HR professional. It will also help you understand what your embedded analytics tools tell you.
Using Excel is easier, faster, and cheaper than relying on IT for reporting, and chances are your HR team has one or more people who are at least somewhat knowledgeable about it.
However, most users’ skills don’t go beyond simple tracking and using reports created by others. We have found that being the “Excel wizard” in HR can positively affect your employability and income.
Microsoft’s extensive documentation is written for everyday users. Excellent training resources and dozens of excellent books make it easy to learn—and for us, the learning never stops.
With that in mind, we are offering here some of the most common human resources formulas, tools, and functions in Excel. We hope you find them useful and that you become eager to learn more.
Working with dates
Let’s start with some commonly used functions for managing dates.
Excel stores dates as sequential serial numbers beginning with 1, starting with January 1, 1900, at 00:00 AM. March 16, 2021 is 44271 because it is the 44,271st day since the beginning of Excel time.
Values less than a day are stored as decimal fractions.
Excel has many ways for us to store, compare, and compute dates and times. There are sixteen standard formats for dates, and custom formatting rules give you even more control over the display. To choose a format, right-click on a cell or a list of dates and select one of the date formats or choose the Custom formats to see even more.
Before we start calculating dates, let’s see how we can enter today’s date—even if we don’t know what it is.
Using the TODAY() function
The TODAY function is a time-saving shortcut. It retrieves today’s date and time from your computer, so it updates automatically. Your worksheets automatically update when you open or change them.
The function is handy for calculating things like age, time in service, or any case where the period you are using does not have a specific end. For example, if you want to calculate an employee’s age, you can subtract the employee’s birthdate from today. We start by formatting the column to display years instead of a serial number or date.
- Right-click the Age column header the action panel will open.
- Select Format Cells…
- Custom opens the options; then you type “yy” (no quotes) in the Type field.
- Click OK.
Finding the difference between two dates
Some of the things we do most frequently in HR measure the difference between two dates. We use those calculations for many reasons, including basics like age of employees, time in service, benefits eligibility, pensions, and seniority.
There are many ways to calculate the difference. How you calculate them depends on your company’s practices, industry sector, and country or region.
We’ll give you four that we find useful and easy to use:
Excel DATEDIF returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a “compatibility” function from Lotus 1-2-3.
=DATEDIF(start_date, end_date, unit)
- “y” years
- “m” months
- “d” days
- “ym” months, ignoring years
- “yd” days, ignoring years and months
We can calculate years, months, and days using these formulas:
To give us this result:
Here is the same method using years, months, and days in a single cell:
Our Gregorian calendar makes things difficult for accountants, so they invented the 360-day year with 30 days in each month to create a tidy framework.
Although the Securities and Exchange Commission (SEC) does not accept it for formal published financial accounts, the 360-day Commercial Year is widely used to simplify interest calculations and internal valuations in business worldwide. Two accepted standards are the US (NASD) method and the European method.
The syntax is:
=DAYS360(start_date, end_date, method)
where method indicates a 360-day standard: FALSE for the US method and TRUE for the European method.
- In the US method, if a starting date is the last day of the month, it becomes the 30th day of the month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending day is equal to the 1st day of the next month. Otherwise, the ending day becomes equal to the 30th of the same month.
- In the European method, starting and ending dates on the 31st of the month equal the 30th of the month. How’s that for tidy?
Dates must be either derived from another function or entered using the DATE format:
DATE(yyyy,m,d) where DATE(2021,3,11) is March 11, 2021.
If the start_date is after the end_date, the DATEDIF formula returns a negative number.
If you want to calculate the difference between two dates as years and fractions of years, YEARFRAC will do it for you. The parameters for the function are the start date, end date, and basis, where the basis is 0 to 4:
|0 (or omitted)||US (NASD) 30/360|
Here’s the function itself:
If you use YEARFRAC and the TODAY() function, it will always be correct.
NETWORKDAYS is a handy function for managing projects, schedules, or any time you need to calculate a span of workdays that includes holidays.
Here’s how we most often set up our holiday schedules. We create a worksheet named Holidays that contains the dates and names of holidays, then:
- Select cell range $A$2:$A$10.
- Click Define Name. The New Name window will open.
- Type “Holidays” in the Name field.
- Click OK.
Then, we can enter a NETWORKDAYS Function anywhere in a workbook like this:
Before we begin working with lookup formulas, let’s discuss how to format employee tables, so your functions and procedures work consistently.
Every table should have a primary key. In structured query language (SQL), a column or set of columns is a unique identifier. In Excel, we want the primary key to be a single column that identifies each data row.
If you work with employee information, the key is almost always the Employee ID. Placing it the first (left-most) column in every worksheet that lists individual employees makes VLOOKUP, INDEX, MATCH, and INDEX-MATCH functions much easier to manage.
Format as Table function
We like to name our tables and arrays to make them easier to use. A quick way to do that is to use the Excel Format as Table function.
- Make sure your table is contiguous, meaning there are no gaps between columns or rows.
- From the Home tab, click in the top left-most cell (A1).
- Click on the Format as Table down arrow. The design panel will open.
- Click on the design you want. The table form will open, displaying the table range and indicating whether your table has headers. You will want headers, so if you don’t have them, go back and reformat your table.
- Click OK. Your table will be automatically formatted.
- Give the table a name that makes sense to you. You can now use the table name in functions and formulas.
You can also use a keyboard shortcut to create tables.
- Highlight your entire table.
- Press Ctrl + T
You can then format it any way you like.
If you follow along in an Excel worksheet, you probably noticed that your tables automatically have filters. Filters are amazing time-savers when you want to slice and dice data. They can also be useful when you want to validate the data in a table.
Suppose you needed to follow up on performance appraisals that have not been completed. You have a report that lists all your employees with the appraisal status.
You could sort the worksheet by status and copy and paste the ones you need to follow up, but it is easy to apply a filter by selecting the status you want to follow. In this example, we want to find all the appraisals that had not been started.
- Click on the filter drop-down icon.
- Clear the form by click on the (Select All) checkbox.
- Select Not Started, then OK.
Here is what your filtered table would look like:
You can use multiple filters in many combinations according to the data type in the column. Number filters have many combinations, as do dates.
Suppose we needed a list of people hired between January 1, 2016 and December 31, 2018.
- Click on the Date of Hire column filter. A list of filter options will open.
- Select the Date Filters option. You now get a long list of filtering options.
- Select Between and fill in the date parameters.
Your report is ready:
Working with pivot tables
Of all the features, functions, and tools in Excel, pivot tables are among the most useful. They are dynamic summary reports you generate from a database, a table in a worksheet, an external data file, or an external database.
The PivotTable function enables you to transform any data source into robust, flexible data summaries in a few clicks. You can then slice and dice, summarize and combine, and manipulate data in just about any way you can imagine.
Here’s how to create a report from any worksheet:
- First, we click in the top-left cell (A1) in a contiguous table of employee data. A good data sample will have the employee number, a value unique to each individual, in column A.
- We click on the Insert menu.
- Then, we click on the PivotTable icon. The Create PivotTable form will open.
- Excel automatically selects our entire table of data.
- We select New Worksheet to tell Excel where to put the table.
- Then, click OK.
- A new tab will open with a blank pivot table on the left and the PivotTable Fields form on the right. Now, we do housekeeping first—renaming the tab so we can find it later.
- In the PivotTable Fields form, drag Date of Termination to the Values section, giving us a numerical value for the table to count.
- We also drag Department, Employee Name, Position, and Date of Term to the Rows section.
At this point, the table will start to take shape, but it will be a confusing mess. We’ll fix that now.
- Click anywhere in the table, then click on design. The design menu appears.
- Click on Report Layout.
- We click on Show in Tabular Form. It starts to make sense, but we don’t need a subtotal in the Employee Name column.
- Right-click in the Employee Name column and click on Subtotal “Employee Name” to uncheck it. We do the same with the Position column.
We now have a neat table showing the number of terminations and reasons for each department.
There is just a little cleanup to do. Some of the column names are a bit long and confusing. We can enter new names directly into the column headers.
One more thing:
Let us show the reasons why this tool is called a pivot table.
In the PivotTable Fields form, we grab the Department field and drag it to the Filters section to pivot the field from a row field to a filter where we can change the report to show a single department or any combination.
Now, we’ll try another pivot. We right-click on the Reason field, hover on Move, then click on Move “Reason” to Beginning.
We now have a report on reasons for termination.
Pivot table columns
If we wanted to see the reasons for the terminations summarized for each department, we could use columns to show the departments.
Let’s remove the report’s names and positions to reduce clutter, then drag the department to the Columns section. We now have a summary report, but it’s not very useful.
Let’s change the way we summarize the data. Right-click in the Grand Total cell and select Show Values As, then % of Grand Total.
After we use the Grand Total cell to format the numbers as a Percent with no decimals, we have a report that tells us something about where we might need an intervention.
We’ve given you only a quick introduction to the power of pivot tables. We hope it whets your appetite to learn more.
Browse our training courses to see how you can put pivot tables into action in people analytics. Once you master them, you will find them to be your best friend when someone in your team needs a quick answer.
Common human resources formulas and metrics
Although there are hundreds of formulas and functions that can help HR get things done, there are a few that stand out because they support important HR metrics.
Cost of benefits and employee programs
Calculating benefits (including all employee programs) helps us calculate the costs of benefits and calculate the total cost of employees for use in other internal metrics.
Benefits costs per employee
We calculate the cost of benefits as the ratio of benefits costs to an average number of employees.
benefits_cost = total_cost_of_benefits_and_programs ÷ average_number_of_employees
You can break this cost down by any period for comparisons.
Many companies compute separate ratios for hourly workers, salaried employees, and executives.
Benefits and programs as a percent of pay
We compute benefits to pay as a percentage ratio.
benefits_ratio = cost_of_benefits ÷ total_pay
Total employee cost
Once we have the benefits ratio, we can use it to calculate the total cost of employees.
total_employee_cost = (1 + benefits_ratio) × total_pay
In HR, we sometimes get so focused on cost that we forget to show our business impact. We recommend partnering with managers and leaders throughout the business to answer the most important questions when making a change or implementing an intervention. They are: What moved? By how much?
Revenue per employee
A quick answer to those questions is in Revenue per Employee. We get the top-line revenue from financial reports and divide it by the number of employees.
revenue_per_employee = average_number_of_employees_÷ top_line_revenue
Tenure and turnover
You can get average tenure with an average function on a list of employees with Hire Date using a YEARFRAC function we explained above.
- Format your list as a table.
- Create a new column next to Hire Date named Tenure. Then, enter the formula in the top cell.
- In the Number menu group, format the value using the comma icon (Accounting format).
- In the Table Design tab, check the Total Row checkbox. The view will jump to the bottom row.
- In the Tenure column, select the Average function.
Talent acquisition metrics
Recruiting costs are a critical business metric. You need to know how much time it takes and what costs to hire talent. Then, you can compare those costs against what you spend on employee engagement, retention, and development.
Time to fill a vacancy
The time it takes to source, engage, select, and hire a candidate is a critical factor in calculating recruiting costs.
An inefficient pipeline can cause costs to grow out of control, and the impact is much more than the cost of operating the recruiting function. Your people must cover the vacancy, and you may incur overtime costs. There will be additional stress on the people who cover it.
First, we define a table named “Holidays” to use it as a NETWORKDAYS function parameter.
Cost per hire
Cost per hire includes covering the vacancy, the direct costs of advertising and hiring, and the cost of managers and recruiters in the process. Here’s a sample breakdown of the talent acquisition cost with respective HR formulas.
|Cost of covering a vacancy||salary X days_to_fill + allocated overtime|
|Recruiter time to source, screen, and interview candidates||(Recruiter_hourly_pay + benefits_ratio) × hours|
|Manager time to interview, evaluate, and select candidates||(Manager_hour_pay + benefits_ratio) × hours|
To understand your talent acquisition function’s efficiency and effectiveness, you will need to measure how each step in the process functions. Only when you do that can you make informed decisions on what is or is not working.
The yield ratio is the percentage of applicants who make it to the next step of the process.
yield_ratio = applicants_at_step ÷ applicants_at_previous_step
Time to fill and hiring velocity are not the same. Time to fill only gives you the length of the entire process. Many companies are now beginning to measure how quickly the process moves at each step. Any delay at a critical time can lead to candidate ghosting.
You can track velocity along with your Yield Ratio. Here are the formulas.
And the result. We calculated the total time to fill in the last column.
Over to you
We hope this quick overview has given you a taste for making data-driven decisions. We encourage you to learn by doing and apply these human resource formulas and functions. Ultimately, use your findings to guide and adapt your day-to-day operations and improve your HR strategy.