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 expert. Fortunately, there are ways to find these candidates, even if you're not an Excel master, like using an Excel proficiency test. 

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. After that we'll cover Excel proficiency tests and other ways to assess Excel skills.

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 ideal candidates should have. Then you can choose the right interview questions and Excel proficiency tests to be sure your candidates have what it takes to get the job done.

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 of 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 Excel proficiency tests to assess skills

An Excel proficiency test can show you how well a candidate applies advanced Excel skills.

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

Opting for free exams found online means that your 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. If that's not an option, TestGorilla has a general Excel proficiency test, an advanced Excel proficiency test, and a test to gauge a candidate's skill in financial modelling with Excel.

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

General Excel proficiency test

TestGorilla's general Excel test evaluates a candidate’s ability to read and interpret Excel spreadsheets, perform basic calculations, and manipulate tables. That way you can hire candidates who understand how to use Excel as part of the Microsoft Office suite.

This test is a good option if you're looking to hire managerial assistants, executive assistants, or other job roles that require a good understanding of the basic functionality of Microsoft Excel.

You can take a look at some sample questions in our Test Library. If you like what you see, sign up for a free trial.

Advanced Excel proficiency test

The Advanced Excel proficiency test evaluates a candidate's mastery of a wide range of Excel features and formulas to help you identify candidates who are skilled in using Excel to organize data and draw insights from them using Microsoft Excel.

This test is a good way to make better decisions when hiring finance professionals, marketers, salespeople, executives, data scientists, and other job roles that require an advanced command of Microsoft Excel.

If this is what you're looking for take a look at some sample questions in our Test Library. If you like what you see, sign up for a free trial.

Financial modeling Excel proficiency test

The financial modeling Excel proficiency test evaluates a candidate's essential finance and valuation skills and their ability to create and maintain financial models using Excel. It's designed to help you hire finance professionals who need to work with Excel frequently.

Consider this test if you're hiring finance professionals (controllers, analysts, consultants) who need to have solid financial modeling skills in Excel.

You can get a better feel for what this test looks like by reviewing some sample questions in our Test Library. If you like what you see, sign up for a free trial.

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.