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
Featured Course
Excel Essentials for the Real World
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
Featured Course
Black Belt Excel Package
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
Featured Course
Fundamentals of Financial Analysis
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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
