If you've ever had to work with a large number of people or have to sort through a deep trove of data, chances are you've used Micosoft Excel software to some degree. Most of us only scratch the surface of Excel's full capabilities, while others have become 'cell' masters after decades of using it.

After consulting our own consultants, we've summarised the top 15 Excel formulas that (we feel) are useful for both recruiters and candidates - even tech managers have to use Excel every now and then!

A note about formulas: "Range," "condition," "lookup_value," "table_array," and "text" in the formulas refer to the specific ranges or values that you want to use in the function. These could be cell numbers, for example cell A1, or B38. The parts in brackets [ ] are optional arguments that you may or may not include, depending on your needs.

Table of Contents

SUM

Formula: =SUM(range)

This Excel formula adds up the values in a range of cells. In many industries, financial data is often collected in Excel spreadsheets, and the SUM formula is useful for quickly adding up large amounts of data. For example, a sales team might use the SUM formula to add up their monthly sales figures to determine their total revenue for the year.

A recruiter or talent acquisition team might use the SUM formula to calculate the total number of candidates in their database or the total number of candidates who have applied to a specific job opening.

AVERAGE

Formula: =AVERAGE(range)

Similar to the SUM formula, the AVERAGE formula is useful for calculating averages of large amounts of data. For instance, a project manager might use the AVERAGE formula to determine the average length of time it takes team members to complete specific tasks.

The AVERAGE formula could be used by a recruiter or talent acquisition team to calculate the average length of time it takes to fill a job opening or the average number of candidates who are hired from a specific source (such as a job board or social media platform).

COUNT

The COUNT Excel formula is useful for counting the number of cells in a range that contain numbers. This can be useful in a variety of scenarios, such as calculating the number of hours worked by an employee in a given week or determining the number of products sold by a company in a particular month.

The COUNT formula could be used by a recruiter or talent acquisition team to count the number of candidates who meet specific criteria (such as having a certain level of education or work experience).

Formula: =COUNT(range)

MAX

The MAX Excel formula immediately determines the highest value in a range of cells. This can be helpful for analysing data, such as determining the highest sales figures for a particular product line or the highest number of calls handled by a customer service representative in a given day.

The MAX formula could be used by a recruiter or talent acquisition team to find out the maximum salary that a group of candidates is willing to accept or the maximum number of years of experience that a candidate has.

Formula: =MAX(range)

MIN

The MIN Excel formula returns the lowest value in a range of cells. This can be helpful for analysing data, such as determining the lowest sales figures for a particular product line or the lowest number of calls handled by a customer service representative in a given day.

Similarly, the MIN formula could be used to determine the minimum acceptable salary among a group of candidates, or the minimum number of years of experience that a candidate has.

Formula: =MIN(range)

IF

The IF formula allows you to test a condition and return one value if the condition is true and another value if it is false. This can be useful for a variety of scenarios, such as determining whether an employee has met their performance goals for the quarter or determining whether a product has met a certain quality standard.

The IF formula could be used by a recruiter or talent acquisition team to filter candidates based on specific criteria (such as location or job function) or to identify candidates who meet specific qualifications or requirements.

Formula: =IF(condition, value_if_true, value_if_false)

VLOOKUP

The VLOOKUP formula is useful for searching for a value in the leftmost column of a table and returning a corresponding value in the same row from a specified column. This can be helpful for a variety of scenarios, such as looking up customer information based on their account number or determining an employee's department based on their ID number.

The VLOOKUP formula could be used to look up a candidate's contact information based on their name or to retrieve information about a candidate's work experience or education from a separate database.

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

CONCATENATE

The CONCATENATE formula is useful for joining two or more text strings into one string. This can be helpful for creating mailing labels, combining first and last names into a single field, or creating custom URLs based on specific parameters.

For recruiters, the CONCATENATE formula could be used to combine multiple fields (such as a candidate's first name and last name) into a single field for easier sorting and filtering. You could also combine full names and a job title into a single string to provide a more accurate summary.

Formula: =CONCATENATE(text1, [text2], ...)

LEFT

The LEFT formula is useful for returning a specified number of characters from the beginning of a text string. This can be helpful for a variety of scenarios, such as extracting area codes from phone numbers or returning the first name from a full name field.

When it comes to recruiting, the LEFT formula could be used to extract a candidate's area code or phone number prefix from a larger phone number field.

Formula: =LEFT(text, [num_chars])

RIGHT

Similar to the LEFT formula, the RIGHT formula is useful for returning a specified number of characters from the end of a text string. This can be helpful for a variety of scenarios, such as extracting file extensions from file names or returning the last name from a full name field.

The RIGHT formula could be used to extract a candidate's ZIP code or postal code from a larger address field, thus making it ideal for recruiters who are updating their databases.

Formula: =RIGHT(text, [num_chars])

MID

The MID formula is useful for returning a specified number of characters from the middle of a text string. This can be helpful for a variety of scenarios, such as extracting specific parts of a longer text string or splitting text into multiple fields based on a delimiter.

With regards to recruitment, the MID formula could be used to extract a specific part of a candidate's work experience or education (such as the name of the school or company).

Formula: =MID(text, start_num, num_chars)

TRIM

The TRIM formula is useful for removing extra spaces from a text string. This can be helpful for a variety of scenarios, such as cleaning up customer data to ensure consistent formatting or removing leading or trailing spaces from data imported from other sources.

The TRIM formula could be used to remove extra spaces from fields such as a candidate's name to ensure consistent formatting or email addresses to make sure the email is valid.

Formula: =TRIM(text)

LEN

Short for "length," the LEN formula is useful for returning the length of a text string. This can be helpful for a variety of scenarios, such as determining the length of a password field.

The LEN formula could be used to ensure that fields such as a candidate's password or email address meet specific length requirements.

Formula: =LEN(text)

ROUND

This formula rounds a number to a specified number of decimal places. The ROUND formula could be used to round numerical fields such as a candidate's salary or years of experience to a specific number of decimal places.

Formula: =ROUND(number, num_digits)

DATE

This formula returns the date in a specified format. The info can then be used to calculate a candidate's age based on their date of birth or to calculate the length of time between a candidate's start and end dates at a previous job.

Formula: =DATE(year, month, day) or =DATEVALUE(date_text)

Conclusion

Love it or hate, spreadsheets are here to stay. These formulas in Excel can also be translated over to Google Sheets as well, making this list doubly useful to keep in your back pocket. If you’re curious, you can also check out the full list of Excel functions available.

Are there any other formulas that more useful but not covered here? Drop us a message at community@hackertrail.com and let us know!

Backend Technology Interview Questions

SUM