How To Hire An Advanced Excel Expert

Microsoft Excel skills are almost a meme within the recruitment world: everyone’s got them! 16% of the world use Microsoft Office and “Intermediate Excel skills” is such a common line on CVs as to have become almost meaningless. But what if you’re looking for more than basic competence—what if your organization needs someone with advanced Excel skills? Someone who can retrieve data vertically, index match with the best of them and apply advanced conditional formatting to tables? And what if you yourself don’t have advanced Excel skills?

If you’re not an Excel expert, it might feel difficult or even impossible to hire someone with the advanced Excel skills you need. But it’s not! In this article, we’ll break down what advanced Excel skills are with plenty of examples and real-world case studies, as well as how you can assess Excel skills in candidates even if you’re not an expert yourself. Let’s get started!

What are advanced Excel skills?

First thing’s first, let’s make sure we all understand exactly what advanced Excel skills are. The way your candidate uses Excel will always depend upon their professional background and the specific needs and duties of their department or job role. But in general, advanced knowledge of Excel means that your candidate can use spreadsheets, graphing, tables, calculations and automation to process large quantities of data both efficiently and accurately.

How does this compare with other Excel users? Let’s break it down.

‍Basic Excel skills mean that your candidate can use common Excel keyboard shortcuts, cell references, style and data ranges. They should be able to offer different view options, including zooming in or out and showing formulas, as well as basic spreadsheet formatting.

‍Intermediate Excel skills mean the ability to use more advanced formulas and functions, including SUM, AVERAGE and SUMPRODUCT, as well as having a working knowledge of charts. Intermediate Excel users will be more efficient with their Excel usage.

Advanced Excel skills mean that your candidate isn’t just able to use advanced formulas and functions—they’ve mastered them. Advanced Excel users should be able to use formulas, Visual Basic for Applications (VBA) programming and other complex Excel features. They can utilize Excel for more advanced tasks, like data analytics and simulations.

Which job roles require advanced Excel skills?

Now that you know what advanced Excel skills are, who needs them? For lots of roles, intermediate or even basic Excel skills will be just fine to ensure that your candidate performs successfully. However, advanced Excel skills are often extremely useful for a company, whether you want to generate pivot tables or track sales ledgers and more. Here are some roles which require candidates with advanced Excel skills to perform.

Financial analysts

It’s all in the name: financial analysts analyze financial data to discover investment opportunities, lower operating costs and provide reports to their colleagues and managers. They are expected to suggest ways to increase the financial growth of the organization. Advanced Excel skills are crucial—look for comfort with VLOOKUP, INDEX MATCH and data simulations as a start.

Business and market analysts

This is a role that bridges the gap between IT and business development. A business and market analyst uses data analytics to extract business intelligence from raw data, and follows up by helping the organization make informed market decisions based on data. A business and market analyst will need advanced Excel skills like pivot tables, conditional formatting, VBA and forecast and trend Excel tools.

Data analyst

As data analysts support company decision-making by inspecting, transforming and modeling data, advanced Excel skills are a must. Data analysts discover useful information, come up with conclusions, identify trends and make predictions about the future—all from raw data! They’ll need advanced Excel skills including pivot tables, VLOOKUP, INDEX and MATCH functions, and forecast and trend tools.

Cost estimator

Cost estimators spend their time collecting and analyzing data to estimate the time and resources needed to manufacture a product. They’ll need to read, analyze and maintain records of estimated and actual costs, using advanced Excel skills including pivot tables, VLOOKUP, INDEX and MATCH functions, and conditional formatting.

Information clerk

An information clerk is another role that involves working intensively with data, collecting and maintaining records and helping customers and colleagues with data and information management. Look for advanced Excel skills including VLOOKUP, INDEX and MATCH functions, conditional formatting and pivot tables.

Accountant or auditor

No surprise here! Accountants and auditors analyze financial statements and documents, file important financial data and submit tax returns. They frequently rely on Excel to ensure data is accurate and up to date. Look for advanced Excel skills including accounting functions and VLOOKUP, INDEX and MATCH functions.

Examples of advanced Excel skills

Now that you know who you’re hiring and what advanced Excel skills are, let’s take a closer look at some of the most important Excel skills. As we’ve mentioned in the examples above, lots of these advanced Excel skills crossover into different job roles. 

It’s important to know the most important advanced Excel skills: you can mention them specifically in the job description to ensure you’re attracting skilled candidates, decide which skills you need for which job roles, and prepare to assess them as part of your hiring process. Here, we’ll break down some examples of advanced Excel skills that you can understand even if you’re not an Excel master!

VLOOKUP

VLOOKUP stands for vertical lookup: it’s a function to discover a value in a table and retrieve data from a specific column. As such, it “retrieves data vertically” and is a valuable skill because it helps merge multiple sets of information into one. Here’s how it looks:

