The 11 Most Important Formulas to Know for RecOps and Data
3 minute read
All RecOps roles call for, at a minimum, an advanced knowledge of Microsoft Excel and/or Google Sheets. With 450+ functions, it can be hard to know where to start and which ones to learn.
To help, here is a list of formulas we recommend you know:
VLOOKUP / XLOOKUP / HLOOKUP
The staple has always been VLOOKUP to find information across a vertical column and check it with search criteria. XLOOKUP eliminates some of the restrictions of VLOOKUP and offers greater flexibility.
Used heavily in spreadsheets to find information.
- These often appear when checking lists or in other formulas to determine exact match or a relational match.
- Used heavily to checklists
IF especially IFERROR and IFNA
- The IF family is especially effective if you checklists. Using IFERROR or IFNA are helpful when trying to have information stand out.
- Used heavily in checking lists
COUNT especially COUNTIF and COUNTA
- Like the name suggests, it counts values. When building tables without using Pivot Tables, using these can be helpful.
- Allows you to extract and manipulate data from a table or range of cells based on specific criteria.
- Very useful when dealing with a massive table and multiple inputs.
- Allows you to apply a formula to an entire range of cells at once, without having to manually copy and paste the formula into each cell.
- Allows you to important information from another workbook (Excel) or sheet (Google Sheets).
- Extremely useful if you need to filter and manipulate data from a master spreadsheet that many people use at the same time.
- Allows you to retrieve a value or a range of values from a specific location within a table or range of cells. Often used in combination with other functions like MATCH or IF, to search for specific data within a table or range and retrieve the corresponding values.
- When trying to calculate team data, this function is fantastic.
- Allows you to search for a specific value in a range of cells, and return the relative position of that value within the range.
- Can be helpful in various tasks, such as looking up data in a table or validating user inputs.
- Advanced-level formulas to be comfortable with are:
- Allows you to import data from another workbook or sheet spreadsheet and then filter and manipulate that data using SQL-like queries.
- Fantastic if you want to pull in information from a master spreadsheet but only want a few columns.
- Allows you to search for specific data within a table or range and retrieve the corresponding values.
- Powerful alternative to V or XLOOKUP for searching for specific data within a table or range, and retrieving the corresponding values. It can be beneficial in large datasets where manual searching can be time-consuming and error-prone.