Posted by Jonathan Kidder on March 17, 2023March 17, 2023

I took a highly beneficial course at university that delved into the intricacies of Excel and how to properly format data. It’s essential for recruiters and talent sourcers to have a solid grasp of Excel fundamentals, such as formatting, shortcuts, and formulas, as well as the ability to present data effectively to business leaders and hiring managers. Here’s a list of Excel hacks for recruiters and talent sourcers to use in there daily activities.

It is important for recruiters and talent sourcers to advance their skills in Excel because it increases efficiency, improves data analysis, enhances communication, helps to stay competitive in the industry, automates tasks, and increases accuracy. By becoming more proficient in Excel, recruiters can work more efficiently, analyze candidate data more effectively, communicate information more clearly, automate tasks to save time, and reduce the likelihood of errors in their data.

Here are some best practice tips for using Excel:

Use proper formatting: Proper formatting can make your data more readable and easier to understand. Use clear fonts and colors to differentiate different types of data.

Use shortcuts: Excel has many shortcuts that can save you time and make your work more efficient. For example, you can use the “Ctrl + C” and “Ctrl + V” shortcuts to copy and paste data, respectively.

Use functions: Excel has many built-in functions that can help you perform calculations and analyze your data. Examples include SUM, AVERAGE, and COUNT.

Use filters: Filters allow you to sort and analyze your data in various ways. For example, you can filter by a specific value or date range.

Use charts and graphs: Charts and graphs can help you visualize your data and make it easier to understand. Excel has many built-in chart types, such as line charts, bar charts, and pie charts.

Keep your data organized: Organize your data into tables and use meaningful headers for each column. This will make it easier to sort and analyze your data.

Use conditional formatting: Conditional formatting allows you to highlight specific cells based on certain criteria. For example, you can use conditional formatting to highlight cells with values above a certain threshold.

Use named ranges: Named ranges can make it easier to refer to specific cells or ranges of cells in your formulas. For example, you can name a range of cells “Expenses” and refer to it in a formula as “=SUM(Expenses)”.

Use error checking: Excel has built-in error checking tools that can help you identify and fix errors in your formulas.

Keep your formulas simple: Use simple formulas that are easy to understand and modify. Avoid using complex formulas that are difficult to troubleshoot and maintain.

Excel Shortcuts:

Recruiters can use several Excel shortcuts to work more efficiently and save time. Here are some useful Excel shortcuts for recruiters:

Ctrl + C and Ctrl + V: Copy and paste data quickly.

Ctrl + X: Cut data.

Ctrl + Z: Undo the last action.

Ctrl + Y: Redo the last action.

Ctrl + F: Find data in the spreadsheet.

Ctrl + H: Replace data in the spreadsheet.

Ctrl + A: Select all data in the spreadsheet.

Ctrl + B: Bold selected text or cells.

Ctrl + I: Italicize selected text or cells.

Ctrl + U: Underline selected text or cells.

Ctrl + 1: Open the Format Cells dialog box.

Ctrl + Shift + L: Turn on/off filters.

Alt + Down Arrow: Display the drop-down menu for a selected cell.

F2: Edit the active cell.

F4: Repeat the last action.

F7: Spell check selected text or cells.

F11: Create a chart based on selected data.

Shift + F3: Open the Insert Function dialog box.

Shift + F11: Insert a new worksheet.

Shift + Arrow Keys: Select a range of cells.

By using these shortcuts, recruiters can work more efficiently in Excel and save time.

Excel Formulas:

Recruiters can use several Excel formulas to help manage their data and analyze candidate information. Here are some useful Excel formulas for recruiters:

COUNTIF: Count the number of cells in a range that meet a certain criteria. For example, =COUNTIF(A2:A10, “Sales”) would count the number of cells in range A2:A10 that contain the word “Sales”.

SUMIF: Add up the values in a range that meet a certain criteria. For example, =SUMIF(A2:A10, “Sales”, B2:B10) would add up the values in column B that correspond to the cells in column A that contain the word “Sales”.

VLOOKUP: Look up a value in a table and return a corresponding value from a specified column. For example, =VLOOKUP(“John”, A2:B10, 2, FALSE) would search for the name “John” in column A and return the corresponding value in column B.

CONCATENATE: Combine two or more text strings into one. For example, =CONCATENATE(A2, ” “, B2) would combine the text in cells A2 and B2 with a space in between.

