Hiring an employee with advanced Excel skills can be challenging. Not only do you have to identify which advanced Excel skills their job will require, but you also have to find a way to test these skills in your candidates. Things get even more complicated if you’re not an Excel whizz yourself. 

Figuring out if a candidate has advanced Excel skills when you’re not an expert yourself may be tricky — but it’s not impossible. There are ways to check the expertise of candidates on the required skills.

In this article, we’ll talk about the Excel skills that are considered advanced, the combination of skills needed for certain job roles, and how to assess whether a candidate has the needed skills or not. Let’s dive right into it!

What are advanced Excel skills?

Advanced knowledge of Excel means possessing the ability to use spreadsheets, graphing, tables, calculations, and automation efficiently to process large quantities of data relevant to business tasks.

The way Excel is used depends on the needs and duties of specific departments or job roles.

Let’s take a look at the three tiers of Excel skills and then we’ll expand on the specific skills an advanced Excel user should have.

Basic Excel skills include common Excel keyboard shortcuts, using cell references, styles, and ranges with data. Different view options like zooming in or out and showing formulas, and basic spreadsheet formatting also belong to the basic skillset for Excel.

Intermediate Excel skills include more advanced formulas and functions like SUM, AVERAGE, and SUMPRODUCT for efficient use of spreadsheets. Intermediate users should also have a working knowledge of charts.

Advanced Excel skills are all about mastery over formulas, VBA (Visual Basic for Applications) programming, and other Excel features for handling complex tasks. Experts can use Excel for more advanced purposes like data analytics and simulation.

6 advanced Excel skills to look for

There are a lot of things you can do with Excel — but the following six skills are the ones that separate the real experts from the rest. Familiarizing yourself with these skills will help you determine which ones your idea candidates should have.

1. VLOOKUP

VLOOKUP (vertical lookup)  is a function to look up a value in a table and retrieve data from a specific column. Basically, it retrieves data in a table vertically using a lookup value.

VLOOKUP is a valuable skill to have as it helps merge multiple sets of information into one — like a merger tool.

In practice, VLOOKUP is useful for:

• Coming up with the corresponding interest rate flow in a financial model with a debt schedule
• Looking up the quantity sold for a specific item
• Calculating sales bonuses

2. INDEX MATCH

The INDEX MATCH combination is made up of two Excel functions — INDEX and MATCH. The former returns the value of a cell at a given location in a list or table. The latter returns the position of a cell in a row or column.

When combined, these functions allow a formula to become dynamic, like in the case of a two-way lookup. Its uses are similar to VLOOKUP although INDEX MATCH is more flexible.

In terms of usage, here are examples:

• Returning the sales figures for a specific month for a specific agent
• Creating a financial summary
• Making a purchase order with a price list

3. Advanced conditional formatting

Conditional formatting is a great way to design a spreadsheet as it gives a user a quick way to add a visual analysis layer on a data set. It can also be used to create advanced tools like heat maps and Harvey bubbles.

A heat map is a representation of data with values represented as colors, usually a warm-to-cool color spectrum, to show which values are larger or smaller than the others.

Harvey balls are graphical representations of qualitative information to indicate the degree to which an item meets a specific criterion.

Here’s more of what advanced conditional formatting can do:

• Flag data entry problems
• Show duplicates
• Highlight rows that have the most sales

4. Pivot tables and reporting

A pivot table is a powerful Excel feature used in creating reports of large data sets. In a sense, a pivot table is like an ordinary report table with one vital difference — you can look at the same data from different perspectives.

A user can group data into categories, filter data to include/exclude categories, and even build charts with pivot tables.

In real life, pivot tables can be used to build:

• Employee database
• Product database
• Project sales record

5. Macros and VBA

A macro is a small computer program that will do an action or a set of actions once turned on. Macros are written using VBA (Visual Basic for Applications) language stored in modules. Using a macro needs a deep understanding of VBA.

VBA macros help automate tasks. However, because of its complexity, not every Excel user has this skill.

