**7 Frequently Asked** **Excel Interview Questions**** [****Interview Tips****]**

**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 itemsthat are frequently asked during Excel interview questions.

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 operationsin Excel.

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 thata relative reference refers to what’s ‘inside’ the cell rather than just the location itself.

**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.

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

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

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:

- SUM including SUMIF and SUMIFS
- AVERAGE including AVEREIF and AVERAGEIFS
- INDEX
- MATCH
- VLOOKUP
- IF

**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

disadvantageof using this function is the fact that the‘col_index_num’ is entered manually.

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’.

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.

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

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

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

**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.

The types of chart you can make in Excel include:

**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

**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. 😊