What are the best Excel skills to look for in candidates?

Spreadsheets don’t have to be scary – just ask a worker with great Excel skills. These workers use Excel tools effectively to finish tasks more quickly, find data insights, and improve client outcomes.

That said, employees unfamiliar with the program may produce incorrect data and analysis, potentially damaging revenues or increasing costs. They may even leave your business due to role incompatibility. You can prevent this by thoroughly assessing candidates’ Excel knowledge during hiring.

Not sure where to start? We can help.

This guide covers the best Excel skills to spot in experts and basic users alike, plus how to evaluate them in assessments and interviews. 

Why assess Excel skills?

You should assess candidates’ Excel skills to ensure candidates are proficient with the spreadsheet tasks required for the role. Otherwise, inefficient Excel use and the need to fix skill gaps could lead to productivity losses or unforeseen training costs.

Excel proficiency is generally categorized into three levels: basic, intermediate, and advanced, each requiring progressively more knowledge and skill. 

Data-intensive roles like market research analysts and accountants require Excel expertise. that often takes years to build – and is expensive to train. Intermediate Excel skills are critical for roles that demand more than basic data entry but not the full complexity of advanced analysis. Training costs for these can also add up. Meanwhile, basic Excel skills may be quicker to learn, but you still save costs by hiring someone with proven skills.

4 basic Excel skills to look for in candidates

Your candidates may require only basic Excel skills if they’re expected to perform duties such as:

• Performing basic data entry and administration

• Interpreting and manipulating basic employee information (for example, timesheets)

• Maintaining and analyzing sales and marketing contact sheets

• Tracking event attendance and ticket sales

• Creating and formatting basic project plans and timelines

Here are the best Excel skills to look for in basic users.

1. Identify and use basic tools

Candidates should be able to identify and use the correct tools for specific tasks. They should know how to: 

• Set and apply filters on column headers to organize table data

• Sort columns alphabetically or numerically

• Rename and reorder sheets in a workbook

• Zoom in and out of the sheet

• Search and replace values in the sheet or workbook, with or without a matching case

• Format cells and tables – for example, by editing font, alignment, color, or cell border

• Merge cells and wrap cell text

• Insert, delete, and hide rows or columns

• Change cell format to various types, such as numerical, text, currency, or date

• Change page layout and print spreadsheets

• Easily navigate top-level tabs, including File, Home, Data, and View

2. Manipulate basic tables

Manipulating tables is an essential Excel skill. Candidates should be able to create or modify a table depending on their task. For example, they might need to manipulate a large customer data set to produce a table featuring only newsletter subscribers. They should use tools like filter, sort, and duplicate removal to do this.

Great candidates should also be able to make tables easy to understand and analyze. To achieve this, they might:

• Transpose rows to columns

• Format headers differently from table values

• Freeze top columns or rows for clearer labeling

• Add cell borders and hide sheet gridlines for an improved look

3. Use basic functions

Here’s a selection of common Excel functions that basic users should be able to incorporate into formulas. (A formula can contain one or several functions, plus manually added values.)

• If: Checks whether a logical argument is true or not. Employees might use this in a “yes/no” column that tracks if actual sales exceeded a sales quota. 

• Sum: Adds up listed values from a column, row, table array, or manual list. Workers might use this to calculate total costs or hours worked in a team.

• Average: Calculates multiple values’ average. This function can help check employees’ average monthly income.

• Count: Returns the number of cells containing numbers in a list. Workers might use this to track the number of sales orders in a column.

• Min/Max: Displays the lowest/highest number in a list. This could be useful for finding the lowest/highest departmental budget in the past 10 years.

• Concatenate: Combines the text from two or more cells into one cell. Workers might use this to create a column with full names using separate first name and last name columns.

4. Apply basic data validation

A basic data validation tool lets you limit the possible values of a cell range and create a drop-down list of options for that range. This makes filtering and sorting easier by restricting values that are inconsistently formatted or not useful.

For instance, a cell without data validation could accept both “USA” and “United States” in a country column – which adds filtering difficulties. However, with data validation, you might allow only “USA” as an option.

2 intermediate Excel skills to look for in candidates

Look for intermediate Excel skills if your new hire must complete tasks such as:

• Efficiently tracking and analyzing large data sets like customer orders or email engagement metrics

• Managing complex projects that require rapid task and status tracking

• Ensuring organizational data privacy and security

Here are two examples of intermediate Excel skills.

1. Apply conditional formatting

Intermediate candidates should be able to use conditional formatting tools to draw viewers’ attention to specific data. For instance, they should:

• Select or create appropriate formatting rules based on requirements. For instance, to highlight a drop in revenue, Excel users might create a “value is less than” rule.

• Select cell formatting that’s appropriate for visual cues. For example, a red background or text signals concern or urgency.

• Use color scales where appropriate. For example, users might apply the Red Amber Green (RAG) color scheme to signal project status – alert, caution, or as-planned.

2. Protect data integrity

