Applying for a job and looking for sample Excel interview questions?
Check out our list of the top 10 practice questions.
Make sure you're prepared for the day that could change your career. Sign up for five free must-learn lessons before your Excel interview!
Microsoft Excel was released over thirty years ago, but it’s still one of the most useful applications on the market. It owes its enduring popularity to a huge number of features that allow users to analyze data quickly and easily. Since Excel’s launch, many competitors have unsuccessfully tried to displace it as the tool of choice for data analysis. Nevertheless, Excel still reigns supreme.
Because spreadsheets are so popular — and useful — many companies conduct Excel interviews before extending job offers. This helps ensure that job applicants are qualified — and comfortable using Excel on the job.
If you’ve got an upcoming Excel interview, you’re probably wondering what sorts of questions you might get. Recruiters are generally vague on details, and it can be scary to walk into a conversation like this one blind.
So, we’ve provided our list of the 10 most common Excel interview questions so that you can get some practice in before you exam. With a little bit of advance preparation, you’ll feel prepared and confident on interview day.
1. What are the most important data formats seen in Excel, and how are they used?
Your Excel interview may start out with an easier question like this one. If you've spent any time in Excel, you've almost certainly had a chance to experiment with different data types, and will likely be familiar with some of the most frequently-used:
2. What is the order of operations used when evaluating formulas in Excel?
Just like in standard mathematics, Excel uses an order of operations when evaluating different operators within the formula bar. You'll almost surely recognize the acronym PEMDAS — it's the order of operations taught in mathematics classes worldwide, and it's also the order that Excel uses. PEMDAS stands for:
When evaluating formulas, Excel always processes operators in this order. If you find yourself receiving an unexpected result from your mathematical formulas, double-check to make sure that parentheses are used properly to achieve the results you want.
Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your Excel interview. Start preparing today!
3. What is a function in Excel?
If you're a frequent Excel user, functions are probably second nature to you. You've used SUM , AVERAGE , and even VLOOKUP so many times that you don't even think about what a function actually is when you're creating spreadsheets.
As such, this question may come as a surprise to you — especially because describing what a function is can be a bit difficult if you never think about it.
Here's an easy way to do it: think of a function like a recipe. It's used to combine a bunch of ingredients — which may not taste particularlly good individually — into something much more useful.
The name of the function is like the title of the recipe. It describes what the function does, like take a SUM or an AVERAGE .
The arguments of the function describe what ingredients go into it. Individual functions can take any number of arguments, from one to an infinite number. It all depends on the function.
Finally, the output of the function is what comes out the other side: a useful quantity that can show you important data, or be used as an input to other functions in your spreadsheet.
4. In your opinion, what are a few of the most useful functions in Excel? How do you use them?
This is a tricky question, because it asks you to use your subjective judgement rather than answering objectively. As such, you'll have a wide range of latitude in your response — and you should have a well thought-out reply prepared that demonstrates both your proficiency with Excel and your wide range of past experience using spreadsheets.
Here are a couple of our top recommendations for features, formulas, and functions to discuss:
5. What is the syntax of the VLOOKUP function? Are there any disadvantages to using this function?
VLOOKUP is one of the most commonly-used functions in the business world, and if you've got an Excel interview, you'll almost certainly be asked about it. Be sure you're properly prepared for your interview by memorizing the syntax of VLOOKUP by heart:
=VLOOKUP (lookup_value ,table_array ,col_index_num ,range_lookup )
VLOOKUP 's arguments can be described as follows:
As for disadvantages — VLOOKUP does have one crucial one: since the col_index_num is usually entered manually into the function, VLOOKUP can break if columns of data are inserted into the table_array after the function is written. This may not seem like a major disadvantage, but can cause hours of frustration over broken formulas if you're not careful.
To fix the problems that sometimes occur when columns of data are inserted, consider replacing your VLOOKUP formulas with INDEX MATCH , which contains similar functionality with none of the disadvantages.
Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your Excel interview. Start preparing today!
6. What is the difference between absolute and relative cell references? In which situations would you use each?
As many Excel users know, one of the most magical features of Excel is the cell reference . Cell references allow users to include the values of external cells in formulas dynamically — rather than hard-coding particular values manually.
However, cell references can be confusing when copied and pasted to different locations. By default, Excel uses relative cell references, which change dynamically as they are copied and pasted around a sheet. For example, if a reference to cell A1 is copied and pasted one row down and one column to the right, the new reference will point to cell B2 . This allows users to perform similar calculations on different ranges of cells quickly and easily.
In contrast, absolute cell references do not change when they are copied and pasted to other locations within a sheet. Absolute cell references can be used on either rows, columns, or both at the same time, and are indicated using the $ sign. For example, if a reference to cell $A$1 is copied and pasted one row down and one column to the right, the new reference will point to cell A1 — it won't change at all, because both the row and column are locked. If a reference to cell $A1 is copied and pasted one row down and one column to the right, the new reference will point to cell A2 — only the row number will change, because the column letter is locked.
Here's a handy table that will show you what the $ sign means depending on where you see it in a cell reference:
| Format | Meaning | Explanation |
|---|---|---|
| $A$1 | Row and column locked | Cell reference will not change at all as cell is copied and pasted. |
| $A1 | Column locked | Only row reference will change as cell is copied and pasted. |
| A$1 | Row locked | Only column reference will change as cell is copied and pasted. |
| A1 | Nothing locked | Both row and column will change as cell is copied and pasted. |
7. What is a PivotTable, and when would you use one? What are the key PivotTable 'sections' into which users can drag columns?
As one of the most-used Excel features in business settings, PivotTables are sure to come up during any in-depth Excel interview. Be sure you're prepared in advance with a firm grasp of what exactly PivotTables are, and why they're useful in practice.
Simply put, a PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions — like SUM , COUNT , and AVERAGE .
There are many use cases for PivotTables, but they're most handy when you need to analyze a large data set quickly. If you've got high-level, one-off questions on a massive data set — for example, "how many cookies did we sell in February of last year", or "which salesperson closed the most deals this March", chances are a PivotTable is the perfect way to answer them.
Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed:
8. Do PivotTables have any drawbacks? How can they be solved?
Of course, no Excel feature is without its drawbacks, and there's a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel's most important features — after all, PivotTables can't be used for everything!
If asked about the drawbacks of PivotTables, consider the following:
As an alternative to PivotTables, consider using conditional summary functions like SUMIFS and COUNTIFS , particularly when constructing dashboards. They can produce similar results, but are less 'fluid' — making your results more predictable and easier to track.
9. What are some best practices when creating complex models in Excel?
Excel can be used for simple calculations, but it's most effective when constructing complex mathematical models that help predict outcomes, project financial results, or track data over time. If you're interviewing for a highly analytical role, there's a good chance your recruiter will ask about how you can use spreadsheets to accomplish these more difficult tasks.
When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible:
10. Talk about some of the spreadsheets you've made that you're most proud of.
This is also a softer, more subjective question. It doesn't have to do with the features of Excel itself — rather, your interviewer may ask it to get a sense for your past experiences with spreadsheets and your enthusiasm for quantitative analysis.
Before walking into your interview, be sure that you have 2-3 examples of your prior spreadsheet use prepared so that you can answer this question. The more excited you are about these examples, the better; it's likely that your interviewer is also trying to get a sense for the excitement and passion that you'd bring to the job if hired.
Here are some examples of applications of Excel that you might want to talk about, if applicable:
Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your Excel interview. Start preparing today!
Make sure you're prepared for the interview that could change your career. Sign up to receive five free must-learn lessons in preparation for your Excel interview. Start preparing today!