The definitive guide to hiring an expert with advanced Excel skills

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.

Source: Exceljet

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.

In this example, Index Match is identifying and matching the population of Japan to F2.Source: Ablebits

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.

In this heat map example, it is shown that temperatures at Central Park are warmer between May and September.Source: Excel-Easy

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

The icon beside the scores indicate whether the score belongs in 0-34, 35-64, or 65 and above group.Source: Contextures Blog

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.

This is a two-dimensional pivot table where you can look further into the specific columns or rows.Source: Excel-Easy

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 requires a deep understanding of VBA, which is why many companies choose to hire a dedicated VBA programmer.

Once the user clicks the “Show High Sales” written with AVBA, Excel will compute the highest sales.Source: Spreadsheeto

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.

An example of data simulation where future profits are projected based on past data.Source: SpreadsheetWEB

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 skills 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.

Comparison between using screening questions and Excel proficiency tests

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:

1. Financial analyst

Financial analysts study financial data to discover investment opportunities, lower operating costs, and write reports on the details of the information. Together with a sales manager, a financial analyst also suggests ways to increase the financial growth of the company.

The advanced Excel skills to look for in a financial analyst are:

• VLOOKUP

• INDEX MATCH

• Data simulations

Example job description text:

“For this role, you need to be familiar with accounting functions in Excel to be able to execute techniques in accounting. You should also have experience with forecast and sales trend tools in Excel. Knowing how to use data simulations and solver is a plus.”

2. Business and market analyst

A business analyst helps bridge the gap between IT and the business with data analytics, enabling business intelligence to be extracted from raw data. A market analyst follows it up by helping the business make informed decisions about the market based on the data.

The advanced Excel skills to look for in a business and market analyst are:

• Pivot tables

• Conditional formatting

• VBA

• Forecast and trend Excel tools

Example job description text:

“For this role, you need to have a strong understanding of Excel, with expert proficiency in pivot tables, conditional formatting, VBA, forecast & trend tools.”

3. Data analyst

A data analyst supports a company in decision-making by inspecting, transforming, and modeling data to discover useful information and come up with a conclusion. In addition, they also identify trends and make predictions about the future.

The advanced Excel skills to look for in a data analyst are:

• Pivot tables

• VLOOKUP, INDEX, and MATCH functions

• Forecast and trend Excel tools

Example job description text:

“For this role, you need to be an expert in Excel’s analysis tools. You should also have mastery over analysis functions and lookup functions like VLOOKUP, INDEX, and MATCH. It would also be great if you know how to create advanced charts and use forecast & trend tools.”

4. Cost estimator

A cost estimator is responsible for collecting and analyzing data to estimate the time and resources needed to manufacture a product. Naturally, a cost estimator has to be good at reading, analyzing, and maintaining records of estimated and actual costs.

The advanced Excel skills to look for in a cost estimator:

• Pivot tables

• VLOOKUP, INDEX, and MATCH functions

• Conditional formatting

Example job description text:

“For this role, you need to know how to create and modify pivot tables and report data from them. You should also be familiar with VLOOKUP, INDEX, and MATCH Excel functions. It will also be best if you have mastered conditional formatting in Excel.”

5. Information clerk

An information clerk performs clerical tasks like maintaining records, collecting data, and helping customers with data and information management.

The advanced Excel skills to look for in an information clerk:

• VLOOKUP, INDEX, and MATCH functions

• Conditional formatting

• Pivot tables

Example job description text:

“For this role, you need to have a strong understanding of Excel’s VLOOKUP, INDEX, and MATCH functions. You should also know how to use conditional formatting and advanced charts in Excel.”

6. Accountant or auditor

Accountants and auditors analyze financial statements and documents to make sure it’s accurate and up to date. They also file important financial data and submit tax returns.

The advanced Excel skills to look for in an accountant or auditor role:

• Accounting functions

• VLOOKUP, INDEX, and MATCH functions

Example job description text:

“For this role, you need to have a strong understanding of Excel’s accounting functions. You should also know how to use VLOOKUP, INDEX, and MATCH functions in Excel.”

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 pre-employment screening questions 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. Sign up for your free plan with us, or book a free live demo with a member of the team.