IF: Perform a logical test and return one value if the test is true and another value if the test is false. For example, =IF(C2>=75, “Pass”, “Fail”) would return the word “Pass” if the value in cell C2 is greater than or equal to 75 and “Fail” if it is less than 75.

LEFT/RIGHT/MID: Extract a specified number of characters from the beginning (LEFT), end (RIGHT), or middle (MID) of a text string. For example, =LEFT(A2, 3) would return the first three characters of the text in cell A2.

AVERAGE: Calculate the average of a range of values. For example, =AVERAGE(B2:B10) would calculate the average of the values in column B from row 2 to row 10.

AX/MIN: Find the maximum or minimum value in a range of values. For example, =MAX(C2:C10) would find the highest value in column C from row 2 to row 10.

By using these formulas, recruiters can manage and analyze their data more effectively in Excel.

Creating a Funnel Recruiting Tracker in Excel:

Creating a recruiting funnel tracker in Excel can help recruiters keep track of the progress of their recruitment process and identify areas that may need improvement. Here are the steps to create a recruiting funnel tracker in Excel:

Determine the stages of your recruiting funnel: Decide on the stages of your recruiting funnel, such as “Sourcing,” “Screening,” “Interviewing,” “Offer Made,” and “Hired.”

Create a table: Create a table in Excel with columns for each stage of the recruiting funnel and rows for each candidate. Include additional columns for information such as the candidate’s name, contact information, resume, and notes.

Enter candidate information: Enter the information for each candidate into the appropriate cells of the table.

Update the status: As you progress through the recruiting funnel, update the status of each candidate in the appropriate column. For example, when a candidate moves from the “Sourcing” stage to the “Screening” stage, update their status from “Not Yet Contacted” to “Screening.”

Use conditional formatting: Use conditional formatting to highlight cells that require attention. For example, you could use conditional formatting to highlight cells in the “Sourcing” column that have not been updated in the past week.

Use formulas to track metrics: Use formulas to track metrics such as the number of candidates in each stage of the recruiting funnel and the percentage of candidates that make it from one stage to the next. For example, you could use the COUNTIF formula to count the number of candidates in each stage of the recruiting funnel.

Analyze the data: Use the data in your recruiting funnel tracker to identify areas that may need improvement. For example, if you notice that a high percentage of candidates are dropping out of the recruiting process at the “Screening” stage, you may need to improve your screening process.

Follow the laws (North America):

When creating candidate tracker excel docs, it is important to comply with HR laws and regulations to avoid legal issues and ensure a fair and ethical hiring process. Here are some key considerations:

Data privacy laws: Ensure that the personal data of candidates is stored and processed in compliance with applicable data privacy laws, such as the General Data Protection Regulation (GDPR) in the EU or the California Consumer Privacy Act (CCPA) in the US.

Equal opportunity laws: Avoid any discriminatory practices in the hiring process, such as discriminating based on race, gender, religion, age, or other protected characteristics. Keep track of all candidates in a consistent and impartial manner, and ensure that all candidates have an equal opportunity to apply for and be considered for open positions.

Record-keeping laws: Some laws require employers to keep certain records related to the hiring process, such as the number of applicants, the number of hires, and the reasons for non-selection. Check your local laws to ensure that your candidate tracker excel docs comply with these requirements.

Confidentiality: Keep all candidate information confidential and only share it with individuals who have a legitimate need to know, such as hiring managers and HR personnel. Protect the information from unauthorized access, disclosure, and misuse.

Accessibility: Ensure that your candidate tracker excel docs are accessible to individuals with disabilities, such as by using accessible formatting and providing alternative formats upon request.

Template Examples: 🏆

Here’s how to create a talent sourcing funnel calculator

Here’s several recruiting tracker template examples

Learn more about using data to drive discussion with your leadership teams: 👌

Conducting Monthly and Quarterly Business Reviews in Recruiting

How to prepare for the debrief final round discussion

Creating a data-driven talent sourcing strategy

Centralized vs Specialized Recruiting Team Models

How to conduct a recruiter performance review

Using recruiting to help decrease attrition rates

Author
Recent Posts

Follow me

Jonathan Kidder

With more than 12 years of recruitment experience, Jonathan has helped startups and small businesses find the talent they need. He runs the blog WizardSourcer, which explores the latest trends in talent sourcing, provides reviews of recruitment tools, and features interviews with prominent figures in the industry.