Advanced Excel users apply VBA Macro to:

• Cleanup and format data quickly
• Listing data on all sheets
• Automate properties and actions in pivot tables
• Create and modify user forms
• Creating systems to make, update, or change Excel files

6. Data simulations

A simulation usually entails making a mathematical model that will represent the characteristics of a system. While there are specialized software programs today for simulations, someone with advanced skills can use Excel tools for simulation.

To do a simulation in Excel, a user needs to know how to use features like:

• Random number generation functions
• Statistical functions
• Data tables
• What-if analysis tools

In terms of application, here are some use cases of data simulations:

• Simulate online ads activities to determine possible average sales for a period
• Find sales patterns based on recent data
• Model customer lifetime value based on their product-purchasing trajectory

How can you assess candidates on their Excel skills?

You may receive applications from a lot of candidates who consider themselves experts in Excel. After all, Excel skill is common considering that 16% of the world’s population use Excel.

To make sure you hire a candidate with the most applicable advanced Excel skills, you’ll need to test them. Let’s go through the different ways to scrutinize the competency of your candidates.

Testing spreadsheet acumen with screening questions

Screening questions help you manage the volume on incoming applications by showing you which applicants have the required skills.

This enables you to sift applicants from the start. However, you can also choose to screen during the application process or even after the selection process. But pre-screening applicants will definitely save time in the long run.

There are three types of screening questions:

1. Text or open-ended
2. Yes/No
3. Multiple choice

For example, you may ask applicants:

• How many years of experience do you have in Excel?
• What do you consider your hardest project completed in Excel? Explain how you approached it and the results.
• Any experience with VBA macros? If so, tell us when and how you've used it to improve data processing.
• In what circumstances do you prefer VLOOKUP over INDEX MATCH?

Yes/No questions are helpful for getting a quick overview of candidates’ skills and comparing your candidates on clear parameters. Yes/No questions are much more specific than open-ended questions and allow to zoom into specific knowledge.

Sample questions include:

• VBA macros are used to automate the processing of data. True or false?
• VLOOKUP and INDEX MATCH are the same. Yes or no?
• You can create heat maps with advanced conditional formatting. Yes or no?

One of the best and commonly used types of screening questions is multiple choice. With the right questions and a timer, you’ll be able to test a candidate’s skills in Excel.

Here’s an example from TestGorilla’s very own Excel test:

TestGorilla has a massive test library. Explore the test library and search for specific tests and see test recommendations. You can also set up your own custom questions.

Conducting practical exams to assess skills application

Practical exams  can show you how well a candidate applies the required advanced skills.

Ideally, this should be done either after the initial screening right before the interview or after a round of interviews.

Opting for free practical exams found online means you candidates may also be able to find the answers to these online. If you want to avoid this, it’s better to bring in an expert who can design an exam just for you.

Here’s an example of a free advanced Excel practical exam from eExcel LTD:

You can ask an expert from your company to build an exam for your candidates. Let them know what you want to test and come up with an appropriate solution together and an answer sheet so you can check the results as they come in. 

Alternatively, you can find free or paid tests online.

Comparison between using screening questions and practical exams

Screening questions and practical exams offer different advantages and drawbacks.

Let’s look at each one in the following table:

With the right combination of assessment methods, you’ll be able to correctly determine if the candidate really has the required skills.

What job roles require Excel skills?

Excel has many uses — from generating pivot tables for accounting ledgers, to tracking sales data of a company.

Depending on the job role, you want to look for specific advanced Excel skills from candidates. Here’s a list of some of the job roles that require Excel skills and what skill set you should look for:

Hire the right Excel expert

As we’ve seen, different job roles require different advanced Excel skills. The good news is that you don’t need to be an Excel expert yourself to assess candidates on their expertise.

With a combination of screening tests and tailored practical exams, you can assess whether a candidate has the advanced skills your open role requires. These objective results will help you evaluate each candidate individually and rank all your candidates based on expertise to make the right hiring decision for your role.