Top Excel Interview Questions

A “strong working knowledge of Excel” is a concept that means different things to different people.

Because Excel is so versatile, different skill sets are required for various job titles and mission objectives.

Everyone has a list of skills they think make for a proficient Excel user.  These lists are as diverse as the people compiling them.

In a job interview scenario, the skills one offers may or may not line up with the skills needed for the offered position.

Being as well-rounded and experienced with Excel is the best way to show you are the best candidate for a position as well as, if you are the interviewer, identifying the best candidate.

Based on an earlier survey of XelPlus viewers, I asked interviewees what questions they were asked during their job interview regarding Excel.  Along with that, I requested input for questions asked by interviewers regarding desired skills in specific industries where Excel is heavily used.

Many of the submissions were predictable and common, while others were quite surprising.

This post will explore the most common questions asked during job interviews as well as some surprising questions you will want to be prepared for in your upcoming interview.

Also, if you are tasked with interviewing someone and you need to assess their Excel skills, you can use this post as inspiration for questions.

Either way, both interviewer and interviewee should be well-versed in all these areas of Excel.

As I showcase the most common and most surprising Excel questions asked during interviews, I will provide links to the posts and videos where more detailed explanations can be found for each topic.

Explaining each of these topics in-depth would be the equivalent of taking a beginner’s through master’s series of courses.

If you desire more structured training with hands-on practice in all these areas, check out my list of courses over on the XelPlus website.

BECOME THE GO-TO EXCEL EXPERT IN YOUR COMPANY

Pivot Tables – Basics

Pivot Tables look complicated and time-consuming to casual observers, but those observers couldn’t be more wrong.

Pivot Tables are a snap to create and can reduce hours’ worth of work into minutes.

If you need to quickly group data and answer common questions like, “What are the total sales for each product”, or “How many orders were sold by each sales representative”, then Pivot Tables make this an easy task.

To learn more about Pivot Tables, visit the post and video below.

Excel Pivot Tables Explained in 10 Minutes

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Pivot Tables – Advanced

Although Pivot Tables can be learned in a matter of minutes, this limited investment in time would only cover the basics.  Unfortunately, this is where most users stop their learning.

If you want to stand out and shine brighter than others in your field, you should consider diving more deeply into the Pivot Table waters.

Questions like, “What was this month’s sales compared to last month’s sales”, or “What percentage of accounts are domestic versus foreign” can also be easily answered using Pivot Tables.  You will need to scratch just a bit below the surface of the basics.

To learn more advanced uses of Pivot Tables, check out this post and video.

Excel Advanced Pivot Table Techniques

Performing Data Discovery with VLOOKUP

Discovering heretofore unknown information using something that is known is what we call a “lookup”.

There are many ways to discover, or lookup, information in Excel.  Some ways are simple but limited while others are more versatile but complex.

The most widely used method of looking up data is by using the VLOOKUP function.

The VLOOKUP function has been around for decades and has proved to be a reliable method for performing data discovery.

Because VLOOKUP can behave differently depending on how it is used, it’s important to learn and be comfortable with the various ways VLOOKUP searches for and returns data.

VLOOKUP is without a doubt one of the most asked topics during job interviews where Excel is a major component of the position.

Learn all about the VLOOKUP function and ace those interview questions by visiting the following post and video.

VLOOKUP & HLOOKUP for Dynamic Lookups

Aggregations with SUMIFS and COUNTIFS

Adding and counting values is one of the most fundamental skills any Excel user needs to possess.

Applying filters to values is another fundamental Excel skill.

Performing filtered sums and counts via single formulas shows you have a firm grasp of these fundamentals.

Using functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS will bring focus to your questions in ways most users only can dream about.

Being able to compose statically filtered aggregations as well as dynamically filtered aggregations via user input and cell references will bring versatility and flexibility to your dashboards.

Learn about single and multiple filters applied to aggregations with this video:

Excel SUMIFS (a better version of SUMIF), COUNTIFS & AVERAGEIFS (Multiple Criteria)

Evaluations with Nested IFs

You can’t call yourself an Excel Expert without knowing how to write formulas that make decisions.

The IF statement is the most basic of all mechanisms for performing evaluations of data.

Learning to perform and becoming proficient using a single evaluation (known as a “simple IF”) can be achieved by most any Excel user.

Chaining together a series of evaluations (upwards of 64 evaluations in a single formula) takes a bit more skill, planning, and experience.

NOTE: A 64-level nested if should be avoided at all cost unless you are trying to win some form of contest.)

Showing that you can handle multiple evaluations logically and efficiently within a single formula will show others that Excel Expert is a title well earned.

To see a demonstration of both simple IFs and more complex nested IFs, check out the following post and video.

Excel IF Formula: Simple to Advanced

All excel courses Leila Gharani

Master it ALL!

Whether you're a beginner or advanced Excel user, there is a course for you in our academy

GET ACCESS

Charting – Basics

One of the best ways to convey complex and large volumes of data into easily understood and visually intuitive stories is with charts and graphs.

Understanding the basics of charting will place you in a position to understand and become proficient in almost any chart you encounter.

With charts, the fundamentals are constant across almost any chart.  Learning each chart’s unique features is all that remains to become a charting expert.

For a quick guide on the basics of charting data, check out this post and video:

Chart Basics

Conditional Formatting