Data integrity means that data is accurate, complete, and consistent. Intermediate Excel users should implement measures that protect data integrity, such as: 

• Formula auditing: Checks if formulas work as intended. Tools like Trace Precedents and Error Checking help identify and correct formula errors, ensuring accuracy.

• Track changes: Shows which users made changes to the Excel file and reinstates previous workbook versions. This is useful for collaboration, enabling users to review and revert changes and enhancing transparency and accountability.

• Lock cells and protect sheets: Prevents accidental editing, renaming, or deletion of important cells and sheets. Employees can enable these measures to ensure locked cells remain unaltered and sheets retain their intended structure and content.

• File access: Restrict workbook access to only coworkers who require it. This prevents accidental editing and sharing of data with unverified parties. 

3 advanced Excel skills to look for in candidates

Your candidates may need advanced Excel skills if the role involves tasks like:

• Creating complex data models to forecast revenues, optimize product costs, calculate risks, or other purposes

• Analyzing different scenarios – for example, in an insurance context or a 10-year business plan

• Automating complex calculations in finance, engineering, or other industries

• Summarizing and presenting complex data to decision-makers 

Here are three of the best Excel skills for advanced users.

1. Use advanced functions and formulas

Advanced Excel functions help extract insights from complex data sets, plus save time by automating manual calculations and searches. Candidates should be able to use multiple basic and advanced functions in formulas. 

Here are common advanced functions to assess.

• And: Checks if all arguments in a specified list are true. A worker might use this to check if your employees completed all required training modules.

• SumIf: Adds up listed values only if they meet certain criteria. An employee might use this to calculate total sales only for a specific product.

• VLookUp: Vertically looks up a given value in the leftmost column and returns the corresponding value from a specified column to the right. This function could be useful for listing the cost figure associated with the look-up value “Employee expenses.”

• Match: Returns the position of a cell that matches a specified value in a list. It’s often used with Index, which returns a cell’s value based on its position in a table or list. A Match + Index formula searches for and retrieves data in a table – for example, employee names and revenue figures.

2. Create pivot tables

Pivot tables help summarize and analyze large data sets in a structured, straightforward way. They’re also easy to copy into presentations and reports – say, when presenting quarterly financial results.

Candidates should be able to:

• Set up a pivot table using a data source from the workbook

• Use the Get & Transform tool to import data from an external source – such as another Excel workbook or a text file

• Select the analysis values (e.g., sum, average), rows, columns, and filters for the pivot table

• Group and filter data according to the task requirements (for example, revenue data might be grouped and filtered by calendar month or product type)

• Refresh the pivot table to account for changes in the underlying data

Create visualizations

Visualizations in Excel help present data quickly and engagingly. Advanced users should know how to:

• Match the appropriate chart type to the requirement. For example, a pie chart can show how a group is divided into sub-groups but can’t show profit growth over time. 

• Select source data for the chart. For instance, an employee creating a revenue chart would select the data range that includes dates and corresponding revenue figures.

• Set up the vertical/horizontal axes of the chart and the values required from the source data. For example, a worker might set years on the horizontal axis and revenue amounts on the vertical axis when illustrating annual revenue trends.

• Add appropriate data labels. Possible labels might be “Total sales” or “2023.”

• Make the chart easy to understand using formatting. For instance, users might edit the color scheme and the size and thickness of chart elements.

• Tell a story using a series of charts. For example, in a market entry presentation, a pie chart can represent the market’s customer sub-groups, a bar chart of the customer sub-groups disposable income, and a line chart of the projected revenues over five years.

How to assess candidates with Excel skills

The best way to assess candidates’ Excel skills is by using a talent assessment. This gives you an unbiased ranking of your top candidates – including not only their Excel knowledge but also their role-specific skills, cognitive abilities, and more.

TestGorilla is a talent assessment platform offering two science-backed Excel tests that assess candidates’ experience with various tools and formulas:

• Our general Excel test covers table manipulation, basic formula use, and more. 

• Our advanced Excel test checks data organizing, advanced formulas, visualizations, and more.

With our platform, you can include up to five tests in a single assessment. You can also customize tests with your own questions so they’re tailored to your role.

On top of talent assessments, you can test Excel skills during interviews. For instance, ask: 

• Basic Excel interview questions like “What’s the difference between the Sum and SumIf functions?”

• Advanced Excel interview questions, such as “Explain how and why you’d protect specific cells or sheets in Excel.”

Find the candidates with the best Excel skills using TestGorilla

A candidate’s Excel proficiency is key – whether they’re analyzing complex data or filtering a column. You’ll want to assess candidates’ Excel skills at basic, intermediate, or advanced levels – depending on the role – to ensure you hire someone with the proficiency you need. 

TestGorilla offers both general and advanced Excel tests, assessing skills such as table manipulation, formula use, and visualization. Combine our Excel test with others in our test library – like cognitive ability or role-specific tests – for a comprehensive hiring approach.

Want to learn more? Schedule a free live demo or join TestGorilla for free today!