Excel Assessment: Overlooked Part of Recruiting

Most business software requires a big financial commitment, not to mention time spent on training your workforce to use something new. Although there are advantages to considering other software, the ease of use Excel offers is unmatched – especially when looking at program costs.However, finding employees that have the advanced Excel skills necessary for some of the more complex operations your business needs can be more challenging than it seems.

That’s why Excel assessments need to be an integral part of your recruitment process. They will help the company overcome the exaggerated self-surveys of each applicant’s Excel knowledge.  You won’t have to rely on resumes, to assess someone’s knowledge of Excel. Use the test to assess the skills that the employee will need for a particular task they will do on a daily basis.

In this article, we’ll cover:

• What Excel Skills Assessment Can Show You
• How to Use Microsoft Excel Assessment to Find Advanced Users
• Why You Need an Excel Test for Interviewing Candidates
• Screening Questions: What to Ask
• What Kind of Excel Test to Administer

What Excel Skills Assessment Can Show You

Excel is a surprisingly powerful and complex tool and users with different skill levels can get different types of usability out of it. That’s why it’s useful to have different Excel tests to assess on which level an employee can use Excel and for which job role they could be best suited.

There are three common tiers of Excel usage. Here we’ll explain what they are and expand on how they fit into roles within a modern corporate setting.

Basic Excel skills

These skills include common tasks such as using cell references, styles, and ranges with data. A person with basic Excel skills should also know how to use zooming in or out and showing formulas, as well as spreadsheet formatting.

Intermediate Excel skills

Intermediate Excel skills include more sophisticated tools offered by the program. They are, among others: functions such as SUM, AVERAGE, and SUMPRODUCT. Those who are on this level of Excel knowledge should also know how to make charts.

Advanced Excel skills

Advanced skills that can be tested by Excel assessment during the job interview include formulas, Visual Basic for Applications, programing, and data analytics and simulation.

Using the Microsoft Excel Assessment to Find Advanced Users

The first step for using an Excel assessment is to know what you’re looking for. Excel has a variety of functions and finding users with advanced skills means knowing what the actual job will look like on a day-to-day basis.  You can ask current employees working in roles that extensively use Excel as to what tools and features are most important for completing their projects.

After doing so, you will be able to provide the candidates with the right difficulty level of test and only test them about the skills they will actually need. This doesn’t mean that the test can’t be broader than the job description of the person doing it. An employee can grow on the job and the Microsoft Excel assessment should accommodate that fact.

Which Functions Should You Be Testing for?

• VLOOKUP stands for vertical lookup and is one of the advanced tools that a Microsoft Excel user needs to know. It’s used to retrieve data from a column in an Excel sheet. It’s somewhat similar to a merger tool because it merges multiple sets of data into one set of information.

VLOOKUP is commonly used for:

• • coming up with a corresponding interest rate in a debt schedule model
  • looking for the number of items sold
  • calculating bonuses for the sales team
• Index match is a combination of “index” and “match” tools. You use the first part to return a value of a cell, based on where it is in a list, and the second returns a position of a cell in a row or column.  It’s similar to VLOOKUP but more flexible.

Index match is commonly used for:

• • creating a detailed financial summary or analysis
  • making a purchase order with a price list
  • searching for sales for a specific date or person
• Advanced control formatting is a tool used to visually represent data and provide information at a glance. The most common ways of doing that are “heat map” and “Harvey bubbles”.

A heat map presents data on a warm to cool color spectrum, thus allowing us to see which values are higher than the others. It’s also useful for noticing patterns within data.

Harvey bubbles are a visual representation indicating that a piece of data meets certain criteria.

Advanced control formatting is commonly used for:

• • flagging specific data
  • showing duplicates in a set of data
  • highlighting certain rows within a range

Using Microsoft Excel Test to Find Advanced Users

A test used to find the best Microsoft Excel users should take into account certain advanced tools that a job candidate should know how to use.

• Pivot tables and reporting: a pivot table is used to create reports based on large data sets. It’s similar to an ordinary report table but it’s more powerful since it allows you to look at the same data from a different perspective. Therefore, this tool shows how one set of data relates to another.

Pivot tables are commonly used for building:

• • employee databases
  • product databases
  • project sales records
• Macros and VBA: a macro is a small computer program that performs a task when it’s activated. Macros are written using Visual Basic Application (VBA). The main goal of this feature is to automate the task that is often done or that tedious to do.

Use them when:

• • formatting data quickly
  • listing data on sheets
  • automating actions
