Many recruiting teams know they need to hire someone with “Excel skills.” They may even add “Excel” to the job description in a vague way.
Unfortunately, Excel skills range widely in complexity. What may seem like advanced Microsoft Excel skills to some are relatively basic to others. And, for recruiters who may not have technical experience with Microsoft Excel, it can be difficult to discern which candidates are true Excel experts, and which have intermediate knowledge.
Few, if any, universities offer concrete Excel curriculums. Recruiters can’t rely on resumes alone to establish someone’s Excel credentials. Hiring an Excel expert requires taking a skills-based approach to recruiting — starting with a deeper understanding of advanced Excel skills and how to assess them.
[Read more: How Skills Based Hiring Can Transform Your Organization]
What are advanced Excel skills?
Excel offers over 475 formulas in its Functions Library. What skills are considered “advanced” depends largely on the industry and role for which you are hiring. However, some skills are more easily mastered through basic Excel training than others.
Basic Excel skills cover the core functions of Microsoft Excel. Someone with basic Excel knowledge is able to make use of the tools in the Home, Page Layout, and View tabs. They are also able to do basic spreadsheet formatting and get different view options, such as zooming in and out. Users can create basic spreadsheets and know how to use the SUM function, COUNT, and AVERAGE.
Intermediate Excel skills include those basic skills plus knowing the difference between an Excel function and a formula. They can understand how Excel functions “behave” when they are used across sheets and copied, pasted, or dragged between cells. Essentially, intermediate Excel users are able to manipulate formulas on Excel spreadsheets to perform basic data analysis. Filters, data tables, and conditional formatting are all examples of intermediate Excel functions.
Therefore, advanced Excel skills are focused on solving complex problems and working more efficiently within the program. An advanced Excel user is familiar with most, if not all of Excel’s functions and can process spreadsheet data with graphs, pivot tables, and automation. These users are able to build spreadsheet models, templates, and dashboards for other users in the organization who are working with limited knowledge.
With more than 475 different Excel tools to learn, advanced knowledge of this program can cover a lot of ground. Here are some common advanced skills you can look for when evaluating candidates.
Top 10 advanced Excel skills you should look for
With so many advanced Excel formulas to choose from, how do you know what to look for? Here are some of the most common top advanced Excel skills you can test in a skills assessment.
1. Data validation
Data validation in Excel is a tool that restricts what a user can put in a worksheet. It’s a useful tool when sharing workbooks with multiple people. The data analyst or person in charge of the Excel workbook can dictate specific rules as to what can be added to a cell. If a user tries to enter invalid data (e.g., letters in a cell restricted to numbers), data validation displays a custom alert explaining the issue.
Data validation sounds simple, but there are eight different data validation options that an advanced user can choose from for any cell. Understanding which validation to use, and how to set it up properly, is considered an advanced skill.
2. Visual Basic for Applications (VBA)
VBA is simply Microsoft Excel’s programming language. VBA has many applications in finance, accounting, and IT, but VBA is used most commonly to make everyday tasks less repetitive via macros. People learn Excel VBA to gain mastery over macros.
Macros are essentially commands that can be recorded, named, saved, and executed as many times and as often as desired. Macros help automate repetitive tasks associated with data manipulation and reporting. For instance, macros can be set up to unmerge all merged cells, save each worksheet as a separate PDF, or protect all worksheets with one command.
VBA and macros empower users to automate actions, clean up and format data quickly, create and modify user forms, and work more efficiently in general. Advanced Excel users will be able to write VBA codes that reduce the amount of manual effort that goes into managing spreadsheets.
3. Pivot Tables
A pivot table is a way for a data analyst to create a visual report from a large dataset. Advanced users create pivot tables to calculate, analyze, and summarize comparisons, patterns, and trends in raw data.
Pivot tables are considered one of the most valuable tools for analyzing data. These advanced charts are able to automatically group matching data; if you are working with a massive dataset, the table will show you quick summaries.
Imagine working with a spreadsheet of all your sales data over the last year. Each line contains the product and amount. With a few clicks, a pivot table could show you all sales summarized by product — a task that would take much longer using an advanced Excel formula.
A pivot table can get even more advanced using Power Pivot. Power Pivot is an Excel add-in that enables Excel to handle large data sets from several sources within one file. Power Pivot can also connect pivot tables to external databases.
4. Index/Match
Index/Match, or INDEX MATCH, is a combination of two Excel functions: the INDEX function and the MATCH function. It’s a more advanced version of the VLOOKUP or HLOOKUP formulas, used to find the relative position of information in the spreadsheet.
The INDEX function returns the value of a cell in a table based on the column and row number. MATCH returns the position of a cell in a row or column. When used together, these formulas can return the value of a cell in a table based on vertical and horizontal and vertical criteria.
Used together, Index/Match helps users parse numbers in different, but similarly arranged datasets. Advanced users could see quarterly revenues for different products over the same time period, for instance.
5. Advanced conditional formatting
Conditional formatting adds visual elements to a database that make it easier to identify trends, analyze data, and improve reporting. Conditional formatting changes the color of a cell depending on the criteria. You can find a specific transaction value in your financial data, for instance, highlighting any sales that are $1000 in blue or any orders from Canada in red. Conditional formatting adds a visual analysis layer that can be useful when dealing with hundreds of rows and columns.
Conditional formatting in Excel offers more than 20 options. While this might seem like visually basic, it can get complicated quickly when using different formulas and Excel functions throughout your spreadsheet.
6. Data simulations
Data simulations are a broad category of advanced Excel skills. Essentially, simulations enable users to forecast or predict certain outcomes. A general manager might forecast net income by simulating purchasing costs. A factory manager might predict optimal plant capacity using a data simulation. Financial planners use this skill to forecast returns.
Excel uses a mathematical technique known as the Monte Carlo simulation. Essentially, an algorithm uses random numbers for different variables and runs the forecast function thousands of times to create an average probability. “It is a technique used to understand the impact of risk and uncertainty in prediction and forecasting models,” wrote Investopedia.
Any data analyst should be familiar with the Monte Carlo technique and able to run simulations for your business.
7. OFFSET
OFFSET is a built-in function that returns a range of a specified number of rows and columns from a reference cell or range. It’s one of the advanced functions that can help improve data analytics.
OFFSET can enable users to isolate a single cell or range of consecutive cells. This gives you insight into specific information in a massive data set. It’s also useful in formulas that require a dynamic range.
8. INDIRECT
The INDIRECT function is used to convert a text string into a valid reference. It essentially locks the specified cell in a formula. This means that users can change a cell reference within a formula without changing the formula itself.
9. Get external data
Often, companies will want to incorporate data points from other Excel files and external sources into their data analysis. Microsoft Excel has the capability to use data that has been stored in an access file, a database, or on the internet. Advanced users can use the “Get External Data” tool to connect outside data into a workbook without having to copy and paste, which can lead to lots of errors (not to mention wasted time).
10. Power Query
Finally, just as there’s a Power Pivot tool, Excel also offers something called “Power Query”. Power Query is a powerful way to clean up your data set. Essentially, Power Query extracts information from different sources, transforms data, and loads it back into the worksheet. This advanced Excel tool can improve data hygiene in just a few clicks.
There are dozens of different Excel features to know and advanced formulas that a candidate can bring to the table. Recruiters often struggle as a result: how can you be sure someone has the advanced excel skills they say they have?
How to assess a candidate’s Excel skills
If you have someone on the hiring team with technical expertise, one way to assess a candidate’s Excel skills is through screening questions. Ask about some of the advanced Excel skills listed above:
- What is a macro, and how is it used?
- How do you prevent someone from editing a cell in your worksheet?
- What does INDEX MATCH do?
- How would you approach cleaning up a data set?
- What’s a pivot table used for?
These screening questions can indicate if a candidate knows what they are talking about. However, it is possible that someone has simply studied the right way to answer your interview questions.
Therefore, the easiest way to assess a candidate’s advanced Excel skills is to put them to the test.
Vervoe’s Advanced Excel skill assessment is a quick and easy way to see if someone has the right Excel skills and move them into the next stage of the hiring process.
Vervoe’s assessment specifically examines formulas, functions, charts, and data management. This ready-to-use assessment focuses on basic Excel skills. You can choose from these pre-formatted questions, or create your own assessment customized to the specific role. This enables you to test conditional formatting, intermediate Excel skills, data tables, and other more advanced functions.
Plus, skill assessments have the added advantage of leveling the playing field for all candidates. Vervoe’s skill tests empower candidates to show their spreadsheet skills no matter what their background, automatically scoring and ranking participants on their performance without eliminating anyone from consideration. For companies, this broadens the field of potential candidates while increasing the likelihood that recruiters will find the right fit the first time.
[Read more: 4 Compelling Reasons to Choose Skills Over Degrees]
Top roles that require advanced Excel skills
Advanced Excel skills are highly sought after in virtually every industry. These roles, in particular, require advanced Excel skills to succeed:
- Data analysts
- Accountants
- Administrative roles, such as office managers
- Market analysts
- Financial analysts and investors
- Digital marketers
- Project managers and coordinators
- Sales teams
- Teachers
- IT staff
One study found that Excel skills are required for 82% of middle-skill positions. Virtually every industry needs workers with basic Excel functions; it’s once you get into the more technical positions that advanced skills come in handy.
[Read more: How To Write Effective Skills-Based Job Descriptions in 4 Easy Steps]
Final thoughts
Excel is one of the Microsoft Office applications that doesn’t seem to be going out of style. Excel experts are in demand to help with everything from sales forecasting to curriculum planning to managing project resources. The ability to transform data, extract key insights, deliver user-friendly reports and dashboards, and incorporate outside information is invaluable to many businesses.
Understanding whether someone has this ability is half the battle. Bringing a skills assessment to your hiring process is the simplest way to find an Excel master for your open position. Vervoe can help: check out our pre-configured Advanced Excel Skills Assessment and explore our library for more options to customize your skill tests.
