Top 10 Excel Interview Questions for Job Seekers

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:

• Numbers. Numbers are one of the most frequently-seen data types in Excel. They can be formatted with a customized number of decimal places, and appear with or without commas separating the thousands digits. Numbers can be added, subtracted, divided, multiplied, or included in formulas and functions that accept numerical inputs.
• Dates. Excel can display dates in any number of ways, including the classic US-style MM/DD/YYYY format. Dates can be added or subtracted using standard addition and subtraction, and can also be manipulated using a slew of date-based functions. Interestingly, dates in Excel are technically also stores as numbers, with each date represented as the number of days elapsed since January 1, 1900. For example, the date May 6, 2019 is stored in Excel as the number 42,129, because there are 42,129 days between January 1, 1900, and May 6, 2019.
• Percentages. Numbers can also be formatted as percentages, which multiplies the given number by 100 and adds a percentage sign at the end. For example, the number 0.08 is equivalent to the percentage 8%.
• Strings. Text is stored in Excel in a format called a string. Strings of text can contain standard characters such as letters, numbers, and punctuation; strings can be manipulated via text-manipulation functions like MID and RIGHT.

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:

• Parentheses
• Exponents
• Multiplication
• Division
• Addition
• Subtraction

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.

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.

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:

• INDEX MATCH. VLOOKUP and INDEX MATCH are two of Excel's most important and commonly-used functions. As veteran Excel users know, they're used to look up values from an external table, and are important parts of automating your work with dynamic spreadsheets. One of the two is bound to come up in any Excel interview, but if you get this question, we recommend bringing up INDEX MATCH. It's a slightly more useful function, and also lacks many of the disadvantages of VLOOKUP, like the inability to insert new rows and columns into your sheets. If you go with this function, outline how you've used it in the past to dynamically lookup values and populate columns of data that would otherwise need to be manually copied and pasted.
• IF statements. IF statements are another staple of any Excel veteran's arsenal. Bring them up to let your interviewer know that you've created advanced spreadsheets that make decisions based on criteria calculated in real-time.
• PivotTables. PivotTables are an extraordinarily useful tool, and if you're applying for a job that requires intermediate or advanced Excel knowledge, they're sure to be an important criteria used by your interviewer. You may have used PivotTables for any number of things in the past, but be sure to emphasize how useful they are when quick, accurate calculations are necessary based on large sets of data with hundreds or thousands of rows.

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:

• lookup_value defines a value to look up within an external table of data.
• table_array defines a table of data in which to look up the lookup_value. The lookup_value must appear within the first column of this table.
• col_index_num tells the function how many columns into the table_array to look for a value to return.
• range_lookup is a TRUE / FALSE switch that tells the function whether to look for an exact match, or an approximate match, to the specified lookup_value.

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.

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:

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:

• Report filter. This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.
• Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen.
• Row labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.
• Values. This section allows us to specify what we're summarizing — for example, total sales or number of items ordered.

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:

• Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format — meaning that it's broken down to it's most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.
• PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.
• PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you'll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!

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.

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:

• Create multiple tabs. Keeping different pieces of your model (for example, inputs, outputs, and calculations) on separate tabs can help with model organization, particularly if you're planning to hand your spreadsheet off to someone who has never seen it before.
• Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line. Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas.
• Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor.
• Comment aggressively. You are the person who understands your models the best, but other people in your organization will doubtless be using them, too. So, be sure to over-comment and explain your calculations line-by-line so that they are as easy to follow as possible for other users.

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:

• Constructing dashboards in Excel to measure and track business metrics;
• Putting together cash flow or revenue projections over time;
• Using Excel as a project management dashboard to track progress across multiple workstreams;
• Automating day-to-day tasks using spreadsheets with IF statements and other conditional logic; or
• Performing back-of-the-envelope calculations to estimate sales volume in various business scenarios.