• Data simulations: a simulation is when you create a model that will represent a characteristic of a system. There are also separate programs that you could use to do, but Excel will do as well, at least for some simulations. An Excel assessment test may be able to determine that a user knows how to use:
  • random number generator functions
  • data tables
  • what-if analysis

In a practical sense, these tools could be used to:

• • run and simulate online ads, to figure out potential sales
  • identify sale patterns and trends
  • create models of a customer lifetime value

Why You Need an Excel Test for Interviewing Candidates

At first glance, it may not be obvious that you should test for Excel skill.  After all, the initial step toward hiring an employee is usually reviewing their resume and cover letter. Most candidates describe their Excel skills as intermediate to advanced. They usually don’t provide details about what kind of tools they can use and how well.

 

This is where an Excel assessment can be helpful during the interview stage. The test and the interview complement each other in determining what the employee can do in real-life settings.

Screening Questions

When a company is working with a lot of potential candidates, it’s best to use screening questions to eliminate and screen potential candidates based on how well they understand certain Excel concepts. There are different ways to phrase these questions, depending on the role of the interviewer. One important technique is to use open-ended questions. These questions let the candidates talk about their Excel knowledge and experience, with limitations.

What Kind of Questions to Ask?

The screening questions should be taken as a part of a larger Excel test for employment. A few questions that you might want to ask include:

• How many years have you been using Excel professionally?
• What’s the most difficult Excel project you’ve been working on?
• Take us through the process that has led you to complete that task.
• What’s your experience with using Macros?
• When is Index match better than VLOOKUP?

It’s also possible to use YES/NO questions to eliminate those who embellish their resumes. For instance, a good question that does, can be VLOOKUP and Index match are the same or VBA macros are used to automate data processing?

Using an Excel Test for Employment?

After you’ve determined what kind of skills you’re looking for, as well as the screening questions, you can start with the actual Excel skills assessment.  There are different tests available that are created for a variety of different skill sets and positions.

One approach is to administer the test after the screening questions and before the interview. Purchasing tests from a test vendor with great reviews is a better option than using those tests that can be found for free online. Purchased tests can also be more statistically reliable than those created internally in your organization.

General Excel Assessment

A basic excel test for employment will test how well the candidate can read and interpret the spreadsheets and the data seen in Excel.

If your main goal is for the employee to understand Excel and maneuver within it, that’s the test for you. Therefore, this may the best option for administrative and executive assistants.

Advanced Excel Proficiency

This more complex test is there to assess the use of Excel for more complex tasks. This is a good Excel test for interview candidates for positions such as finance professionals, marketers, salespeople, executives, data scientists, and other similar roles.

They should know Excel formulas, as well as how to work with tables, which is a tool that’s often used in these roles. It’s also helpful to make the results of this test a part of the subsequent interview. That way you can focus on the areas of the test that a candidate has failed in or done exceptionally well in.

Test for Financial Modeling

This sophisticated assessment is more than just a basic Microsoft Excel test. It does test how well a candidate knows Excel, but it focuses on the financial models that can be created by using this tool. Financial controllers and analysts should use this because they will work in Excel on a day-to-day basis.

It’s the most advanced Excel assessment you’ll need to use and, if a candidate does well on this test, it’s a good indicator that they use the tool for the most complex of tasks. Lastly, it tests for skills that only experienced employees should have.

How to Combine Tests and Interview Questions?

The most effective way to test a large group of potential candidates is to combine the screening questions and a test for the Microsoft Excel assessment. That will provide the best return on your time investment, with the goal of identifying the best applicants.

These tests are an investment for the company since they provide the best evaluation of what the employee can do.

Another useful feature of an Excel assessment test is that it reduces the need for probationary employment periods. There’s no need to hire someone and only later determine that they don’t have the required skills. The test will do that for you, which will save time and increases retention.

Planning for the Future

Companies are complex entities and they tend to grow and change over time. That’s why the test should cover the skills that may not be needed right away. It will allow you to test for the skills that the employees will need as the company expands or their role within it expands.

Every organization should plan for growth and think ahead, which is a key factor in being competitive in any market.

Summing Things Up

A company always requires employees that are proficient in Excel. Many claim that they are able to use this tool, but there’s a lot of complexity to it. That’s why it’s important to conduct an Excel assessment screening that will help you to choose the best employee.

The assessment should cover the skills that you need for the actual job the employee will do within the company. Test different proficiency levels for different job roles. It’s also useful to have employees that are willing to grow on the job and that have skills that you may not need at the moment.

EmployTest offers a variety of Microsoft Excel tests, adjusted for different skill levels – if you’re interested in finding out more about our assessment process, try our free Excel test sample today.