Excel functions every HR professional should know

Although my cute widdle heart beats for HR, it's true that I'm a bit of an Excel geek. I probably shouldn't have spent so much time with my good friends over at Finance.

But it's a fact that a handful of Excel functions learned from helpful colleagues have made my life much, much easier and I'm now using many of them on a daily basis.

Before it gets interesting: how to learn Excel

I can't sit down and learn something if I don't already know how to use it. Maybe you're the person who can sit through a training or read an online tutorial or book and remember a special function 6 months later when you need it. Unfortunately, I'm not like this.

So here's what I do. When I realize that my current skills can't help me solve a problem, I look for tutorials. For example, today I needed to create a waterfall chart but didn't remember how to do it, so I googled "tutorial excel waterfall").

Another strategy is to talk to your friendly friends at Finance (they're weird, but they're also the masters of spreadsheets). Have a coffee with them. They're usually happy to share their knowledge. If you're not comfortable talking to the bean counters, try to find an HR controller somewhere.

Filters

I already hear the duh's in the crowd. I know they're not difficult to use, but I think that many people could benefit from using filters more often.

I use them everyday in my "Entry process follow-up checklist" where I have always a few dozen active lines (one line per new hire, active until the very last step of the entry process has been successfully concluded).

Need to work on one line? Use filters so the other lines don't get in the way. And instead of scrolling through the list of names, filter first for "department", "country" if you work for an international organization or "job title" if you hire many people for the same position.

Once the entry process is over and the person in question has become a "real" employee (from an HR admin point of view), exclude this line. One way to do it is with a column you can call "status". Add "okay" in this line's status column and filter for "blanks". You will then only see the "unreal" employees.

Or you might have a list with several dozens or even hundreds of employees and need to look up all female employees in Marketing with a car allowance sorted by last name? Too bad, Excel only allows to sort for three different criteria, but you need four. And you don't want to spend hours scrolling down the file anyway.

So lets use a filter for gender (it's quick because there's only two possibilities), then department (if you have departments with only male employees some departments will already have been excluded, so the list gets shorter) and then use a filter for "yes" or a custom filter ("car allowance" not equal 0) if the column contains amounts (e.g. "0", "200", "500", etc.). Finally, you can sort by last name.

Vlookup [1]

The vlookup function can be useful in the following situations:
you need to combine two lists with different information, for instance one list with entry date and salary and the other a list provided by the department heads with the annual bonus results. You need one cell that contains the same information in both lists, and then you can combine them to a single list (possible information is "employee number" or "name").

And maybe a Concatenate might even come in handy.

Concatenate

If you have "LastName FirstName" in one list and two separate cells for last name and first name in the other list, use the Concatenate function.

Example: Last name in column A and first name in column B: add the following formula to column C (assuming the title of the column is in row 1):

=A2 &" "& B2

This will combine "Benson" from column A and "Steve" from column "B" as "Benson Steve" in column C with a nice space in between. Then copy the formula in cell C2 to the following lines (C3 to Cx).

To avoid problems, I recommend to remove the formulas (mark column C, select "copy", then click "edit", "paste special" and "values").

Finally: in case I've offended you...

If you're an HR professional and still think that the title of this post is too strong and maybe even offensive, or if you're offended by this post because you've known everything for years: please accept my apology.

I'm aware that not every HR professional needs advanced Excel skills, but I never had the opportunity to go to an "Excel for HR peeps" training.

One late night experience

One experience I'll never forget is how I tried for hours to get some budget figures ready. At 1 am I walked over to Finance because I needed a clarification. The graveyard shift guy gave me the info and said: "Great, in this case I'll get the HR figures in 10 minutes." When I told him that I needed at least 2 hours he taught me the secrets of pivot tables. This is why I think that many HR people could benefit from additional Excel skills.

And if you're still offended... well, you know, it's hard to get any attention as a new blogger. So please tell all your HR friends about this rude new blogger and don't forget to include the link ;-)


____________________________________________________________________
[1] for more information on vlookup's, lets google "tutorial excel vlookup"