7 Frequently Asked Excel Interview Questions [Interview Tips]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

It’s been more than 30 years since Excel was released.  

And yet, the number of companies who would like an Excel ninja to work for them keeps increasing!

The answer to that is simple:

The range of what Excel can do — from simple ledgers to organizing field data — is purely massive that thousands of career postings require applicants to have a working knowledge of it.

That’s why in this article, we’ll be talking about 7 items that are frequently asked during Excel interview questions.

Kasper Langmann, Co-founder of Spreadsheeto

Once you master what’s written here, you’ll be job-ready in no time. Let’s get started! 😊

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

1. Order of Operation

Let’s start with the easiest one.

Questions:

  • What is the central order of operations used in Excel calculations?
  • What is the order of operations in evaluating formulas in Excel?
  • What is the sequence of mathematical operations in Excel?

The bottom line — they want to make sure that you, at the very least, know the order of operations in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

Like what you learned in school, Excel follows the order of operations used in mathematics and computer programming: PEMDAS.

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

In case you’re asked to apply this in the formula bar and you encountered an error, check that the parentheses are used properly. You’ll be amazed at how a parenthesis could easily destroy an equation.

After you answer this, the interviewer might proceed to questions related to some of the operations. It would be helpful to know some of the related topics:

2. Cell Reference

Questions:

  • How does cell reference affect calculations?
  • How does absolute reference differ from relative cell reference?

First off, a cell reference saves you time by allowing you to include the values of other cells in formulas

This is usually done by either typing the cell’s location directly or clicking on the cell after writing the equal sign (=).

There are two types of cell reference:

  • Relative
  • Absolute

Relative reference is when you simply type the location of the cell or clicking on it after the equal sign. This is the default reference.

The basic difference between the two is that a relative cell reference changes dynamically as the cell referenced is cut and pasted around the sheet. You can say that a relative reference refers to what’s ‘inside’ the cell rather than just the location itself.

Kasper Langmann, Co-founder of Spreadsheeto

Absolute cell references do not change once a cell’s content is cut and pasted to another location. It can be applied to a column, row, or both. The dollar sign ($) is an indication that absolute cell reference is used.

Because a cell reference is used to avoid writing the same data again, a formula with incorrect cell references would be catastrophic.

Suggested Readings:

3. Functions in Excel

Questions:

  • What is the definition of a function in Excel?
  • What is the difference between a function and a formula?
  • What are the most common functions in Excel?

Functions are built-in (or pre-defined) formulas that do calculations with specific values in a defined order.

The basic syntax of a function usually composes the following:

  • Equal sign (=)
  • Function name
  • Arguments or parameters

The equal sign signifies the start of a function. When you enter the sign and press a letter, Excel gives you suggested functions. Also, when your mouse hovers over a formula, you can read a little summary of what the function can do.

after writing the letter 'a' after the equal sign, a dropdown with function suggestion shows up

Also, the name of the function usually gives a hint on what the function can do or what output you can expect. For example, a SUM function would certainly give you a sum of specific values.

The arguments or parameters are the input or information you have to feed the function to calculate correctly. However, there are certain functions, like the ‘NOW’ function, that don’t require arguments.

Kasper Langmann, Co-founder of Spreadsheeto

On the other hand, a formula is a statement written by a user to make calculations. It can contain values, cell references, and even functions.

Some of the most commonly used functions include:

4. The VLOOKUP Function

Questions:

  • What does a VLOOKUP do?
  • What’s the syntax of VLOOKUP?
  • Are there any disadvantages to this function?

The primary purpose of the VLOOKUP (Vertical Lookup) function is to provide a quick vertical search for a specific value

Here’s its syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Here’s a short summary of the arguments:

  • ‘lookup_value’: What you’re looking for
  • ‘table_array’: Where to look
  • ‘col_index_num’: What you want to know
  • ‘range_lookup’: Return either a precise or approximate value

The disadvantage of using this function is the fact that the ‘col_index_num’ is entered manually.

Kasper Langmann, Co-founder of Spreadsheeto

Once you insert a column within the function’s range, VLOOKUP breaks. To fix this, consider using an INDEX + MATCH function.

Further readings:

5. The Pivot Table

Questions:

  • What is a pivot table and how to use one?
  • What are the key sections into which users can drag columns with a pivot table?
  • Can you make a pivot table with multiple sources of data?
  • Can you check whether a pivot table is modified or not?

Here’s a pivot table in a few words: 

Take the data and re-arrange it.

Pivot tables help improve your ability to draw conclusions from your data especially when you have large sets of them with different attributes. It’s a tool that can help you summarize large quantities of data easily and quickly.

A pivot table has many key sections:

  • Report filter: Breakdown of data across different categories
  • Column labels: Place data (like a summary) labels across columns
  • Row labels: Place data labels across rows
  • Values: Where you can specify what type of data you want to summary

It is possible to create a pivot table with multiple sources (like worksheets) from the same workbook. 

There’s a ‘PivotTable and PivotChart Wizard’ which can help you analyze data from different sources. Open this wizard by pressing ‘Alt’ + ‘D’, then ‘P’.

Kasper Langmann, Co-founder of Spreadsheeto

In addition, you can check if the pivot table is modified or not by using the ‘PivotTableUpdate’ in the worksheet with the pivot table.

Further readings:

6. Important Data Formats

Questions:

  • What are the most commonly used data formats in Excel?
  • How are data formats used?

Data formats are some of the basic elements you first get exposed to in Excel. At the same time, they’re also the ones easy to master.

In Excel, there are different data formats available:

  • Strings: Texts stored in Excel which can contain letters, numbers, and punctuations
  • Numbers: Numerical inputs used in formulas and functions. They can be formatted in terms of decimal places and separating commas.
  • Dates: They are different ways to display dates. Technically, they are stored as numbers which means you can add or subtract them using standard methods.
  • Percentages: They are also numbers but you can format them either as percentages (%) or in decimal form.
  • Currencies: It’s possible to format monetary values in different currencies.

Furthermore, there are lots of data formats available in Excel.

access other available data formats by pressing the dropdown and then 'more number formats'

To view them all, simply click the dropdown on the ‘Number’ group under the ‘Home’ tab of your Excel.

home tab from the tab list with the data format dropdown

Then, click ‘More Number Formats’ from the dropdown to open the ‘Format Cells’ window.

more number formats on the dropdown

Feel free to explore all the format category and their available options.

different data formats available

7. Available Charts in Excel

Questions:

  • What are the different charts in Excel?
  • Why is it important for you to use the appropriate chart?

Charts in Excel are visualization tools that would help present data visually to the audience.

To be able to use the correct chart, you first have to understand your data as well as the message you want to convey.

In addition, you can use different charts as you like. However, the graphics presentation is limited to your data. You can’t visualize your data to a specific chart if you don’t have sufficient data.

Kasper Langmann, Co-founder of Spreadsheeto

Recommended Charts

In addition, it’s possible to check the recommended charts of your data.

To do so, here’s what you need to do:

  • Select your data
  • Click ‘Insert’ from the tab list

Click ‘Recommended Charts’ under the ‘Charts’ group in the Ribbon

see the recommended charts by clicking its icon

Wrapping things up…

With the items above, along with the suggested readings, it’s almost guaranteed that you would be job-ready if you’re able to study them all.

But the key to mastering Excel is the 8-lettered magic word — PRACTICE. A lot of interviews include practical ones, so always be prepared to apply in practice what you know. 😊

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-04T08:55:56+00:00