The definitive guide to hiring an expert with advanced excel skills

The definitive guide to hiring an expert with advanced excel skills

Our blog The definitive guide to hiring an expert with advanced excel skills
advanced excel skills guide

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

INDEX MATCH
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.

Advanced conditional formatting
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 needs a deep understanding of VBA.

VBA Macro
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.

data simulations
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 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:

Preview Question excel test TestGorilla

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:

Screening questions vs practical exams

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:

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

The best advice in pre-employment testing, in your inbox.

No spam. Unsubscribe at any time.
Something wrong occured
Thank you, you have successfully registered !
Close