Conditional Formatting is one of the most enjoyable features of Excel.

Having a cell change its appearance based on what you place in the cell is a great way to draw attention to when certain milestones are achieved or when problems arise that may go unnoticed.

Using icons to convey a value’s status is an efficient way to tell a story quickly without having to dig too deeply into the details.

See the post and video below for an introduction to Excel’s amazingly versatile Conditional Formatting tool.

Excel Conditional Formatting with Formula

The AGGREGATE Function

The AGGREGATE function is the Swiss Army Knife of functions.

AGGREGATE is 19 separate functions packaged into a single function with additional features thrown in for good measure.

When you write a formula using the AGGREGATE function, you use a code number to indicate which of the 19 functions you wish to use.

Selecting one of the numbers listed is no different than using the selected function on its own.

The benefit of using AGGREGATE comes with the next set of options.

Selecting the appropriate option value will alter the way AGGREGATE behaves when encountering errors, hidden rows, or even other AGGREGATE functions.

This is a great way to create subtotals and grand totals whereby the grand totals ignore the subtotals, avoiding the issue of double counting.

Check out this video for a demonstration of the AGGREGATE function along with 9 other functions useful to accountants and those wishing to expand their Excel horizons.

10 Excel Functions for Accountants

Cleaning Data with Power Query

It can be argued that Power Query (also known as “Get & Transform) is the single greatest feature in Excel.  Power Query can be used to help solve almost every Excel problem you encounter.

Can you think of any other feature in Excel that can make that claim?  Probably not.

One of the greatest selling points of Power Query is that the same problem can be solved with many different approaches.  It all comes down to your experience and creativity.

Connecting to data sources, combining data sources, and transforming “bad” data into “good” data is what Power Query is known for.

Knowing this tool may be the single best skill you can obtain for virtually any position you are applying for that uses Excel.

To learn more about this life-changing tool (your Excel life, at least), visit the post and video linked below.

Change Your Life with Excel’s Power Query

Focusing on Data with Filters

Filtering is a great way to reduce data to only areas of the viewer’s interest.

Understanding all the ways data can be filtered will provide the greatest flexibility in filtering depending on the storytelling method.

Be familiar with the following filter methods:

  • Filter Buttons located at the top of tables
  • The Advanced Filter feature
  • Slicers
  • Filtering functions (SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS)

You can learn more about these filtering techniques through the following links:

Basic & Advanced Filters

Advanced Lookups with INDEX and MATCH Function

Knowing how to perform VLOOKUP (and HLOOKUP) operations is essential for every Excel power user.  But these functions have limitations.  Understanding the limitations is one thing; overcoming those limitations is another.

The INDEX and MATCH functions, when paired together, form one of the more powerful methods for performing complex lookup operations.

Each function by itself is powerful, but when put together, they overcome many of the limitations of VLOOKUP.

Check out this post to learn the basics of the INDEX and MATCH functions and see how they will become one of your greatest lookup assets.

The Basics of INDEX & MATCH

The SUMPRODUCT Function

If you’ve never heard of or used the SUMPRODUCT function, once learned, it will become one of the most powerful functions in your toolkit arsenal.

SUMPRODUCT is an array function but is different than most of Excel’s array functions in that it doesn’t require Ctrl + Shift + Enter (CSE).

SUMPRODUCT can be used to help solve many problems in non-obvious ways.  It can be used like SUMIFS, it can be used to COUNT and it can also be used like INDEX and MATCH.

Complex logical scenarios are handled with ease using the SUMPRODUCT function.

NOTE:  The syntax and logic used by the SUMPRODUCT function are more advanced than standard, day-to-day functions and may take a bit of practice to master.  Once mastered, the logical possibilities are endless.

If you’re ready to impress almost any Excel user, check out the SUMPRODUCT function post and video for more information.

SUMPRODUCT for Excel Experts

The New Office 365 Functions

Every one of the above-mentioned functions and features is essential to elevating you above all other job candidates.  The more you can successfully demonstrate knowledge of, the greater your chances of landing your dream job.  (You think Elon Musk got to where he is without masting SUMPRODUCT?  I think NOT!)

If you are a Microsoft 365 user (formerly Office 365) you have access to all the new Dynamic Array functions and new lookup functions.  These include:

  • FILTER
  • RANDARRAY
  • SEQUENCE
  • SORT
  • SORTBY
  • UNIQUE
  • XLOOKUP
  • XMATCH

Many of these functions replace older methods of achieving the same goal, but they do it more easily and more efficiently.

This is not to say that the “old ways” are no longer relevant.  Many company’s older Excel files (or those companies not running Microsoft 365) are still heavily reliant on the traditional tools.

Given the option, using the newer Dynamic Array functions will save tremendous amounts of time and frustration when developing spreadsheets.

Plus, you will be able to achieve behaviors in your spreadsheets that you thought were impossible.

If you’re looking to impress your boss or prospective boss, there is no better way than by using the new Dynamic Array functions to solve Excel problems.

For a summary and demonstration of the power of the new Dynamic Array functions, check out the post and video below.

Excel Dynamic Arrays – Excel will never be the same

5 Excel Functions YOU NEED in 2021

PRACTICE – PRACTICE – PRACTICE

Learn and practice as many of these Excel features as possible.  The more you know, the greater your chances of securing your dream job or securing your dream candidate.

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE