Advertisement

Quick and Dirty Excel Automation for Sourcers and Recruiters by @Megan_Calimbas

Jan 9, 2015
This article is part of a series called Tips & Tricks.

At some point, most recruitment professionals have worked from a spreadsheet that includes the standard fields of First Name, Last Name, Location, Title, Company, etc. Though most of us have different workflows surrounding when they enter that data, it ends up there at one point or another.

If you’re a disciplined user of spreadsheets you probably begin populating those fields immediately upon target identification. You’re probably going to begin identifying those targets before you get to abstract and exotic sourcing means, so you’ll typically start with populating the targets names, and company information. From there other critical information, and for us that means contact info. Leading us to the next step- sourcing contact information.

Everyone has a slightly different workflow and sourcing professionals take advantage of numerous tools to minimize the additional clicks, data entry, and otherwise time sucking speed bumps in their day. If we can automate/eliminate at least some of the time in the next step, I’m always a happy camper. So we started using our spreadsheet to automate a simple google search for candidate contact info right there in our working spreadsheets.

Step 1. For the info I want to log my columns run A-J. To set up the search I start With the first empty cell in K on the same row as my data. For my example this would be Column K, Row 15

Step 2. Click the Fx button and select or type “Concatenate”

Step 3. Is where I tell excel what cells I want it to pull the information from, by using the “” and CHAR(34) commands. In my spreadsheet it is A15, B15, and E15 –  First Name, Last Name, and Company.

calimbas post 1

Enter the following into the text boxes:

Text 1:  CHAR(34)

Text 2:  Select the cell that includes first name

Text 3: “ “

Text 4: Select the cell that includes last name

Text 5: CHAR(34)

Text 6: “ “

Text 7: CHAR(34)

Text 8: Select the cell that includes company name

Text 9: CHAR(34)

Step 4:

Click “okay” and this should populate the selected cell

calimbas post 2

 Step 5:

Google contents of a Cell:

Copy and paste the following formula in the cell to the right of the one you just used in the CONCATENATE formula above.

=HYPERLINK(“https://www.google.com/search?q=”&SUBSTITUTE(K15,” “,”+”),”Search Google for “&K15)

*NOTE* adjust the cell to number to your own spreadsheet cell*

Creating this:

calimbas post 3

Step 6:

Click the link, and hopefully you get to enjoy results like this!

Now just drag the formula for both cells down the length of the spreadsheet, save this as your new working template, and you’re set. Will this work every time? No. Will building it once and saving it reduce waste in your process over the course of weeks and months so you can deliver faster? Absolutely.

This article is part of a series called Tips & Tricks.
Get articles like this
in your inbox
Subscribe to our mailing list and get interesting articles about talent acquisition emailed weekly!
Advertisement