{"id":5176,"date":"2019-10-03T11:30:24","date_gmt":"2019-10-03T09:30:24","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=5176"},"modified":"2025-03-31T14:05:19","modified_gmt":"2025-03-31T12:05:19","slug":"google-sheets-formulas-hr-managers","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/google-sheets-formulas-hr-managers","title":{"rendered":"Five Google Sheets formulas for HR managers"},"content":{"rendered":"\n

Beebole\u2019s blog is home to some really great tutorials<\/a>. Often, both Google Sheets<\/strong> and Office 365<\/strong> experts dive into practical tutorials you can start implementing at work today. Whether you\u2019re a financial controller looking to manage profits, an HR manager who wants the best for your team or someone who just wants to manage company data more efficiently, these tutorials are for you. We\u2019ll be publishing these posts regularly, so don\u2019t hesitate to let us know if there\u2019s a topic you\u2019d like to see covered.<\/p>\n\n\n\n

Five things your HR team should be doing with Google Sheets <\/h2>\n\n\n\n

If you work in Human Resources, your job might contain all of the following: employee time tracking<\/svg><\/span><\/a>, payroll, hiring, performance review, personnel issues, and so much more! With a job that changes every day, how do you make the most of your time?<\/p>\n\n\n\n

Every job requires working with data. Using Google Sheets, you can manage and analyze data effectively<\/a>. It\u2019s a simple, free spreadsheet tool that\u2019s good enough to analyze your workforce and their needs. In this tutorial, you’ll learn five formulas HR managers can use in Sheets<\/strong> to automate and expedite important tasks.<\/p>\n\n\n\n

We\u2019ve created a spreadsheet that you can use to practice the formulas and functions in this tutorial<\/em>, which you can grab here:<\/p>\n\n\n

\n\t
\n\t\t\t\t\t
<\/div>\n
<\/div>\n \t\t
\n\t\t\t
\n\t\t\t\t\n\t\t\t<\/div>\n\t\t\t
\n\t\t\t\t\t\t\t\t\t
GET MY COPY<\/div><\/div>\n\t\t\t\t
5 Google Sheets Formulas for HR Managers\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n \t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tDownload now! <\/a>\n <\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n <\/div>\n<\/div>\n\n\n

With your copy of this spreadsheet, you can follow along and complete the exercises below before applying it to your job in HR. Beebole users can also take advantage of our free add-on for Google Sheets<\/svg><\/span><\/a> and access their timesheet data via Google spreadsheet in real time.<\/em><\/p>\n\n\n\n

1. How to calculate payable hours <\/h2>\n\n\n\n

If you\u2019re running a lean operation, chances are that you\u2019re doing payroll<\/strong> in-house. For hourly employees, that means calculating and capturing the hours worked<\/a> so that you pay your team accurately. <\/p>\n\n\n\n

For each employee, it\u2019s best to capture the hours they work by shift<\/strong>. On each spreadsheet row, log an employee\u2019s time in and time out. Payable hours are the difference between the two times. <\/p>\n\n\n\n

Now, find the difference between the two times by writing a simple subtraction. In this case, subtract the start time from the end time with a formula.<\/p>\n\n\n\n

\"calculating<\/figure>\n\n\n\n

You\u2019ll notice that Sheets returns an answer, but it\u2019s not in the format that we need. Let\u2019s use formatting options to convert it to a total number. On the formatting dropdown, choose the number option.<\/p>\n\n\n\n

This number is basically a partial day. To convert it to a simple number of hours, multiply that number times 24 in the same cell.<\/p>\n\n\n\n

\"calculating<\/figure>\n\n\n\n

That\u2019s it! You\u2019ve calculated their payable hours, and all that\u2019s left is to multiply it by their hourly pay rate to find the wages owed. Now you\u2019ve calculated base wages and are ready to prepare the payroll.<\/p>\n\n\n\n

\"Easy<\/figure>\n\n\n\n

On a related note, you might also be interested in learning about payroll variance<\/a>, and why it’s crucial to making smarter business decisions. <\/p>\n\n\n\n

2. How to perform a compensation review<\/h2>\n\n\n\n

Compensation reviews<\/strong> are a must for every HR team. If your employees are paid out of line with the market or their peers, you risk losing top performers or inflating your labor costs. <\/p>\n\n\n\n

Spreadsheet functions make understanding the true cost of employees<\/a> and comp reviews easier. Let’s look at a few techniques that help you review and adjust compensation. <\/p>\n\n\n\n

First, make sure to lay out your data in a structured format that makes it easier to review. Capture all of the data that can help you understand the employee’s compensation, like job title, years of service, performance rating, and any other info that helps you compare compensation.<\/p>\n\n\n\n

\"Using<\/figure>\n\n\n\n

To review comp, you need to compare employees with similar job roles. That’s why it helps to use a function called AVERAGEIF, to average the salaries for a given job title. Let’s use the AVERAGEIF function to find the average for each job title. <\/p>\n\n\n\n

First, start by creating a list of all job titles in your organization. Paste that list separately from your data. Now, let’s write an AVERAGEIF. The structure for AVERAGEIF is as follows:<\/p>\n\n\n\n

\"google<\/figure>\n\n\n\n

Let’s walk through this. For the first part of the formula, you’ll want to highlight the job titles. Also, go ahead and press F4 on your keyboard to lock in the references to these cells. Then, add a comma to move on to the next part of the formula.<\/p>\n\n\n\n

For the second part of the formula, you’ll want to enter the “condition” or basically the field you want to watch. Click on the first job title in the list you created. This is basically telling Sheets to use the selected job title for your AVERAGEIF. <\/p>\n\n\n\n

One last step: add a comma, and then return to the comp data table. Highlight the numeric values, and press F4 again to lock in the references. Close your parenthesis, and press enter. Voila! You’ve found the average, but only for a specific job title.<\/p>\n\n\n\n

\"using<\/figure>\n\n\n\n

Best of all, you can simply pull the formula down to run the same calculation for each job title. Because we locked the data references (except for the titles in the list) the formula will extend perfectly.<\/p>\n\n\n\n

\"spreadsheets<\/figure>\n\n\n\n

That’s it! You have averages for each job title in your organization, and you can use it to review individual compensation plans to make sure that there are no outliers. If you found this tip helpful, don’t miss our tutorial on how to automate emails from Excel<\/a>, which happens to be the easiest way to send employees their bonus emails!<\/p>\n\n\n\n

3. How to calculate length of service <\/h2>\n\n\n\n

Showing appreciation for employees is a key part of retaining them. Celebrating milestones and remembering their growth<\/strong> in your company shows that you care. That\u2019s why it helps to calculate length of service<\/strong>.<\/p>\n\n\n\n

This tip is a simple one. For each employee, you need to start with a spreadsheet that has each employee\u2019s start date. Then, write a formula for today\u2019s date using the TODAY <\/strong>function so that you have an always-up-to-date cell with current dates.<\/p>\n\n\n\n

\"Looking<\/figure>\n\n\n\n

Sheets returns the number of days between two dates. If you want to convert this to year, you\u2019ll need to convert it by dividing the result by 365.<\/p>\n\n\n\n

\"how<\/figure>\n\n\n\n

At the start of each month, open your spreadsheet and review the list to plan any upcoming celebrations. As you approach whole numbers in the \u201clength of service\u201d column, prepare celebrations for your team.<\/p>\n\n\n\n

4. How to clean data with functions <\/h2>\n\n\n\n

You don\u2019t always have the luxury of \u201cclean\u201d data. Maybe your payroll system automatically joins first and last names into a single column. Or maybe a monthly report from your financial system puts employee names in all caps. <\/p>\n\n\n\n

No matter what type of data you work with, every HR professional benefits from a few data cleanup<\/strong> techniques. Let\u2019s learn more.<\/p>\n\n\n\n

For our example, let\u2019s assume that we get data in an ugly format like the one that you see in the screenshot below.<\/p>\n\n\n\n

\"how<\/figure>\n\n\n\n

To split it, we need to separate it in columns each time you see the \u201c | \u201d character. Highlight the first column, then go to the Data > Split Text to Columns<\/strong> .option. Then, on the Separator <\/strong>dropdown, choose Custom <\/strong>and add the character that you want to split your text based on.<\/p>\n\n\n\n

\"cleaning<\/figure>\n\n\n\n

Now, Sheets will split the column every time there\u2019s a \u201c|\u201d character. You could repeat this same set of steps to split full names into first and last names by choosing \u201cspace,\u201d for example. <\/p>\n\n\n\n

One other step to clean up data. Use the PROPER <\/strong>function to convert the all-caps text to a more naturally readable version. It\u2019s not perfect (for example, you might need to retype \u201cVP\u201d instead of \u201cVp,\u201d but it\u2019s faster than re-typing from scratch.)<\/p>\n\n\n\n

\"a<\/figure>\n\n\n\n

5. Calculate working days <\/h2>\n\n\n\n

Rounding out our formulas, let\u2019s learn how to calculate the net working days<\/strong> between two dates. This is another formula that you can use when calculating salaries or length of service. No matter how you use it, it helps you find the number of working days between two dates.<\/p>\n\n\n\n

Use the function NETWORKDAYS <\/strong>to start calculating the working days. Naturally, this excludes weekends, so it\u2019s ideal for your salaried workforce. Point it to two date cells, separated by commas.<\/p>\n\n\n\n

\"Calculating<\/figure>\n\n\n\n

You can also exclude holidays from your calculation. Add another comma, then highlight the list of holiday dates. You could type F4 on your keyboard to lock in the holiday references so that the formula extends naturally as you drag it.<\/p>\n\n\n\n

\"how<\/figure>\n\n\n\n

That\u2019s it! The result is a calculation for the total working days between dates. <\/p>\n\n\n\n

In this article, you saw five techniques to help you work with data in Google Sheets. These techniques are designed to help you spend less time working with data, and more time engaging your workforce<\/a>. If you enjoyed this post, don’t miss more great Google Sheets spreadsheet tips like how to automate employee cell phone data usage<\/a> and how to perform an annual compensation review<\/a>. <\/p>\n\n\n\n

Ready to give it a try? Get a free 30-day trial with Beebole to get instant access to payroll, employee time tracking sheets, customizable reports and more. The best part? Use our Google Sheets add-on<\/a> to take your HR data to the next level. Free for all our users. <\/em><\/strong><\/p>\n\n\n

\n
\n

Take Your HR Data to the Next Level<\/h4>\n
\n Book a Call<\/a>\n Sign Up<\/a>\n <\/div>\n <\/div>\n<\/div>\n\n\n

\u2014
Photo by @bkotynski on Unsplash<\/p>\n

The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company\/tool or their past\/present organizations, employers, or associates.<\/div>","protected":false},"excerpt":{"rendered":"

Beebole\u2019s blog is home to some really great tutorials. Often, both Google Sheets and Office 365 experts dive into practical tutorials you can start implementing at work today. Whether you\u2019re a financial controller looking to manage profits, an HR manager who wants the best for your team or someone who just wants to manage company […]<\/p>\n","protected":false},"author":15,"featured_media":10706,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[123],"tags":[2890,2892,4012,4013],"class_list":["post-5176","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-productivity-time-management","tag-hr","tag-google-sheets","tag-tutorials","tag-templates"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=5176"}],"version-history":[{"count":25,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5176\/revisions"}],"predecessor-version":[{"id":13816,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/5176\/revisions\/13816"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/10706"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=5176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=5176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=5176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}