VLOOKUP is a practical tool which can help with lots of everyday work tasks, including coming up with the corresponding interest rate flow in a financial model with a debt schedule, looking up sales for a specific item, calculating sales bonuses and more.

INDEX MATCH

INDEX MATCH is actually a combination of two Excel functions. Can you guess which? That’s right, it’s INDEX and MATCH! INDEX gives you the value of a cell at any location in a list or table, while MATCH returns the position of a cell in a row or column. Here’s an example where INDEX MATCH identifies and matches the population of Japan to F2:

INDEX MATCH is similar to VLOOKUP in use, although it can be more flexible, because the two combined functions allow a formula to become dynamic. You could use it in practice for tasks like returning a specific agent’s sales figures per month, creating a financial summary and making a purchase order with a price list.

Advanced conditional formatting

Advanced Excel users utilize conditional formatting to design spreadsheets and add visual analysis layers on a data set. Conditional formatting is also commonly used to create advanced tools. One advanced tool example is a heat map, which represents data with values as colors, usually on a warm-to-cool color spectrum, to show which values are larger or smaller. Here’s a heat map example showing that temperatures at Central Park are warmer between May and September:

Another useful tool you can create with advanced conditional formatting is Harvey bubbles. Harvey bubbles, sometimes also called Harvey balls, are graphic representations of qualitative information which indicate the degree to which an item meets a specific criterion. For example in this Harvey ball example, the icon beside each score indicates whether the score belongs in 0-34, 35-64 or 65 and above group.

Advanced conditional formatting can also flag data entry problems, show duplicates and highlight rows with the most sales.

Pivot tables

Pivot tables provide a powerful way to create reports of large data sets. It offers you the opportunity to look at the same data from different perspectives, making it possible to draw invaluable business conclusions. For example, in this two-dimensional pivot table example you can look further into the specific columns on rows:

Using pivot tables, you can group data into categories, filter data to include or exclude specific categories and even build charts. That makes pivot tables useful for building databases of employees and products, as well as sales records and other significant reports.

Macros and Visual Basic for Applications (VBA)

A macro is essentially a small computer program, written using VBA language stored in modules. The macro can do an action or set of actions when it’s turned on. For example, in this macro, when the user clicks “Show High Sales”, Excel will compute the highest sales.

VBA macros are extremely useful for automating tasks. But they’re complex to write and use, meaning that only advanced Excel users have the skill. Using VBA macros, you can clean up and format data quickly, list data on all sheets, automate properties and actions in pivot tables, create and modify user forms, create systems to make, update or change Excel files and more.

Data simulations

Advanced Excel users create data simulations by making a mathematical model which represents a system’s characteristics. There are specialized software programs which create simulations, but an advanced Excel user can manage it all on their own! However, they’ll have to be able to use advanced features including random number generation, statistical functions, data tables and what-if analysis tools. For example, here’s a data simulation where future profits are projected based on past data:

Data simulations are extremely useful for workplace tasks like simulating online ad activity to determine possible average sales, finding sales patterns based on recent data and modeling customer lifetime value based on product-purchasing trajectories.

How to assess candidates on their advanced Excel skills

It’s easy to declare that you have advanced Excel skills on a CV. Some candidates might be exaggerating or simply misunderstanding their own ability, so for roles where advanced Excel skills are important, it’s crucial that you test a candidate’s Excel skills as part of the recruitment process. 

The best way to assess candidates on their advanced Excel skills involves a mixed method approach which includes questions, tasks and tests. Here’s how to make sure you get an accurate picture of each candidate’s Excel skills.

Screening questions

Even if you’re not an Excel expert yourself, you can still get a sense of your candidate’s proficiency with Excel by asking a range of screening questions. You could also get a colleague who does have advanced Excel skills to assist in this part of the interview process, either by sitting in on the interview or checking the candidate’s answers after.

Some examples of good screening questions include:

• How many years of experience do you have with Excel?
• What is the hardest project you ever completed in Excel? Tell us how you approached it and the result.
• Do you have experience with VBA macros? If so, please explain when and how you’ve used it to improve data processing.
• When would you prefer to use VLOOKUP over INDEX MATCH?

Tests and assessments

A good way to see how a candidate applies their advanced Excel skills is via a proficiency test or a sample assessment. Talent assessment tests are a great way to ensure that your candidate can deliver what they promise, and you could approach this in a range of ways.

• A hard skills assessment test designed to assess Excel and other hard skills
• Generic Excel proficiency tests which ask your candidate to perform a number of advanced Excel functions, such as pivot tables or advanced conditional formatting
• A specific assignment designed to replicate the kind of work the candidate will be performing at your organization. For example, if you’re hiring a financial analyst you could ask them to create a data simulation based on some of your organization’s recent (non-confidential) data.

Need some help?

Assessing advanced Excel skills requires the best employment tests as well as data-driven insights into each candidate. HiPeople can help you with that: our smart hiring tools ensure that you hire the right person every time. Book a free demo today.