Hiring An Excel Expert With Advanced Excel Skills

Many times, HR knows they need to hire someone with “Excel skills” and they may also add it to the job description and promote it actively. But what they do not realize is that Excel skills vary in complexity. What may seem like basic skills to some are advanced for others.

And recruiters who do not possess technical knowledge of Excel can have a hard time discerning which candidates are true Excel experts. It is not fair to rely only on the applicant's Excel credentials in the resume during the selection process.

Hiring an expert requires a skills-based approach, which starts with a deeper understanding of advanced Excel skills. The good news is that there are ways to check candidates' expertise in the spreadsheet space.

In this article, we will discuss the jobs that require Excel knowledge, top advanced Excel skills and how to best assess whether a candidate is hands-on with Excel. Let us go:

What are advanced Excel skills?

Advanced Excel skills include processing spreadsheet data with pivot tables, graphs, and automation. The user can design spreadsheet models, dashboards, and templates for other users in the business working with limited knowledge.

Someone with basic Excel skills can utilize the tools in the Home, Page Layout, and View tabs. They can perform elementary spreadsheet formatting and know how to use functions such as SUM, AVERAGE, and COUNT.

On the other hand, users with intermediate Excel skills understand how Excel functions can be used across sheets and copied, pasted, or dragged between cells. They can manipulate formulas on Excel spreadsheets to perform basic data analysis.

Which jobs require the use of advanced Excel skills?

Excel is used in many industries, with financial services relying on it the most. The tool is also used for office administration, project management, and accounting. Here are five job roles where knowing how to use Excel is necessary:

• A data analyst examines and presents data as useful information, helping companies make better-informed decisions. They also identify data trends and do forecasting. A data analyst uses advanced Excel functions such as PivotTables, INDEX, and MATCH.
• An accountant uses Excel to create financial statements, perform tax return calculations, and close books of accounts. Knowing auditing and formatting functions and formulas is a must for them.
• A financial analyst tracks a company’s financial performance against market conditions, runs budgeting analyses and manages cash flow. They use Excel features such as data manipulation, PivotTables, formatting, and LOOKUP to create forecast reports.
• Administrative assistants provide support for keeping operations running smoothly. They schedule meetings, do bookkeeping, organize document systems, and more - all of which require intermediate (if not advanced) Excel skills.

Hiring an Excel expert: 12 advanced Excel Skills to look for

If you have read till here, you will agree there are many things one can do with Excel. But knowing essential Excel skills will give you an edge when you actually get down to determining your ideal candidates. Here are the 12 advanced Excel skills you must know about:

1. OFFSET

OFFSET is a built-in function that enables users to isolate a single cell or range of consecutive cells. It is helpful in formulas requiring a dynamic range and is one of the advanced functions that helps improve data analytics. Here is the Syntax: OFFSET( reference, rows, cols, [height], [width]).

source

2. INDIRECT

The INDIRECT function converts a text string into a valid reference. It locks the specified cell in a formula. That means the users can change a cell reference within a formula without changing the formula.

For instance, if you have number 3 in cell A1 and text A1 in cell C1, put the formula “=INDIRECT (C1).” The INDIRECT function refers to the value in cell C1, which is A1. The function is routed to cell A1 where it picks the value to return, which is number 3.

source

3. VLOOKUP

VLOOKUP or vertical lookup is a function to “look up” a value in a table and fetch data from a particular column. Knowing this is valuable because VLOOKUP helps merge multiple datasets into one - similar to a merger tool. In practice, with VLOOKUP, you can search for the quantity sold for a specific item or calculate sales bonuses.

source

4. Sort & Filter

As the name suggests, filters can temporarily hide some of the data in a table, so the user views the data they want to see. The option to “Sort & Filter” is in the Data tab. You can also use built-in comparison operators like “top 10,” “greater than,” or “AutoFilter” to filter by number values and cell color and hide everything else.

5. INDEX MATCH

This is a combination of two Excel functions - INDEX and MATCH. While INDEX returns the value of a cell at a given location in a table or list, MATCH returns the cell's position in a column or row. When combined, the formula becomes dynamic.

For example, the MATCH function looks up the value of 53 - on position 5 - in the range B3:B9. On the other hand, the INDEX function returns a specific value in a one-dimensional range. In this case, it returns the 5th value in the range E3:E9.

The next step involves replacing the value 5 in the INDEX function with the MATCH function to look up the salary of ID 53, which is $58,339.

source

6. Power Query

Power Query offers an effective way to clean up your data set. It extracts information from different sources, transforms the data sets, and loads it back into the worksheet. This advanced Excel tool improves data hygiene in a few clicks.

7. Data validation

Data validation in Excel is a tool that restricts what a user can add to the worksheet. This is useful when multiple people use the worksheet. If a user tries to enter invalid data (for example - numbers in a cell restricted to letters), data validation displays a custom alert highlighting the issue. Understanding which validation to use and how to set it up correctly is often considered an advanced skill.

8. Macros and VBA

A macro is a small computer program that does an action or a set of actions once turned on. It is written using Visual Basic for Applications (VBA) language stored in modules. VBA macros automate tasks, clean and format the data quickly, create and modify user forms, and more. Due to its complexity, not every Excel user may have this skill.

9. Data simulations

A simulation typically involves creating a mathematical model representing a system's characteristics. An example of data simulation involves projecting future profits based on past data. To do a simulation in Excel, the user must know how to use statistical functions and what-if analysis tools and create data tables. It is a highly advanced skill that data scientists usually possess.

10. Get External Data

Users may want to incorporate data points from other Excel files and external sources for data analysis. They can then use the “Get External Data” functionality to connect outside data into a workbook without copy-pasting, which can otherwise waste time and cause errors.

11. Pivot tables and reporting

A pivot table is used for creating reports of large data sets. In a general sense, it looks like an ordinary report table. The only difference is you can view the same data from different perspectives.  A pivot table is two-dimensional, where you can move further into specific rows or columns.

You can group the data into categories, build charts with pivot tables, and filter data to include or exclude categories. Pivot tables are handy for creating product databases, project sales records, and employee databases.

12. Advanced conditional formatting

Conditional formatting allows the user to add a visual analysis layer over a data set. It can also be used for creating advanced tools like Harvey balls and heat maps.

Harvey balls are graphical representations of qualitative information for indicating the degree to which an item meets a specific criterion. On the other hand, a heat map represents data with values represented as colors - usually a warm-to-cool color spectrum - to indicate which values are smaller or larger than others.

Advanced conditional formatting helps show duplicates, flag data entry problems, and highlights rows with the most sales (as an example).

So, what is the best way to assess Excel skills in potential employees?

Now that you have a fair bit of an idea about what Excel is and its functionalities, screening candidate resumes will not be so challenging anymore. Plus, the Excel test uses scenario-based MCQ questions to evaluate a candidate's ability to work with large datasets in Excel, utilize advanced formulas, navigate multiple spreadsheets, and create reports.

The test screens for the ability to sort/filter data, create dashboards using charts, analyze data using PivotTables, record, modify or write macros, use data tools like sorting, subtotaling and filtering, deal with errors, and provide forms for users' inputs.

The Adaface Excel Test is the most accurate way to shortlist data analysts. The actual questions on the Excel test are non-googleable. To find out more about what we do, check out Adaface.