Ok recruiters. I know your ugly secret.... no not that one....no...gross... no not that either....Jesus.
You have a big messy spreadsheet as your ultimate source of truth, don't you? Don't worry, its pretty common.
It's also common for that wee sheet to gobble up more and more of your time. 'Operational debt' is the fancy term. 'Dicking about' is the non-fancy term.
Well let's see if we can use our data-driven super powers to save you a ton of time, and if all the HR folk think you are a witch, all the better.
1. IMPORT RANGE
What It Does
Pulls live data from one Google Sheet to another.
Formula
=IMPORTRANGE("spreadsheet_url", "sheet_name!range")
Use Case
You’re tracking candidates in one sheet and interview feedback in another. Use IMPORTRANGE to create a consolidated view that pulls candidate details from the first sheet and interview feedback from the second.
Any updates in the parent sheets are reflected in the importing sheet so all data is accessible in one place for quick decision-making.
Also good for smushing together data from multiple trackers in different teams.
2. COUNTIF
What It Does
Counts the number of cells that meet a specific condition.
Formula
=COUNTIF(range, criteria)
Use Case
You’re sourcing candidates and want to track how many applications came from LinkedIn. If Column A lists sources (e.g., “LinkedIn,” “Job Board”), use =COUNTIF(A:A, "LinkedIn") to see the exact number of LinkedIn applicants.
3. VLOOKUP
What It Does
Searches for a value in one column and returns a corresponding value from another column.
Formula
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Use Case
You have a spreadsheet of candidates with IDs in Column A and interview scores in another sheet. Use VLOOKUP to pull interview scores into your main spreadsheet. For instance, =VLOOKUP("CandidateID", 'ScoresSheet'!A:C, 3, FALSE) retrieves the score for a given candidate.
4. CONCATENATE
What It Does
Combines text from multiple cells into one cell.
Formula
=CONCATENATE(text1, text2, ...) or =TEXTJOIN(" ", TRUE, text1, text2, ...)
Use Case
You want to personalise outreach emails by combining first and last names into a full name column. Use =CONCATENATE(A2, " ", B2) to create “John Smith” from “John” in Column A and “Smith” in Column B.
5. QUERY
What It Does
Extracts and filters data using SQL-like syntax.
Formula
=QUERY(data_range, "query", [headers])
Use Case
You’re managing a large dataset of candidates and want to filter for those who passed the technical interview. If “Status” is in Column D, use =QUERY(A:D, "SELECT A, B, C WHERE D = 'Passed'", 1) to create a new table with only successful candidates.
Why These Formulas Matter
• IMPORT RANGE keeps all your data connected and up-to-date.
• COUNTIF helps track sourcing metrics at a glance.
• VLOOKUP lets you to link datasets for faster insights.
• CONCATENATE makes personalisation and automation easier.
• QUERY turns messy data into clear data for more actionable insights.
So, spend a bit of time learning these functions and you fill find you save hours and hours of painfully manual donkey works further down the road.stay.
Give them a try!
Luke Update
I'm still parsing all the cool stuff I learned at the RecOps Conference, I'm also running through Jim Millers awesome talk on Hiring Excellence and how time can be used as a variable to improve hiring outcomes. This stuff will be informing my content for the next few weeks.
Well... that's issue twenty eight! If you have any questions about it, or any feedback on this issue of The Data Driven Recruiter, grab me on LinkedIn for a chat.
I'll see you next week!