Most talent sourcers and recruiters have a love-hate relationship with Microsoft Excel. It’s great for keeping track of lists, tracking candidates and metrics, and stashing names while doing name generation. But it’s also just one more place to do data entry and can seem cumbersome at times. I’m about to show you a reason to fall back in love.
There are three key tricks I use in Excel to speed up my list manipulation and generation. I’m going to share the first one with you here and the others in subsequent posts.
The first reason to love Excel is the ability to manipulate long lists of data that you may have otherwise had to do manually.
Sourcer Tip for Excel #1 – Concatenate
We’ve all been there, we have a long list of research but things are in multiple columns (i.e. First Name and Last Name or City and State). We want the whole name in one column or a City, State in one column. How do most people tackle this? By beginning to type or copy and paste each cell. Not anymore. With a simple Excel formula the tedious manual method is gone and you can have a quick clean list of names. How?
We’re going to use a function called “Concatenate”. This function can bring the contents of two or more text cells together into one. i.e. Column 1: First Name + Column 2: Last Name = Column 3: First Name Last Name
Here are the steps to combine First Name and Last Name into one column. Let’s pretend this is your starting List:
Step One: Insert a blank column between First and Last Name.
Step Two: In the newly created column hit the Space Bar once and then copy that down all the rows. This leaves your spreadsheet looking like this:
Step Three: In Column D where we are going to put the full name we now enter the following formula: =CONCATENATE(A2,B2,C2) and hit ENTER
Voila! Here is what you’ll end up with. Now just copy that formula down the column and you’ve got a list of full names.
If you were doing this to combine address components like City, State then you would simply enter a comma with a space following it in your newly inserted column and follow the rest of the process.
REMINDER: The results in column D are currently the result of a formula so if you delete columns A-C without first copying and pasting Column D as “Text Only”, you’ll lose your results.
In Part 2 of this Excel Series I’ll show you how to do this in reverse, taking a list of full names dividing them into separate columns.