excel case study interview questions

Table of Contents

Beginner level excel interview questions, intermediate level excel interview questions, advanced level excel interview questions, 50 ms excel interview questions to ace your excel interview.

Microsoft Excel is a go-to tool if you’re working with data. Starting from simple calculations to building reports, Excel has it all covered. Knowing Excel has become a necessity in today’s times. Organizations rely on Excel for storing and analyzing their data. This article on MS Excel interview questions and answers will cover the top 50 questions that could be asked in an Excel interview.

So, let’s start with our beginner level Excel interview questions.

Watch the video below on Excel interview questions and answers that covers all the basic, intermediate and advanced interview questions that are frequently asked.

We will start with a set of Excel interview questions aimed at the beginners. 

1. What is a cell address in Excel? 

A cell address is used to identify a particular cell on a worksheet . It is denoted by a combination of the respective column letter and a row number. 

As shown above, the highlighted cell belongs to the column ‘D’ and row 5, so the cell address is read as D5.

Learn Job Critical Skills To Help You Grow!

2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

3. How do you freeze panes in Excel?

Freeze panes keep the rows and columns visible while scrolling through a worksheet. To freeze panes, select the View tab and go to Freeze Panes.

If you are looking to freeze the first two columns of a dataset, select the 3rd column, and click ‘Freeze Panes’. A thick grey border indicates this.

4. How can you restrict someone from copying a cell from your worksheet?

1. First, choose the data you want to protect.

2. Hit Ctrl + Shift + F. The Format Cells tab appears. Go to the Protection tab. Check Locked and click OK.

3. Next, go to the Review tab and select Protect Sheet. Enter the password to protect the sheet. 

Let’s now move onto our next question on our list of Excel interview questions. 

5. How is a Formula different from a Function in Excel?

Now, let’s head to our next question in our list of Excel interview questions.

6. Mention the order of operations used in Excel while evaluating formulas.

The order of operations in Excel is referred to as PEDMAS. Shown below is the order of precedence while performing an Excel operation. 

• Parentheses
• Exponentiation
• Division/Multiplication 
• Subtraction

As seen above, first, the data in the parentheses is operated, followed by the exponentiation operation. After that, it can be either the division or multiplication operations. The result is then added and finally subtracted to give the final result.

Let’s look at an example of the PEMDAS precedence in the next question on our Excel interview questions list. 

7. How will you write the formula for the following? - Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2.

To write a formula for the above-stated question, we have to follow the PEDMAS Precedence. The correct answer is ((A1*10)+5)/2.

Answers such as =A1*10+5/2 and =(A1*10)+5/2 are not correct. We must put parentheses brackets after a particular operation. 

The output will look like this:

8. What is the difference between count, counta, and countblank?

The count function is very often used in Excel. Here, let’s look at the difference between count, and it’s variants - counta and countblank. 

It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted. Shown below is an example of the count function.

It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted. Shown below is an example of the counta function.

3. COUNTBLANK

As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration. Shown below is an example of the countblank function.

9. What is the shortcut to add a filter to a table?

The filter mechanism is used when you want to display only specific data from the entire dataset. By doing so, there is no change being made to the data. The shortcut to add a filter to a table is Ctrl+Shift+L.

10. How do you create a hyperlink in Excel?

Hyperlinks are used to navigate between worksheets and files/websites. To create a hyperlink, the shortcut used is Ctrl+K.

The ‘Insert Hyperlink’ box appears. Enter the address and the text to display. Here, we are directed to the Amazon Website.

11. How can we merge multiple cells text strings in a cell?

To merge text strings present in multiple cells into one cell, you can use the CONCATENATE() . Shown below is an example of the concatenate function. 

Another way of combining cell values is by using the “&” operator, as shown below:

Let’s now move onto the next question on our Excel interview questions list.

12. How can you split a column into 2 or more columns?

You can split a column into 2 or more columns by following the below steps:

1. Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns.

2. Select the delimiter.

3. Choose the column data format and select the destination you want to display the split.

4. The final output will look like below where the text is split into multiple columns.

13. What is the use of VLOOKUP and how do we use it?

The function VLOOKUP in Excel is used to look up information in a table and extract the corresponding data. 

Syntax:  VLOOKUP (value, table, col_index, [range_lookup])

              value - Indicates the data that you are looking for in the first column of                a table.

              table -  Refers to the set of data (table) from which you have to retrieve                the above value.

              col_index - Refers to the column in the table from where you are to                      retrieve the value.

              range_lookup - FALSE = exact match [optional] TRUE = approximate                    match (default). 

Shown below is an example of the VLOOKUP function . We are to find the Product related to the Customer Name – “Richard”.

14. How is VLOOKUP different from the LOOKUP function?

Your Data Analytics Career is Around The Corner!

15. How many report formats are available in Excel?

There are three report formats available in Excel; they are:

• Compact Form
• Outline Form
• Tabular Form

16. How does the IF() function in Excel work?

In Excel, the IF() function performs a logical test. It returns a value if the test evaluates to true and another value if the test result is false. It returns the value depending on whether the condition is valid for the entire selected range. 

Let’s look at the below example:

As seen above, the IF function returns “Record is Valid” if age is greater than 20, and the salary should be greater than $40000. Else, it will return “Record is Invalid”. Here the final answer will be “Record is Valid” as the entire selected range qualifies both the conditions. 

17. How do we use the SUMIF() function in Excel?

The SUMIF() function adds the cell values specified by a given condition or criteria. Given below is an example of the sumif function.

As seen above, the costs corresponding to the years 2010 are added as per the given criteria. 

18. Using the COVID data, find the number of days in which the number of deaths in Italy has been greater than 200.

To perform this operation, we can use the COUNTIFS() function. The dataset we will be using is shown below:

The COUNTIFS() function we use is -  =COUNTIFS(G2:G35777,"Italy",E2:E35777,">200")

19. What is a Pivot Table?

A pivot table is like a summary table of the dataset that enables you to create reports and analyze trends. They are useful when you have long rows or columns that hold values you need to track. 

To create a pivot table, first, go to the Insert tab and select the ‘PivotTable’ option.

Select the table or the range and choose where you want to place the pivot table.

Drag the fields you wish to show in the pivot table. Here we have created a pivot table using the Coronavirus data.

20. Create a drop-down list in Excel.

This can be done by using the ‘Data Validation’ option present in the Data tab.

In the example below, we have created a list based on the city column of the dataset.

21. How do we apply advanced filters in Excel?

To apply advanced filters, use the Advanced Filter option present in the Data tab. Select where you want to filter the table. Choose the ‘list range’ and the ‘criteria range’ that has the conditions based on which you would like to filter the table. 

The below example shows how to apply advanced filters.

22. Using the below-given sales data, highlight those cells where total sales > $5000.

Here, conditional formatting is used to highlight cells based on the criteria.

1. Select ‘Conditional Formatting’ from the home tab and under Highlight Cells Rules, choose ‘Greater Than option’.

2. Provide the condition and choose the color for the cells to be highlighted.

23. Using the given table, explain how the index-match function works in Excel.

Here, we will write an index-match function to find the city to which Andrew belongs to from the below table.

Here is how you can use the Index-Match function to get the result.

24. How do you find duplicate values in a column?

To find duplicate values in a column, you can either use Conditional Formatting or the COUNTIF() function.

1. Conditional Formatting

First, go to the Home tab, then under Conditional Formatting, select ‘Highlight Cells Rules’. Then choose ‘Duplicate Values’.

Below, we have highlighted the cells in the ‘Name’ column that have been repeated.

2. COUNTIF()

You can write a COUNTIF() function to check if the values in a particular column are repeated.

In the below example, we are fetching the duplicate names using the COUNTIF() function.

25. How can you remove duplicate values in a range of cells?

1. To delete duplicate values in a column, select the highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’ option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet.

2. You can also delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.

Moving forward, let’s have a look at the intermediate level of Excel interview questions.

26. What are the wildcards available in Excel?

Wildcards only work with text data. Excel has three wildcards.

1. * (Asterisk)

This refers to any number of characters.

The example stated below filters the customers whose name ends with “a”. 

For that, we use “*a”.

2. ? (Question mark)

It represents one single character.

The example below shows how to filter a particular customer name. 

3. ~ (Tilde)

It is used to identify a wildcard character (~, *, ?) in the text.

In the following example, we are filtering How?* using the tilde (~) symbol. 

27. What is Data Validation? Illustrate with an example.

Data Validation restricts the type of values that a user can enter into a particular cell or a range of cells.

In the Data tab, select the ‘Data Validation’ option present under Data Tools. 

Select the kind of data validation you want to apply.

In the following example, we have applied data validation to the ‘Name’ column to accept only text values. If you enter something other than a text, it will throw an error.

28. Given below is a student table. Write a function to add pass/fail to the results column based on the following criteria.

If student marks > 60 and attendance > 75%, then pass else the student fails.

You can use the IF() function and check with an AND condition to fill in the results column.

29. Calculate your age in years from the current date.

Use the YEARFRAC() or DATEDIF() function to return the number of whole days between start_date and end_date 

Learn best business analysis techniques by Purdue University, IB and EY experts. Sign-up for our Post Graduate Program in Business Analysis TODAY!

30. How are nested IF statements used in Excel?

The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.

Below, using nested IF statements, we are categorizing results based on the marks.

31. From the below table, find the descriptive statistics of the columns using the Data Analysis ToolPak in Excel.

Add the Analysis ToolPak from Options ----> Add-ins ----> Analysis ToolPak.

Click on the Data Analysis option in the Data tab. Choose Descriptive Statistics.

Below is the summary table for the columns and their respective statistical measures.

32. Using the Coronavirus dataset, create a pivot table to find the total cases in each country belonging to their respective continents.

First, drag the continent and country columns into rows. After that, drag the cases column on to the values section.

33. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?

Dynamic Range in the data source of pivot tables is used to make your pivot table dynamic to adjust to new data when refreshed automatically.

Create a Named table to provide a dynamic range. Go to the Insert tab and select Table.

Under Table Design, give a name to the table.

Become job-ready with a globally recognized Business Analyst Certification Course. Sign-up today and enrich your career.

34. Is it possible to create a Pivot Table using multiple sources of data?

Yes, you can create a pivot table from multiple worksheets. For this, there must be a common row in both the tables. This will act as the Primary key for the first table and Foreign key for the second table. Create a relationship between the tables and then build the pivot table.

35. Create a pivot table to find the top three countries from each continent based on the total cases using COVID data.

• Create a pivot table using the coronavirus dataset by dragging sales into values.
• Place the continent and country columns into rows.
• Filter the table by selecting ‘Top 3’.

Below is the sequence of steps to follow.

Become The Highest-Paid Business Analysis Expert

36. How do you create a column in a pivot table?

For this, you have to go to the PivotTable Analyze tab and select ‘Fields, Items & Sets’ option. Under that, you need to click ‘Calculate Field’ to create a new column.

The Insert Calculated Field box appears. Give a name to the column and insert the formula by selecting the existing columns from the pivot table. Click Add ----> OK to create the column.

37. How does a Slicer work in Excel?

To filter data in a Pivot table, we can use slicers. 

• To create a slicer, go to the Insert tab, and select Slicer present under Filter. 
• Then, select the list of fields for which you want to create slicers.

In the below example, we have created two slicers (months, countries, and territory) to filter the pivot table. 

38. Use the coronavirus dataset to find the percentage contribution of each country and continent to the total cases?

• Create the pivot table to show the total cases by country and continent.
• Right-click on the sum of cases column and under Show Value As, select “% of Grand Total.”

39. How do you create a pivot chart in Excel?

• To create a pivot chart, first, we need to create a pivot table.

• Go to the Insert tab next and select the ‘Pivot Chart’ option. Choose a suitable chart to represent your pivot table data.

Accelerate your career with our Post Graduate Program in Business Analytics in partnership with Carlson School of Management. Enroll and start learning!

40. What are macros in Excel? Create a macro to automate a task.

Macro is a program that resides within the Excel file. The use of it is to automate repetitive tasks that you would like to perform in Excel.

To record a macro, you can either go to the Developer tab and click on Record Macro or access it from the View tab.

Now that we are done with the intermediate level of the Excel interview questions, let’s move on to the advanced level of Excel interview questions. 

41. What is the What-If Analysis in Excel?

The What-If Analysis in Excel is a powerful tool to perform complex mathematical calculations, experiment with data, and try out different scenarios. 

Consider the following example:

If you get $10,000 worth of sales over the next few months, how much profit can you expect?”

Such scenarios can be solved using the What-If Analysis.

Go to the Data tab and click on What-If Analysis present under Forecast.

Scenario Manager is used for a comparison of different scenarios.

The Goal Seek performs reverse calculations.

The Data Table is used for sensitivity analysis.

To learn more about how What-If analysis works, click on this link: “IQ video link”

42. What is the difference between a function and a subroutine in VBA?

43. What is the difference between ThisWorkbook and ActiveWorkbook in VBA?

44. How will you pass arguments to VBA Function?

Arguments can be passed to a VBA function as a reference or as a value.

Below is an example to illustrate both the usages.

Dim x As Integer

MsgBox Triple(x)

If you run the cells by passing the values as a reference, it will display 40 both the times. When we pass arguments by reference, we are referencing the original value. The original value of x is changed in the function.

When we pass the arguments by value, we are passing a copy to the function. The original value is not changed. Hence, the second MsgBox will display the original value 10.

45. How do you find the last row and column in VBA?

To find the last row, use the below lines code in the VBA module:

Sub FindingLastRow()

Dim lastRow As Long

lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

MsgBox (lastRow)

To find the last column, use the below lines code in the VBA module:

Sub FindingLastColumn()

lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

MsgBox (lastColumn)

Want to learn the latest business analysis skills? Sign-up for our Post Graduate Program in Business Analytics and gain the skills to excel in the business world. Start learning TODAY!

46. How do we check whether a file exists or not in a specified location?

Sub CheckFileExists()

Dim strFileName As String

Dim strFileExists As String

    strFileName = “File location\file_name.xlsx”

    strFileExists = Dir(strFileName)

   If strFileExists = “” Then

        MsgBox “The selected file doesn't exist”

        MsgBox “The selected file exists”

47. Explain how to debug a VBA code?

To debug a VBA code line by line, you can use the F8 key. You can also create a breakpoint to terminate the execution wherever you want.

The execution will start from the beginning of the code, and every time you press F8, it will execute the next line and continue until the end of the code. The yellow arrow and the highlighted line tells you the current point to execution.

Become a Data Scientist with Hands-on Training!

48. Write a VBA function to calculate the area of a rectangle.

Function Area(Length As Double, Optional Width As Variant)

    If IsMissing(Width) Then

        Area = Length * Length

        Area = Length * Width

End Function

49. Write a VBA function to check if a number is a prime number or not.

Sub Prime()

Dim divisors As Integer, number As Long, i As Long

divisors = 0

number = InputBox(“Enter a number”)

For i = 1 To number

If number Mod i = 0 Then

    divisors = divisors + 1

If divisors = 2 Then

    MsgBox number & “ is a prime number”

    MsgBox number & “ is not a prime number”

Learn the latest tools, work on real-world projects, and attend Masterclasses from IBM and EY experts. Join our Master's program today.

50. Write a VBA code to create a bar chart with the given data.

Consider the below data that has two features. You can use the lines of code below to create a bar chart.

Once you have run the above VBA code lines, below is the bar chart you will get.

So, those were the 50 Excel interview questions that can help you crack your Excel interviews and help you in bagging your dream job. 

Now that you know the various Excel interview questions that can be asked in an interview, you can prepare by referring to the given answers for each of these Excel interview questions. Here, we had a look at a plethora of Excel interview questions based on different levels of difficulty. Practicing Excel regularly and going through these Excel interview questions will keep you prepared for any question that is thrown at you.

To kick-start your  fruitful career in Excel , enroll in  Business Analytics with Excel course today!

We hope this article on Excel interview questions was useful. Do you have any questions related to this article? If so, then please put it in the comments section of the article and our experts will get back to you on that right away. 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Recommended Reads

Kubernetes Interview Guide

Top Sales Interview Questions and Answers

Top 24 Ansible Interview Questions and Answers

Deep Learning Interview Guide

50 MBA Interview Questions and Answers

Top 30 UiPath Interview Questions With Answers

Get Affiliated Certifications with Live Class programs

Data analyst.

• Industry-recognized Data Analyst Master’s certificate from Simplilearn
• Dedicated live sessions by faculty of industry experts
• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

100+ Excel Interview Questions & Answers

Microsoft applications such as Word, Excel, PowerPoint are used in almost every large organization as well as by millions of small businesses, freelancers, and entrepreneurs.

For something that is so popular, it makes sense to have a decent knowledge on how to use these applications efficiently. In some job roles, a major part of the work involves working with these applications on a daily basis.

I have been part of many interview panels where I have seen the candidates mention Excel as one of the skills they are expert in, but when asked a few questions on it, the expertise fizzled out.

In case you’re prepping up for your next interview, and have listed Excel as one of your skills, take some time to go through these common Excel interview questions.

I have tried to cover all the popular Excel interview questions I have seen being asked, as well as some that I thought could be good to know.

This guide can also be used by interviewers who want to gauge the Excel skill of the candidates in the interview.

Note: I will keep on adding new questions to this guide. You can bookmark it so that you can come back and check it later.

Related: Best Books to Learn Excel and become an Expert

How to Use this Excel Interview Questions Guide

• If you have time, go through each of the Excel questions.
• If you want to learn about specific topics, click on the topic name from the table of contents. Then you can go through all the Excel questions for that topic.
• In most of the questions, further reading has been suggested and relevant tutorials has been linked. You can go through those tutorials to get a deeper understanding of the topic.
• If you only want to get the basics right, go through all the questions except the ones in the boxes highlighted in blue. Excel questions in the blue box are of an advanced level.

Excel Interview Questions Topics

Below I have covered the most common and important Excel interview questions based on the topic.

Excel Formatting Questions

Below are some common formatting Excel interview questions (click on the question to view the answer to it).

What are the different data formats in Excel?

The following formats are available in Excel:

• Text Format – This can include text as well as alphanumeric strings (such as ABC123). A text string can also include punctuation and symbols.
• Number Format – There are different formats even within numbers. For example you can have decimals, fractions , have thousand separators, etc. No matter what format has been applied, you can use numbers in calculations such as addition, subtraction , division, and multiplication.
• Date Format – The most important thing to know about dates is that these are stored as numbers in the Excel. However, you can format it to be shown as dates. For example, 01-01-2019 would be saved as 43466 in Excel, which is the number for the given date. In Excel, you can show dates in different formats such as long date (01 January 2019), short date (01-01-2019), etc.
• Accounting / Currency   Format – When you apply the accounting / currency format to a number, Excel adds the currency symbol along with two decimal places .
• Percentage Format  – You can format numbers to be shown as percentage. For example, you can make 0.1 to show up as 10% when you apply the percentage format to it.

How can you wrap text in Excel?

Wrapping text in Excel allows you to avoid any text overflowing out of the cell.

By applying the wrap text option, you can make sure all the text fits nicely in one single cell (which may change its height, though).

To wrap text, select the cell, go to the Home tab, and click on the Wrap text option in the Alignment group. Note that this is a toggle button, which means that if you click on it again, it will unwrap the text.

How can you merge cells in Excel?

To merge cells , you need to first select the cells that you want to merge, then go to the Home tab, and click on the ‘Merge and Center’ option in the Alignment group.

Note that while using ‘Merge and Center’ gets the job done, it is not the most efficient way to do it.

The problem of using ‘Merge and Center’ is that the resulting cells wouldn’t sort properly. The right way merge cells is by using the ‘Center Across Selection’ option.

What is ‘Format Painter’ used for?

‘Format Painter’ allows you to copy the format from a cell and apply it on another cell (or range of cells).

How would you clear all the formatting without removing the cell contents?

Sometimes, you may want to remove all the formatting (colors, borders, font styling, etc.) and just have plain simple data. You can do that by clearing all the formatting in Excel.

To do this, you need to use the ‘Clear Formats’ option, which can be found in the Home tab in the editing group. It becomes visible when you click on the ‘Clear’ drop down.

Note there are other options as well – such as clear contents, clear comments, and clear Hyperlinks. In case you want to clear everything – use the ‘Clear All’ option.

What is Conditional Formatting?

Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value in less than 30 with a red color, you can do that with Conditional Formatting.

How would you highlight cells with negative values in it?

You can do this using conditional formatting.

Here are the steps:

• Select the cells in which you want to highlight the cells with negative value.
• Go to the Home tab and click on Conditional Formatting option.
• Go to Highlight Cell Rules and click on ‘Less Than’ option.
• In the ‘Less Than’ dialogue box, specify the value as 0 and the formatting.

How would you highlight cells with duplicate values in it?

You can do this easily using conditional formatting. Here are the steps:

• Select the data in which you want to highlight duplicate cells.
• Go to Highlight Cell Rules and click on ‘Duplicate Values’ option.

How would you highlight cells with errors in it?

In Excel, there can be different types of errors – such as #N/A, #DIV/0! , #VALUE! , #REF! , #NAME, and #NUM.

You can highlight all the cells that contain any of these errors using conditional formatting.

Here are the steps to highlight cells with errors:

• Select the data in which you want to highlight the cells with errors.
• Click on ‘New Rule’.
• In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’ option.
• In the formula field, enter =ISERROR(A1) , where A1 is the active cell in the selection.
• Click on the Format button and specify the color in which you want to highlight the cells.

How can you make text invisible in Excel?

There are multiple ways to do this:

• You can simply make the font white and it will appear as if it’s invisible.
• [Better Way] You can change make the text invisible by changing the custom format. Here are the steps to do this. Select the cell, press Control + 1 (hold the control key and press 1). This will open the Format Cells dialog box. In the Custom option, type ;;; in custom option field. This will make the text invisible (but it will still be there).

Excel Formula Questions

Below are some common Excel formula questions for interviews (click on the question to view the answer to it).

What is the order of operations used when evaluating formulas in Excel?

Following is the order of precedence in Excel formulas:

• P arenthesis ( B rackets)
• E xponentiation (^)
• M ultiplication or D ivision – both have equal precedence and is evaluated based on whichever comes first
• A ddition or S ubtraction – both have equal precedence and is evaluated based on whichever comes first

An easy way to remember this is by the acronym PEMDAS – which is the first alphabet of each operator.

What is the difference between a function and a formula in Excel?

A formula is a user-defined expression that calculates a value. A function is pre-defined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it.

For example, =A1+A2 is a formula and =SUM(A1:A10) is a function.

What, according to you, are the top 5 functions in Excel?

This question is often asked to understand comfort of a candidate with Excel functions.

While there are 450+ functions in Excel and there is no set criteria to select the top five, here are the ones I believe are worthy:

• VLOOKUP / XLOOKUP
• INDEX / MATCH

I have chosen the above functions as these are not very basic and are quite useful for someone who does analysis in Excel.

You can also consider the following functions – SUMPRODUCT , TEXT, SUM, AVERAGE, LEN/LEFT/RIGHT/MID.

Again, there is no right or wrong answer to this. Just make sure you know the functions that you mention.

You can find detailed explanations of most popular Excel functions here .

What is the difference between absolute and relative cell references?

In Excel reference refers to a cell reference – such as A1 or range reference – such as A1:A10.

Relative References: These are cell references that change when you copy and paste the formula that has the references. To give you a simple example, if you put =A10 in cell A1, and then you copy cell A1 and paste it in cell A2, the reference would change to A11. This happens as this is a relative cell reference and it changes relative to the cell it’s copied from.

Absolute References: These are the references that remain the same and don’t change copy and paste the formula that has the references. For example, if you put =$A$10 in cell A1 and then copy cell A1 and paste it in cell A2, the reference would still remain $A$10. The $ sign before the column alphabet and the row number makes it absolute.

You can read more about absolute and relative references here .

What are the different types of errors you can encounter in Excel?

When working with Excel, you can encounter the following six types of errors:

• #N/A Error : This is called the ‘Value Not Available’ error. You will see this when you use a lookup formula and it can’t find the value (hence Not Available).
• #DIV/0! Error: You’re likely to see this error when a number is divided by 0. This is called the division error .
• #VALUE! Erro r: The value error occurs when you use an incorrect data type in a formula.
• # REF! Error : This is called the reference error and you will see this when the reference in the formula is no longer valid. This could be the case when the formula refers to a cell reference and that cell reference does not exist (happens when you delete a row/column or worksheet that was referred in the formula).
• # NAME ERROR : This error is likely to a result of a misspelled function.
• #NUM ERROR : Number error can occur if you try and calculate a very large value in Excel. For example, =194^643 will return a number error.

How can you tackle errors when working with Excel Formulas?

There are various ways you can tackle the errors in Excel:

• You can highlight the errors using conditional formatting. This requires using the ISERROR function in conditional formatting.
• You can use the IFERROR function in Excel to get a specific value in case the formula returns an error.
• You can use ISERROR to get TRUE in case there is an error and FALSE in case there is not.
• You can use IFNA function to tackle the #N/A error.

Which function would you use to get the current date and time in Excel?

The following functions can be used:

• TODAY() – This function takes no argument and would return the current date value.
• NOW() – This function takes no argument and would return the current date and time value.

Remember that that dates and time are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates.

How can you combine the text from multiple cells using a formula?

To combine text from different cells , you can use any one of the following three methods:

• TEXTJOIN function – If you’re using Office 365 subscription, you will have the TEXTJOIN function available in your version. Click here to read how it works.
• CONCATENATE function – If you want to combine values in cell A1 and A2, you can use the formula =CONCATENATE(A1,A2)
• Ampersand (&) operator: This works just like the CONCATENATE function. To combine text strings in cell A1 and A2, use the formula =A1&A2

You can read more about joining strings in Excel here .

What formula would you use to find the length of a text string in a cell?

You can find the length of a string in a cell using the LEN function .

For example, if you want to know the length of the string in cell A1, you can use the formula =LEN(A1)

What is the syntax of the VLOOKUP function?

VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel question that I have seen in interviews.

Here is the VLOOKUP syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value –  this is the look-up value you are trying to find in the left-most column of a table. It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name.
• table_array –  this is the table array in which you are looking for the value. This could be a reference to a range of cells or a named range. In the score sheet example, this would be the entire table that contains score for everyone for every subject
• col_index –  this is the column index number from which you want to fetch the matching value. In the score sheet example, if you want the scores for Math (which is the first column in a table that contains the scores), you’d look in column 1. If you want the scores for Physics, you’d look in column 2.
• [range_lookup] –  here you specify whether you want an exact match or an approximate match. If omitted, it defaults to TRUE – approximate match .

If you’ve time, I recommend going this VLOOKUP function guide I created with 10 practical examples.

How would you get rid of leading / trailing / double spaces in a text in Excel?

To get rid of leading, trailing, and double spaces, you need to use the TRIM function .

For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula:

Note that it doesn’t remove single spaces between words.

Excel TRIM function does a good job in removing spaces in Excel, however, it fails when you have non-printing characters (such as line breaks ) in your data set. To remove non-printing characters, you can use a combination of TRIM and CLEAN functions.

If you have some text in cell A1 from which you want to remove spaces, use the below formula:

=TRIM(CLEAN(A1))

You can read more about it here .

What are the known limitations of the VLOOKUP function?

The VLOOKUP function is mighty useful, but it also has a few limitations:

• It can not be used when the lookup value is on the right. For VLOOKUP to work, the lookup value should always be in the left-most column. Now this limitation can be overcome by using it with other formulas, it tends to make formulas complex.
• VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column).  You can make the column number dynamic, but if you planning to combine two or more functions, why not use INDEX/MATCH in the first place.
• When used on large data sets, it can make your workbook slow.

You can read my comparison of VLOOKUP Vs. INDEX/MATCH here .

Here are some examples of using the INDEX MATCH combo in Excel.

When would you use the SUBTOTAL function?

When you’re working with tabular data, you can use the SUBTOTAL function to get variety of subtotals – such as AVERAGE , COUNT, MAX, MIN, STDEV.

One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on a criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only .

Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too.

What are volatile functions? Can you name a few?

A volatile function recalculates the formula again and again (whenever there is any change in the worksheet). This can slow down the workbook considerably.

A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculate. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing.

Here is a list of volatile formulas:

• Highly volatile: RAND(), NOW(), TODAY()
• Almost volatile: OFFSET(), CELL(), INDIRECT(), INFO()

BONUS TIP : Conditional formatting is also volatile. You should use it only the cells where it’s needed.

Excel Keyboard Shortcuts

Below are some common Excel interview questions about keyboard shortcuts that you might get asked in an interview (click on the question to view the answer).

What are the most useful keyboard shortcuts you use?

There are hundreds of Excel keyboard shortcuts. I am listing my top five shortcuts, but in case you have your own, feel free to use that.

• CONTROL D to fill down the content from the cell above.
• SHIFT Spacebar to select entire row (or CONTROL + Spacebar to select entire column).
• CONTROL – to delete cells/row/column. This opens the delete dialog box where you can choose what to delete.
• CONTROL ; to insert current date (CONTROL SHIFT ; inserts the current time).
• CONTROL PAGEUP/PAGEDOWN to cycle through the worksheets.

You can read the following tutorials in case you want to more keyboard shortcuts:

• 20 Excel Keyboard Shortcuts that will Impress Your Boss
• 200+ Excel Keyboard Shortcuts

What is the shortcut for opening the find and replace dialog box

• CONTROL F – This opens the Find and Replace dialog box with the Find tab selected.
• CONTROL H – This opens the Find and Replace dialog box with the Replace tab selected.

What is the shortcut for spell check?

F7 – This opens the spell-check dialog box.

What is the shortcut to quickly auto-sum rows/columns?

If you have numbers in a column/row, you can quickly get the sum by using this Excel keyboard shortcut.

What is the shortcut to open a new Excel workbook?

This works only when you have the Excel application open.

How can you select all the cells in the worksheet

You can use CONTROL A A – hold the control key and hit the A key twice.

In case you don’t have any data around the active cell, hitting the A key once would select the entire worksheet. But in case there is data, hitting the A key once select the entire data and hitting it again then selects all the cells in the worksheet.

How would you insert a new line in the same cell?

To insert a new line in the same cell, use the shortcut ALT Enter – hold the ALT key and press enter.

What’s the shortcut to insert a comment in Excel?

Select the cell in which you want to add the comment, hold the ALT key press the F2 key.

In case you have selected a range of cells, it will insert the comment in the active cell only.

Excel Pivot Table Questions

Below are some common Excel interview questions about Pivot Table that you might get asked in an interview (click on the question to view the answer).

What is a Pivot Table, and when would you use one?

A Pivot Table is a feature in Microsoft Excel that allows you to quickly summarize huge data sets (with a few clicks).

Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports.

To give you an example, if you have the 10,000 rows sales data from four different regions, you can use a Pivot Table to instantly find what are the total sales in each region. If you want to further drill down and see what are the sales of each product in each region, you can easily do that (it’s literally as easy as dragging a data point in a box).

You can read more about Pivot Tables here .

What are the various sections in a Pivot Table?

A Pivot table is made up of four different sections:

• Values Area : This is the area where the values are reported.

• Rows Area : The headings to the left of the Values area makes the Rows area.

• Columns Area : The headings at the top of the Values area makes the Columns area.

• Filter Area : It is an optional filter that you can use to further drill down in the data set.

What are slicers?

Slicers were introduced in Pivot Table in the 2010 version of  Excel.

A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box (as shown below).

What is a Pivot Chart

When you create a Pivot Table, you get the summary of your data. You can also plot this summary in a chart that is connected to the data.

This chart is called the Pivot Chart.

One big benefit of using a Pivot Chart is that it updates when you change the Pivot Table layout. For example, if you have the total sales by region, and you update the Pivot Table to show sales data for each product in the regions, the Pivot Chart would accordingly update.

What are the difference between Pivot Charts Vs Regular Charts?

While Pivot Charts are amazing and come with the ability to update when the Pivot Table updates, these are not as flexible as the regular charts.

In general, you can do a lot of customization in a regular Excel chart, but not in a Pivot chart. Also, if you customize a Pivot Chart, and then update the Pivot Table, you are likely to lose the customization.

Despite the limitations, Pivot Charts are useful and can help create quick views from a Pivot Table.

How can you refresh a Pivot Table?

To refresh a Pivot table, click on any cell in the Pivot Table, right-click and select Refresh.

Another way of refreshing a Pivot Table is to select any cell in the Pivot Table. It will enable the Pivot Table Tools tab. In the Analyze tab, click on ‘Refresh’.

You can read more about refreshing the Pivot Table here .

Can you group dates in Pivot Tables?

If you have date wise records, you can easily group these into the following segments:

• Hours / Minutes / Seconds

The option to group data in Pivot Table is in the Analyze tab, which becomes visible when you select a cell in the Pivot Table in the Rows area.

You can read more about grouping dates in Pivot Table here .

What is a Pivot Cache?

Pivot Cache is something that automatically gets generated when you create a Pivot Table.It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache.The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results.Pivot Cache enables this fast functioning of a pivot table.

Can you make a Pivot Table from multiple tables?

Yes, you can create one Pivot Table from multiple different tables. However, there needs to be a connection in these tables.

For example, if you have two tables, one that has date, Product ID and sale value, and another which has Product ID and Product Name, then you can combine these as the common column in Product ID.

Once you have connected these tables, you can create a Pivot table from these.

A crucial part of this is to set-up table relationships (where you specify the relationship between 2 tables).

You can read more about this here .

What are Calculated Fields in Pivot Table?

A calculated field allows you to add a column to the Pivot Table data where you can use the existing columns to do some calculations.

Let me give you a simple example.

Suppose you have a data set of retailers and you’ve created a Pivot Table as shown below:

The above Pivot Table summarizes the sales and profit values for the retailers.

Now, what if you also want to know what was the profit margin of these retailers (where the profit margin is ‘Profit’ divided by ‘Sales’).

You have a couple of options:

• You can go back to the original data set and add this new column. This is feasible but not the best solution as it alters the original data set and increases file size.
• You can do this calculation outside of the Pivot Table. This is alright if your Pivot Table is static, but if you update the table or change the layout, you’ll need to do the calculations again.
• You add a calculated field.

Consider Calculated Field as a virtual column that you have added using the existing columns from the Pivot Table.

There are a lot of benefits of using a Pivot Table Calculated Field:

• It doesn’t require you to handle formulas or update source data.
• It’s scalable as it will automatically account for any new data that you may add to your Pivot Table. Once you add a Calculate Field, you can use it like any other field in your Pivot Table.
• It easy to update and manage. For example, if the metrics change or you need to change the calculation, you can easily do that from the Pivot Table itself.

You can read more about the Pivot Table Calculated Field here .

Excel Charting Questions

Below are some common Excel interview questions about charting that you might get asked in an interview (click on the question to view the answer).

What is a Column chart?

A column chart is made up of vertical bars that is used to compare values over time or two compare values in different categories.

For example, you can use it to see how the sales have done over the years. Or you can use it to compare which product category has done better sales. Since you can see all the vertical bars at one go, it is easier to visually see and compare.

You can also create clustered column charts, where you can have multiple columns for the same category or year (something as shown below).

What is a Bar chart?

A bar chart is made up of horizontal bars that is used to compare values in different categories.

For example, you can use it to compare which product category has done better sales. Or what has been the response of a survey.

You can also create clustered bar charts, where you can have multiple bars for the same category (something as shown below).

What is a Line chart?

Line chart are useful when you want to show a trend over the years (or other time periods such as weeks, months, or quarters).

You can have multiple lines in a line chart. This would allow you to compare different categories over the same period of time (something as shown below).

What is a Scatter chart?

A scatter chart is used to compare two sets of values. For example, you can have data of different products on two KPIs, and you can plot the data on a scatter chart (as shown below).

This allows you to see what products are doing well on both the KPIs (the top right quadrant) or doing bad on both the KPIs (bottom-left quadrant).

You can see an example of scatter chart in action in the KPI Dashboard here .

Are Pie charts good? Should it used in reports/dashboard?

There are two school of thoughts.

There are some who completely hate Pie chart and recommend never to use these (such as this article ). And then there are some (including myself), who sometime use Pie charts in dashboards in reports.

There are many managers who are comfortable with pie charts and find these easy to read. So if you want to show a breakup of revenue by division (where you have only a few divisions), then you can use a pie-chart.

Let me be clear. Pie chart can be completely replaced by a bar chart. There is no additional benefit of using it. But in some cases, Pie charts make a good story (for example showing that one division is bringing in ~75% of the revenue as shown below).

You should avoid using Pie charts:

• In case the difference in values in not significant. The difference is better visualized by a bar chart.
• In case there are too many parts in a pie chart. In such cases, it can look cluttered.

What is a Waterfall chart? When would you use it?

A waterfall chart shows different values (positive and negative) that lead to the final result value. For example, if you’re analyzing companies net income, you can have all the cost components shown in the waterfall chart.

This will help you visually see how the value from revenue to net income is obtained when all the costs are deducted.

What are Combination charts?

Combination charts are those where you combine more than one chart type. A popular example of this is showing bar chart with a line chart.

Combination charts let you present and compare two different data-sets that are related to each other. For example, you may be interested in plotting the revenue figures of a company, and at the same time, also be able to show how the profit margin has changed. A combination chart (as shown below) is an apt way of doing this.

What is a secondary axes in a chart?

In a chart, there is a Y axis where you show the scale on which you can measure the chart (be a bar chart or line chart or others).

In cases where you have two show two different types of data set with a significant difference in value, you can use the secondary axes.

To give you an example, if you want to show the revenue and net income margin in the same chart, you need to show two different axes. This is because revenue numbers can be in thousands or millions, but net income margin would be in percentage and always less than 100%. In this case, you need to have two axes, one that shows scale for revenue and one that scale for net income margin.

So when you add another axes, it is called the secondary axes. In the below figure, the axes on the right is the secondary axes.

What is a Bullet chart? When should we use it?

Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target.

One of the best things about bullet charts is that it is power-packed with information and takes little space in your report or dashboards.

Below is an example of a bullet chart:

Note that bullet charts are not a default chart type in Excel, and you need to use a number of steps to create these.

You can read more about bullet charts here .

Excel Data Analysis Questions

Below are some common Excel interview questions about Data Analysis that you might get asked in an interview (click on the question to view the answer).

How to replace one value with another in Excel?

You can replace one value with another using the FIND & REPLACE feature in Excel.

To do this, select the data set and use the keyboard shortcut – CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box.

In this dialog box, you can specify the value you want to change and the replacement value.

If you’re interested in learning more about Find and Replace, click here .

What kind of data filters are available in Excel?

In Excel, you can filter a data set based on the kind of data.

The following types of data filters are available in Excel:

• Text Filter
• Number Filter
• Date Filter

You can apply filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon.

When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter. For example, if you have text data, it will show you filters related to text (such as text contains, begins with, ends with, etc.).

How can you sort data in Excel?

There is a sorting feature in Excel that can sort data based on text, numbers, or colors.

Here are some ways to sort data in Excel:

• Select the data and click on one of the two sort icons in the Data tab.
• Select the data and click on the Sort icon. It will open the sort dialog box and you can specify the column to sort and the criteria (ascending/descending).
• Apply data filter , and click on the filter. Along with the filter options, it also shows the data sorting options.

You can read more about data sorting here .

What is data validation?

Data Validation allows you to enter only that data in a cell that satisfies a criteria.

For example, if you want to only have numbers entered in a cell, then you can use Data validation to do this. In case any one enters something other than numbers, Excel will show an error and not allow that.

Data validation options are available in the data tab.

You can read more about data validation here .

Data validation can be really helpful when you’re creating forms in Excel. For example, if you want a user to enter only their age, you can use Data Validation to make sure the cell accepts a numeric value only.

How can you transpose a data set in Excel?

There are two popular ways to transposing data in Excel:

• Using Paste Special dialog box.
• Using the Transpose Function.

With Paste Special dialog box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option (as shown below).

You can read more about transposing data in Excel here .

How can you select all blank cells in Excel?

If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).

To do this, you need to use the Go To Special dialog box in Excel.

Here are the steps to select all blank cells in Excel:

• Select the entire data set (including blank cells)
• Press F5 (this opens the Go To dialog box)
• Click the ‘Special’ button (this opens the Go To special dialogue box)
• Select Blanks and click OK (this selects all the blank cells in your data set)

You can read more about selecting blank cells in Excel here .

How can you remove duplicates from a data set?

Excel has an in-built functionality that allows you to remove duplicate cells/rows in Excel.

You can find the option to remove duplicates in the Data tab.

Here are the steps to remove duplicates in Excel:

• Select the data.
• Click the Data tab and then click on the Remove Duplicates option.
• In the Remove Duplicates dialog box, if your data has headers, make sure the ‘My data has headers’ option is checked. Select the column from which you want to remove duplicates.

You can read more about removing duplicates in Excel here .

What is an advanced Filter in Excel?

Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set.

Here are some differences between the regular filter and Advanced filter:

• While the regular data filter will filter the existing data set, you can use Excel advanced filter to extract the data set to some other location as well.
• Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data, you can filter data on a criterion where the sales rep is Bob and the region is either North or South.

You can read more about Excel Advanced Filter here .

Can you sort multiple columns at one time?

Yes, you can sort multiple columns in Excel.

With multiple sorting, the idea is to sort a column and then sort the other column while keeping the first column intact.

Below is an example of multiple level sorting in Excel.

Note that Column A is sorted first in this case and then Column B is sorted. The final result has Column A values sorted, and Column B sorted for each item in Column A.

To do multiple level sorting, you need to use the Sort dialog box. To get that, select the data that you want to sort, click the Data tab and then click on the Sort icon.

In the Sort dialog box, you can specify the sorting details for one column, and then to sort another column, click on ‘Add Level’ button. This will allow you to sort based on multiple columns.

You can read more about multiple-column data sorting here .

What is a one-variable data table?

One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables.

For example, if you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.)

The option to set One-Variable data table is in the Data tab, in the What-if Analysis drop down.

You can read more about One-variable data table here .

What is a two-variable data table?

Two variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change two of the input variables.

For example, if you want to know how much on monthly installment change if you increase/decrease the number of months and the interest rate.

You can set up a two-variable data table for it that will show you a the final monthly installment based on different combinations of interest rate and number of months. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have.

The option to set Two-Variable data table is in the Data tab, in the What-if Analysis drop down.

You can read more about Two-variable data table here .

What is Scenario Manager?

Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variable that can change, then scenario manager is the way to go.

For example, if you’re a regional sales manager and have four areas under you, you can use scenario manager to create different scenarios (such as):

• None of the area shows any growth in sales.
• Area A grows 10% but the other 3 don’t grow.
• Area A and B grow by 10%, but other two don’t grow.

You get the idea.. right?

With scenario manager in Excel, you can easily create the scenarios and analyze these one by one or as a summary of all the scenarios.

You can read more about scenario manager here .

What is Goal Seek?

Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value.

For example, if you’re buying a car and you want to know how many month’s installment you should opt-for so that your monthly payment is not more than $500, you can do this using Goal seek.

You can read more about Goal Seek here .

What is a Solver?

Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek.

With Solver, you can specify what the constraints are and the objective that you need to achieve. It does the calculation in the back-end to give you a possible solution.

You can read more about Solver here .

Excel VBA Questions

Below are some common Excel interview questions about VBA that you might get asked in an interview (click on the question to view the answer).

What is VBA?

VBA stands for Visual Basic for Applications. It’s the programming language that you can use to automate tasks in Excel.

What are the benefits of using VBA in Excel?

While Excel has a lot of amazing features and functionalities, it may not have everything you need.

VBA allows you to enhance Excel’s ability by creating codes that can automate tasks in Excel. Below are some of the things you can do this VBA:

• Automate a set of tasks and save time.
• Create your own Excel functions (in case existing functions are not enough).
• Create and share your codes with other people so they can also automate tasks and save time.
• Create custom applications.

What is a macro in Excel?

A macro is a set of instructions written in the VBA language that Excel can understand and execute. A macro can be as simple as a single line or can be thousands of line long.

In many cases, people tend to use VBA code and macro interchangeably.

How can you record a macro in Excel?

Even if you know nothing about VBA, you can still create some macros and automate your work.

You can do this by recording a macro.

When you record a macro, Excel closely watches the steps you’re taking and notes it down in a language that it understands – which is VBA.

Now, when you stop the recording, save the macro, and run it, Excel simply goes back to the VBA code it generated and follows the exact same steps.

This means that even if you know nothing about VBA, you can automate some tasks just by letting Excel record your steps once and then reuse these later.

You can record a macro by using the Record Macro option which is available in the Developer tab in the ribbon .

Once you record the macro, Excel stores it with the name you specify and then you can easily reuse it as many times as you want.

You can read more about recording a macro here .

What are limitations of recording a macro in Excel?

While recording a macro is a great way to quickly generate code, it has the following limitations:

• You can’t execute a code without selecting the object. If you want the macro recorder to go to the next worksheet and highlight all the filled cells in column A, without leaving the current worksheet, then it won’t be able to do this. It’s because if I ask you to do this, even you won’t be able to do that (without leaving the current sheet). And if you can’t do it yourself, how will the macro recorder capture your actions. In such cases, you need to manually go and create/edit the code.
• You can’t create a custom function with a macro recorder. With VBA, you can create custom functions that you can use in the worksheet as regular functions. You can create this by writing the code manually.
• You can’t run codes based on Events. In VBA you can use many events – such as opening a workbook, adding a worksheet, double-clicking on a cell, etc, to run a code associated with that event. You can’t use a macro recorder to do this.
• You can’t create loops with a macro recorder . When you manually enter the code, you can leverage the power of loops in VBA (such as For Next , For Each Next, Do While, Do until). But you can’t do this when you record a macro.
• You can’t analyze conditions: You can check for conditions within the code using macro recorder. If you write a VBA code manually, you can use the IF Then Else statements to analyze a condition and run a code if true (or another code if false).

What is a UDF in Excel VBA?

A UDF is a User Defined Function in VBA. These are also called custom functions.

With VBA, you can create a custom Function (UDF) that can be used in the worksheets just like regular functions.

These are helpful when the existing Excel functions are not enough. In such cases, you can create your own custom UDFs to cater to your specific needs.

You can read more about User Defined Functions here .

What are Events in VBA?

In Excel VBA, an event is an action that can trigger execution of the specified macro.

For example, when you open a new workbook, it’s an event. When you insert a new worksheet, it’s an event. When you double-click on a cell, it’s an event.

There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed.

Excel automatically does this as soon as it notices that an event has taken place. So you only need to write the code and place it in the correct event subroutine.

You can read more about the Excel VBA Events here .

What are some of the useful loops in VBA?

There are the following loops in Excel VBA:

• For Next Loop
• Do While Loop
• Do Until Loop
• For Each Next Loop

You can read more about Excel VBA Loops here .

What are the different ways to run a macro in Excel?

You can use the following ways to run a macro in Excel:

• Assign the Macro to a Shape
• Assign the Macro to a Button
• Run a Macro from the Ribbon (Developer Tab)
• Run a Macro from the VB Editor
• Run a Macro using a Keyboard Shortcut
• Call a Macro from another Macro

You can read more about running a macro here .

What are UserForms?

A UserForm is a dialog box that you can design and build in Excel VBA.

Once created, these can be used in many ways in the Excel worksheet area:

• You can use it to get input from the user.
• You can make a form and record the entries in Excel.
• You can add buttons to the UserForm and give options to the user. You can also code these buttons so every time a user clicks on a button, a specific macro is executed.

What are add-ins?

An add-in is a file that you can load with Excel when it start.

When you create an add-in and install it in Excel, it opens whenever the Excel application opens. You can have many different macros in an add-in and whenever you open Excel , these codes are available for use.

This is useful as you can create an add-in and becomes available to all the workbooks. So if there are some tasks that you need to do often, you can automate these by writing a macro and then saving these as add-ins. Now no matter what Excel you open, you can use the macro.

Another benefit of add-in is that you can share the add-in file with others as well. All they need to do is install it once and they will also have the same macros available to them

You can read more about creating an add-in here .

Excel Dashboard Questions

Below are some common Excel interview questions about dashboards that you might get asked in an interview (click on the question to view the answer).

What is difference between a dashboard and a report?

A report is meant to provide relevant data. It could be a report that has all the data or can also have a few charts/visualizations. Examples of reports can be sales transaction data or employee survey data.

A dashboard is meant to answer questions using the data. It could be to show which regions are performing better in sales or which areas are lagging in terms of employee feedback. These dashboards could be static or interactive (where the user can make selections and change views and the data would dynamically update).

You can read more about Excel Dashboards here .

What are some of the questions you should ask a client/stakeholder before creating a dashboard?

While the questions would depend on a case to case basis, there are few high-level questions that you should ask when creating a dashboard in Excel.

• What is the Purpose of the Dashboard?
• What are the data sources?
• Who will use this Excel Dashboard?
• How frequently does the Excel Dashboard needs to be updated?
• What version of Office does the client/stakeholder uses?

There can be many such questions. The intent here is to be clear on what the dashboard needs to be and what purpose it serves.

What are some interactive controls you can use in dashboards?

Excel has a number of interactive tools that can be used in a dashboard:

• Drop Down Lists
• Check-boxes
• Scroll bars
• Radio Buttons

Apart from these regular interactive tools, you can also use VBA to add more functionality to your dashboard.

What are some useful chart types that you can use in a dashboard?

One of the need when creating a dashboard is to show relevant data with visuals. Charts that can tell a good story and show relevant information are more suited for dashboard.

Since a dashboard is usually expected to fit in a single screen, there is limited space for data and visuals. In such cases, combinations charts come handy.

The following charts can be useful when creating a dashboard:

• Combination charts
• Scatter Charts
• Bullet Chart
• Waterfall chart

What are some best practices when creating a dashboard in Excel?

Here are some best practices when creating dashboard in Excel:

• Convert Tabular Data into Excel Tables:  Creating dashboards using an Excel Table as the data source is way easier. Unless you have extremely strong reasons against it, always convert back-end data into an Excel Table.
• Numbering your Charts/Section: This helps when you’re presenting the dashboard and referring to different charts/tables. It’s easier to ask people to focus on chart numbered 2, instead of saying the line chart or bar chart on the top-left.
• Restrict Movement in the dashboard area : While an Excel worksheet is huge, it’s better to remove all the rows/columns except the ones that have your dashboard.
• Freeze Important rows/column : In case you want some rows/columns to always be visible, you can freeze these.
• Make Shapes/Charts Stick : A user may end up changing row/column width. You don’t want the shapes and charts to get misaligned and move from their original place. So it’s better to make them stick to their position.
• Provide a User Guide : It’s a good idea to include a separate sheet that has the details on how to use the dashboard. This becomes more useful when you have interactive controls in the dashboard.
• Save Space with Combination Charts : Since there is limited space in a dashboard (as you need to fit it in one screen), using combination charts can save you space.
• Use Symbols & Conditional Formatting : You can make dashboard more visual and easy to read by using symbols and conditional formatting. For example, it’s easier to see the lowest value in a table when it’s highlighted in red, instead of going through all the values one by one.
• Use colors wisely to show contrast: When you want to highlight a data point in a table or a bar in the chart, it better to make it stand out by highlighting it in eye-catching color.

Other Excel Questions

These are the questions that I could not fit in any of the above categories. So I am putting all these together here (click on the question to view the answer).

What is an Excel Table?

An Excel Table is a feature in Excel. This is not the same as tabular data.

When you convert tabular data into an Excel Table, there are a few additional features that get added to it that can be really useful.

According to Microsoft Help Site – “A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.”

You can read more about Excel Tables here .

What are the benefits of using Excel Table?

When you convert tabular data into Excel Table, the following features are automatically added to the data:

• You can use the filter icon on each column header to sorting and filter the data.
• You can easily apply styles to a table and format it. There are a lot of in-built styles available that you can use with a single click.
• You can use Table Name and Column Names instead of cell references in formulas.
• When you add a new row/column to the tabular data, the Excel Table automatically expands to cover the new row/column as a part of it. And since you can use Table/Column names in formulas, you don’t need to go and update formulas when new rows/columns are added. Excel Table automatically accounts for it.

What are structured references?

When you use an Excel Table, you don’t need to use the cell references. Instead, you can use the Table name or the column names. These references are called structured references.

What are the commonly used file formats in which an Excel file can be saved?

There are a lot of file formats in which you can save your Excel workbook. Some commonly used ones are:

• .XLSM (when you file has macros)
• .XLB (binary format)
• .XLA (for add-ins)

How to reduce the size of an Excel file?

There are many ways you can reduce the file size of an Excel Workbook:

• Delete unused data
• Delete unused worksheets
• Remove images or compress these images
• Remove unnecessary formatting
• Remove Pivot Tables you don’t need. This will also help clear the Pivot Cache.
• Save file in the .XLSB format

Read more : 8 ways to reduce file size in Excel

What steps can you take to handle slow Excel workbooks?

You can use the following techniques to handle slow Excel workbooks:

• Avoid Volatile Functions such as INDIRECT , OFFSET, etc.
• Use Helper Columns instead of array formula
• Use Conditional formatting only when absolutely necessary (as it is volatile too).
• Use Excel Tables and Named Ranges.
• Convert Unused Formulas to Values .
• Keep All Referenced Data in One Sheet.
• Avoid Using Entire Row/Column in References.
• Use Manual Calculation Mode.

You can read more how to handle slow Excel workbook here .

How many rows and columns are there in an Excel worksheet?

An Excel sheet (2007 and later versions) has:

• 16,384 columns
• 1,048,576 rows

How to add/delete rows in Excel?

To add new rows, right-click on any cell where you want insert the row and click on the Insert option. This will open a dialog box which you can use to insert new rows.

Similarly, to delete cells/rows/columns, select these, right-click and select Delete.

How can you zoom in or zoom out in Excel?

There are Zoom In and Zoom Out buttons in the status bar in Excel. You can click the plus sign to Zoom In and minus sign to Zoom Out.

You can also hold the Control Key and then use the scroll wheel in the mouse to Zoom in and Zoom out.

Also, in the View tab, there are option to Zoom in and out in Excel.

How to protect a sheet In Excel?

You can protect a sheet by clicking the Review tab and then clicking the Protect Sheet option.

This opens a dialog box where you can set a password. If you don’t want to set a password, you can leave it blank.

What are named ranges? What are its benefits?

A named range is a feature in Excel that allows you to give a cell or a range of cells a name. Now you can use this name instead of using the cell references.

Using a named range makes it easier when you’re working with formulas. This becomes specially useful when you have to create formulas that use data from multiple sheets. In such cases, you can create named ranges and then use these instead of the cell references.

You can give descriptive names to these named ranges – which also makes it easier to read and understand the formula. For example, you can use =SUM(SALES) instead of =SUM(C2:C11), which will instantly tell you what the formula is about.

You can read more about Named Ranges here .

Can you make the header rows/columns stick and be visible when you scroll?

When you’re working with large data sets, when you scroll to the bottom or to the right, the header rows and columns disappear. This sometimes makes it difficult to understand what a data point is about.

By using Freeze Panes option in Excel, you can make the header rows/columns be visible when you scroll away to the far off data points.

The Freeze Panes options are available in the View tab in the Excel ribbon.

You can read more about Excel Freeze Panes here .

How would you identify cells that have comments in it?

The cells that have a comments added to it are flagged by a small red triangle at the top-right of the cell. When you hover the cursor over the cell, the comments becomes visible.

How would you save an Excel worksheet as a PDF?

To save a worksheet as a PDF document , you can specify the file type as PDF when saving the file.

To do this, click on the File tab and then click on Save As.

In the Save As dialog box, select the location where you want to save the file and use the Save As type drop-down to select PDF. This will save the entire worksheet as a PDF document.

How to create a hyperlink in Excel?

To create a hyperlink , select the cell in which you want the hyperlink and use the keyboard shortcut Control K (hold the control key and press the K key).

This will open the Insert Hyperlink dialog box where you could specify the URL.

You can also get the option to add the hyperlink when you right-click on the cell.

When would you consider switching from automatic to manual calculation in Excel?

While in most cases automatic calculation mode is the way to go, in case you have a formula heavy file where recalculation takes a lot of time every time you change anything in the sheet, then you can switch to manual calculation.

Once you have switched to manual calculation, you need to refresh every time you want the sheet to recalculate.

What is Flash Fill?

Flash Fill is an amazing tool that was added in Excel 2013 and is available in all version after that.

You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you.

Some simple examples of using Flash Fill could be to get the first name from the full name, get name initials, format phone numbers, etc.

You can read more about Flash Fill here .

What is a Fill Handle?

Fill handle is a tool that you can use to autocomplete lists in Excel. For example, if you have to enter numbers 1 to 20 in cell A1:A20, instead of manually entering each number, you can simply enter the first two numbers and use the fill handle to do the rest.

Fill Handle is the small square you would see when you select two or more than two cells in Excel.

When you hover your cursor on Fill handle, the cursor changes to a plus icon. Now you can hold the left-mouse key and drag it to add more numbers in the series.

What are wildcard characters in Excel?

There are three wildcard characters in Excel:

• * (asterisk) – It represents any number of characters. For example, Ex* could mean Excel, Excels, Example, Expert, etc.
• ? (question mark) – It represents one single character. For example, Gl?ss could be Glass or Gloss.
• ~ (tilde) – It is used to identify a wildcard character (~, *, ?) in the text. For example, let’s say you want to find the exact phrase Excel* in a list. If you use Excel* as the search string, it would give you any word that has Excel at the beginning followed by any number of characters (such as Excel, Excels, Excellent). To specifically look for excel*, we need to use ~. So our search string would be excel~*. Here, the presence of ~ ensures that excel reads the following character as is, and not as a wildcard.

Wildcard characters are useful when you want to use it in formulas or while filtering data.

You can read more about Wildcard Characters here .

What is a Print Area and how can you set it in Excel?

A print area is a range of cells (contiguous or non-contiguous) that you designate to print whenever you print that worksheet. For example, instead of printing the entire worksheet, if I only want to print the first 10 rows, I can set the first 10 rows as the print area.

To set the Print Area in Excel:

• Select the cells for which you want to set the Print Area.
• Click on Page Layout tab.
• Click on Print Area.
• Click on Set Print Area.

You can read more about Print Area here .

How can you insert Page Numbers in Excel?

You can insert page numbers using the Page Setup dilaog box.

Here are the steps to do this:

• Click the Page Layout tab,
• In the Page Setup category, click on the dialog box launcher icon (which is the small tilted arrow icon at the bottom right of the group).
• In the Page Setup dialog box, click on Header/Footer tab.
• Select the Page Number format from the Footer drop-down.

You can read more about Page Numbers in Excel here .

While I have tried to keep this ‘Excel Interview Questions & Answer’ guide error-free, in case you find any errors in any of the questions, please let me know in the comments area.

Related : Free Online Excel Training (7-part video course)

You May Also Like the Following Excel Tutorials:

• Reduce Excel File Size .
• Recover Unsaved Excel Files.
• How to Describe Excel Skills in a Resume?
• 20 Advanced Excel Functions and Formulas

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

32 thoughts on “100+ Excel Interview Questions & Answers”

It is definitely one of the best learning gifts of the new year. Thanks a ton, Sumit!

how to specify to select or exclude blank date in criteria range of advanced filter? =, ,, with date works.

Very useful

Really helpful

Want to learn sum.product as a vlook up …

Absolutely awesome. Thank you for sharing

Very helpful

Awesome information.

EXCELLENT KNOWLEDGE SOURCE

One of the best site of excellent information of Excel

very valuable work

Can you give me a question about screen tips

Great Job.If You can Share these Tutorial to download in a PDF file. It is much appreciated

Awesome information. Helped me a lot in preparation.

Excellent…….Really useful. Thanks a lot.

I appreciate this useful points , thanks alot .

Unable to find what @ is used for in a formula.

First you should select a cell to find in worksheet overall then press “Ctrl+F” and type word that you want to find in dialog box. After typing word press “Enter” to find one by one or click “Find All” button to show all at a time.

Informative and useful

how use formula in excel of took total calls from 1801 to 2000, each of which calls at the rate of 50 paise per round, so how much money we received.

very useful

Its too good.

hello Experts Can you help me below. I have multiple pivot tables in one big workbook. But whenever i save as file with different name and try to update the pivot table, it is still keeps referring to old file name. It is painful for me to change data source everytime. How to fix this? why it is still keeps referring to old file? i am using excel 2016 pro plus.

thanks a lot

Excellent!!

You are so amazing Sumit…. I keep learning and practicing whatever material you share.

Great Job sir!!!

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Top 25 Microsoft Excel Interview Questions and Answers

Prepare for your next job interview with our comprehensive guide on Microsoft Excel interview questions and answers. Gain insights into frequently asked questions, practical exercises, and top tips for success.

Microsoft Excel, a cornerstone of the Microsoft Office suite, is an essential tool for businesses worldwide. Its powerful capabilities extend beyond simple data entry and number crunching to encompass complex calculations, data analysis, chart creation, and even programming through VBA (Visual Basic for Applications). Whether you’re in finance, marketing, HR, or any other field, proficiency in Excel can significantly enhance your productivity and effectiveness.

Excel’s versatility makes it a vital skill for many job roles today. From basic functions like SUM and AVERAGE to more advanced features such as PivotTables and Macros, understanding Excel’s wide array of functionalities can give you a competitive edge in the job market.

In this article, we’ve compiled a comprehensive list of common interview questions about Microsoft Excel. These range from fundamental concepts to more intricate aspects of this powerful software. Our aim is to equip readers with a solid foundation of Excel knowledge that will prove invaluable in their professional journey.

1. Can you explain the process of creating a pivot table in Excel and the advantages of using one?

A pivot table in Excel is created by selecting the data range, navigating to ‘Insert’ tab and clicking on ‘PivotTable’. A dialog box appears where you confirm your data selection and choose a location for the PivotTable. Click ‘OK’ to create an empty PivotTable.

The advantages of using a PivotTable include quick data analysis and summarization without complex formulas. It allows easy reorganization of data by dragging and dropping columns into different positions. You can filter and sort data within the table, making it easier to focus on specific information. Additionally, it provides a dynamic way to present data, as changes in source data are reflected immediately.

2. What is the difference between COUNT, COUNTA, COUNTIF, and COUNTBLANK functions?

COUNT function in Excel calculates the number of cells containing numerical data within a specified range. COUNTA, on the other hand, counts all non-empty cells regardless of content type, including text and error values.

The COUNTIF function extends this functionality by allowing for condition-based counting. It counts the number of cells that meet a specific criterion defined by the user. For example, it can count how many cells contain numbers greater than 10.

Lastly, the COUNTBLANK function is used to calculate the number of empty cells within a given range. This function is particularly useful when you need to identify missing data in large datasets.

3. What is an Excel Array Formula? Can you provide an example?

An Excel Array Formula is a tool that performs multiple calculations on one or more items in an array. It can return either a single result or multiple results. Array Formulas are used when you want to perform complex calculations, which regular formulas cannot handle.

For example, consider the following data set:

A1: 5 A2: 10 A3: 15

If we wanted to calculate the sum of squares for this data set, we could use an array formula. The formula would be {=SUM(A1:A3^2)}. This formula first squares each item in the range (yielding 25, 100, and 225), then adds them together for a final result of 350.

4. How would you create a dynamic drop down list in Excel?

To create a dynamic drop-down list in Excel, start by typing the list of values you want to appear in the drop-down list into cells within a single column or row. Then, select the cell where you want the drop-down list to appear and go to Data > Data Validation. In the dialog box that appears, set ‘Allow’ to ‘List’. Click on the Source box and then select the range of cells containing your list of values. To make it dynamic, use an Excel Table for the source data and refer to the table name as the source. This way, when new items are added to the table, they will automatically be included in the drop-down list without needing to adjust the source range.

5. Can you explain how to use the VLOOKUP function? How does it differ from the HLOOKUP function?

VLOOKUP, or Vertical Lookup, is a function in Excel that searches for specific information in your spreadsheet’s vertical column. It requires four arguments: lookup_value (the value you’re searching for), table_array (the range of cells where the VLOOKUP will search), col_index_num (the column number in the range containing the return value), and [range_lookup] (optional – if true, returns an approximate match; if false, exact match).

Here’s a coding example: =VLOOKUP(A2,B2:D5,3,FALSE) This formula looks for A2 value within B2 to D5 range and returns the corresponding value from the third column.

HLOOKUP, or Horizontal Lookup, operates similarly but scans horizontally across rows instead of columns. The syntax remains similar with row_index_num replacing col_index_num.

Coding example: =HLOOKUP(A1,A1:D4,3,FALSE) This formula seeks A1 value within A1 to D4 range and retrieves the corresponding value from the third row.

6. Could you describe the process of creating a macro in Excel and its benefits?

A macro in Excel is a sequence of commands that automate tasks. To create one, navigate to the ‘Developer’ tab and select ‘Record Macro’. Assign a name, shortcut key, and description for your macro. Choose where to store it: Personal Macro Workbook (for use in all workbooks), New Workbook, or This Workbook. Click ‘OK’ to start recording. Perform the actions you want to automate then click ‘Stop Recording’.

Macros offer several benefits. They increase efficiency by automating repetitive tasks, reducing human error. Macros can perform complex calculations quickly, saving time. They also allow customization of Excel functions to suit specific needs.

7. What methods would you use to consolidate data from different Excel files into one?

To consolidate data from different Excel files into one, I would use the following methods:

1. Manual Copy-Paste: This is a simple method where you open each file and copy the required data then paste it in the destination file.

2. Use of ‘Consolidate’ Feature: Excel has an in-built feature called ‘Consolidate’ under the Data tab which allows combining data from multiple files. You can choose to consolidate by position, category or formulae like sum, average etc.

3. Power Query: It’s a powerful tool for data consolidation. You load all your files into Power Query Editor, append them together, perform necessary transformations and load back to Excel.

4. VBA Macros: If the task is repetitive, writing a VBA script could be beneficial. The macro can be programmed to open each file, extract data and consolidate it in one place.

8. How would you use conditional formatting in Excel? Can you provide a real-world example?

Conditional formatting in Excel is used to automatically apply a format, such as color-coding cells, based on the value within each cell. This feature aids in visualizing data and identifying trends or anomalies.

To use conditional formatting, select the range of cells you want to format. Then, go to Home > Conditional Formatting > New Rule. In the dialog box, define your rule criteria. For instance, if you want to highlight cells with values above 100, choose ‘Format only cells that contain’, set ‘Cell Value’ > ‘greater than’ > ‘100’. Click Format to specify how these cells should look, like setting fill color to red. Press OK twice to apply the rule.

A real-world example could be tracking sales performance. If you have a list of monthly sales figures for different products, you can use conditional formatting to highlight any product that sold more than 500 units in a month. This quickly identifies high-performing products.

9. How can you prevent errors from appearing in your Excel document?

To prevent errors in Excel, ensure data validation by setting rules for the input type. Use formulas like IFERROR and ISERROR to handle errors that may arise from calculations. Regularly use error checking feature under ‘Formulas’ tab which identifies common issues. Utilize conditional formatting to highlight potential problem areas. Always cross-verify data and results for accuracy. Automate tasks with macros but ensure they are correctly coded. Keep your Excel updated as newer versions have better error handling capabilities.

10. Can you explain how to use the INDEX-MATCH function? How does it differ from VLOOKUP?

The INDEX-MATCH function in Excel is a powerful tool for data lookup. It uses two separate functions: INDEX returns the value of a cell in a specified range, while MATCH provides the relative position of an item within an array.

To use it, first identify your lookup value, row/column array and return array. The syntax is =INDEX(return_array,MATCH(lookup_value,row/column_array,0)). The ‘0’ indicates exact match.

Unlike VLOOKUP, which only looks rightwards from the column containing the lookup value, INDEX-MATCH can look both ways. This means you don’t need to rearrange columns for successful lookup. Additionally, INDEX-MATCH isn’t affected by column insertions/deletions, unlike VLOOKUP which would return incorrect results if columns are altered.

11. How would you handle large data sets that exceed Excel’s row or column limit?

To handle large data sets exceeding Excel’s row or column limit, I would use Power Query to import the data. This tool allows for data transformation and cleaning before loading it into Excel. If the data is too large even for Power Query, I’d consider using a database system like SQL Server or Access, which can handle larger datasets. Alternatively, I could split the data across multiple worksheets or workbooks. For analysis purposes, I might use Power Pivot, which can manage millions of rows of data efficiently.

12. Can you describe the purpose and usage of Solver Add-In in Excel?

The Solver Add-In in Excel is a tool used for optimization and simulation. It helps to find the best possible solution, given certain constraints, for complex problems that involve multiple variables. This can be useful in various fields such as finance, logistics, or operations where optimal solutions are sought.

For instance, in business, it could be used to maximize profit by determining the ideal combination of products to sell, considering factors like production cost, selling price, and demand. In logistics, it might help minimize transportation costs by finding the most efficient routes.

To use Solver, you first define your objective (maximize, minimize, or achieve a specific value), decision variable cells (the values Solver adjusts to reach the objective), and constraints (limitations on the decision variables). Then, Solver uses iterative algorithms to find the optimal solution.

13. How you can manage and maintain data accuracy and consistency in Excel?

Data accuracy and consistency in Excel can be managed through various methods. Data validation is a key tool, allowing you to set rules for the type of data or the values that users can enter into a cell. For instance, you can restrict entries to a certain range of numbers, dates, or even text length.

Another method is conditional formatting which highlights cells with specific characteristics, making it easier to spot errors. You can also use formulas like COUNTIF or VLOOKUP to cross-check data.

Excel’s ‘Remove Duplicates’ feature helps maintain uniqueness in your data. It identifies and eliminates duplicate rows, ensuring data integrity.

For large datasets, Power Query is useful. It allows you to connect to multiple sources, clean, transform, and consolidate data before loading it into Excel. This ensures consistent data preparation steps are applied each time data is refreshed.

Lastly, creating a data dictionary detailing what each column represents aids in maintaining consistency across users who interact with the spreadsheet.

14. How would you use Power Query and Power Pivot in data analysis in Excel?

Power Query is used for data discovery, connecting to a variety of sources and reshaping messy data. It allows you to extract data from different sources, clean it, transform it, and load it into Excel or Power Pivot models. For instance, if you have sales data in multiple files, Power Query can consolidate all the data into one table.

On the other hand, Power Pivot is an add-in that enables advanced data modeling. It’s used for performing powerful data analysis and creating sophisticated data models. With Power Pivot, you can create calculated columns and measures using formulas, build relational data models, and produce interactive reports.

For example, after consolidating your sales data with Power Query, you could use Power Pivot to calculate total sales per region or average sales per product category.

15. Can you demonstrate how to use nested functions? Please provide an example.

Nested functions in Excel are used when a single function cannot perform the required calculation. They involve using one function as an argument within another function.

For example, let’s consider we have a list of sales figures and want to find out the highest sale after tax (15%) has been deducted. We can use the MAX and ROUND functions together for this purpose.

The formula would be: =MAX(A1:A10)*(1-0.15). However, this might return a number with many decimal places. To round it off to two decimal places, we nest the previous formula inside the ROUND function like so:

=ROUND(MAX(A1:A10)*(1-0.15), 2)

This formula first calculates the maximum value from A1 to A10, deducts 15% tax, and then rounds off the result to two decimal places.

16. How do you handle errors that appear when using the IF function with the AND or OR functions?

When handling errors in Excel using the IF function with AND or OR functions, it’s crucial to use error-handling formulas. For instance, ISERROR and IFERROR can be used to manage errors. These functions check if an error exists and return a custom message or perform a different calculation if true.

17. Can you explain how to implement and use data validation in Excel?

Data validation in Excel is a feature that allows users to control the type of data or values that can be entered into a cell. To implement it, select the cells you want to apply validation to and go to Data > Data Validation. In the dialog box, choose settings for your criteria. For instance, if you only want numbers between 1-100, select ‘Whole number’, set ‘Data’ to ‘between’, and specify minimum and maximum as 1 and 100 respectively. You can also input custom formulas under ‘Custom’.

To use this feature, simply try entering data into the validated cells. If the entry doesn’t meet the criteria, an error alert will appear. This helps maintain data integrity by preventing incorrect entries.

18. What techniques do you use to debug an Excel VBA code?

To debug Excel VBA code, I use the built-in debugging tools in the Visual Basic for Applications editor. The “Debug” menu provides several options such as ‘Step Into’, ‘Step Over’, and ‘Step Out’ to control execution flow. ‘Step Into’ is used when we want to observe the behavior of a function or subroutine line by line. ‘Step Over’ executes the current line and goes to the next one, useful when we know that the current line has no errors. ‘Step Out’ completes the current procedure’s execution and returns to the calling procedure.

I also use breakpoints to pause execution at specific lines. This allows me to inspect variables and their values at different stages of execution. Additionally, the ‘Immediate Window’ (Ctrl+G) can be used to evaluate expressions without altering the code itself.

The ‘Watch Window’ lets me monitor the value of certain variables and expressions during execution. Lastly, error handling techniques like ‘On Error Resume Next’ and ‘On Error GoTo’ help manage unexpected errors and direct the program flow accordingly.

19. Can you describe a situation where you would use a circular reference?

A circular reference in Excel is when a formula refers back to its own cell either directly or indirectly. This can be problematic as it may cause the formula to calculate endlessly, leading to incorrect results and potential program crashes. However, there are instances where intentional use of circular references can be beneficial.

One such situation is when you’re dealing with iterative calculations that require repeated approximation until reaching a desired level of accuracy. For example, in financial modeling, calculating interest rates or depreciation values often involves complex equations where the result depends on an initial guess value. By setting up a circular reference and enabling iterative calculation in Excel settings, each recalculation refines the guess value closer to the accurate result.

20. How would you explain the difference between relative, absolute, and mixed cell references?

Relative cell references change when a formula is copied to another cell. For example, if you have the formula =A1+B1 in cell C1 and copy it to C2, the formula will adjust to =A2+B2.

Absolute cell references remain constant regardless of where they are copied. They’re denoted by dollar signs before both the column letter and row number (e.g., $A$1). If you copy the formula =$A$1+$B$1 from C1 to C2, the formula remains unchanged.

Mixed cell references contain both relative and absolute components. There are two types: – When the column reference is absolute and the row is relative (e.g., $A1), copying across rows adjusts the row number but not the column. – When the row reference is absolute and the column is relative (e.g., A$1), copying across columns adjusts the column letter but not the row number.

21. Can you explain the steps to create and manage a dashboard in Excel?

To create a dashboard in Excel, start by preparing your data. Ensure it’s clean and organized for easy analysis. Next, insert a PivotTable to summarize the data. Go to ‘Insert’ tab, select ‘PivotTable’, choose your data range and place it on a new worksheet.

Create charts from the PivotTable by selecting the data you want to visualize, then go to ‘Insert’ > ‘Chart’. Customize these charts as per your needs. Repeat this process until all necessary data is visualized.

For managing the dashboard, use slicers for interactive filtering. To add a slicer, click anywhere inside a PivotTable, go to ‘PivotTable Analyze’ > ‘Insert Slicer’. Choose the column to filter by. Position and customize the slicer using options under ‘Slicer Tools’.

Use ‘Grouping’ feature to manage large amounts of data. Right-click on a date field in the PivotTable, select ‘Group’. Choose the grouping basis (months, quarters, etc.).

Finally, arrange all elements neatly on one page.

22. What are the limitations of using Excel as a data analysis tool and how would you overcome them?

Excel has several limitations as a data analysis tool. It can only handle up to 1,048,576 rows of data, making it unsuitable for large datasets. Additionally, Excel lacks advanced statistical functions and is prone to human error due to manual data entry. Its inability to support multi-user collaboration also hinders efficiency.

To overcome these limitations, one could use more robust tools like SQL or Python for handling larger datasets. These languages offer advanced statistical functions and are less prone to human errors. For collaborative work, cloud-based platforms such as Google Sheets provide real-time multi-user access. However, if sticking with Excel, using data validation rules can minimize errors, while Power Query and Power Pivot can enhance its analytical capabilities.

23. How can using named ranges be beneficial while working in Excel?

Named ranges in Excel enhance readability and understanding of formulas by replacing cell references with descriptive names. They simplify navigation, as you can quickly jump to a named range using the name box. Named ranges also make data validation easier by defining dropdown list values. They facilitate dynamic referencing, where the range automatically adjusts when new data is added or removed. This feature is particularly useful for charts that need to update dynamically based on changing data sets.

24. Can you describe how to use the SUMPRODUCT function and provide a practical example?

The SUMPRODUCT function in Excel multiplies corresponding components in the given arrays, and returns the sum of those products. It’s a versatile tool for various tasks such as weighted averages or conditional sums.

For instance, consider two columns: Quantity (A2:A5) and Price per unit (B2:B5). To calculate total cost, use =SUMPRODUCT(A2:A5, B2:B5). This formula multiplies each quantity by its price and adds up these products to give the total cost.

25. How would you optimize an Excel workbook for performance when handling large data sets?

To optimize an Excel workbook for large data sets, consider the following strategies. Reduce formula calculations by using manual calculation mode and only recalculate when necessary. Minimize volatile functions as they recalculate every time a change is made in the workbook. Use helper columns to break down complex formulas into simpler ones, reducing processing load. Avoid array formulas which can be resource-intensive. Opt for INDEX MATCH over VLOOKUP due to its lower processing demand. Limit usage of conditional formatting and pivot tables as they increase file size and slow down performance. Lastly, save your workbook as a binary (.xlsb) file to reduce file size without losing data or functionality.

Top 25 Joomla Interview Questions and Answers

Top 25 ibm db2 interview questions and answers, you may also be interested in..., top 25 innodb interview questions and answers, top 25 unix programming interview questions and answers, top 25 android location services interview questions and answers, top 25 .net authentication interview questions and answers.

• Onsite training

3,000,000+ delegates

15,000+ clients

1,000+ locations

• KnowledgePass
• Log a ticket

01344203999 Available 24/7

Top 40+ Excel Interview Questions and Answers

This comprehensive blog will discuss the most commonly asked Excel Interview Questions and some sample answers to help you in your following interview. These questions will dive into your technical expertise and working knowledge of Microsoft Excel. Read this blog further to learn more about these interview questions!

Exclusive 40% OFF

Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

• Microsoft Excel VBA and Macro Training
• Microsoft Excel Course
• Excel Training with Gantt Charts
• Business Analytics with Excel Course

Are you preparing to demonstrate your Microsoft Excel skills and knowledge in an upcoming interview? Need help with how to do this effectively? Don't worry! This comprehensive blog is here to guide you. It covers many of the most commonly asked Excel Interview Questions and provides sample answers to help you confidently prepare.

These questions are not just a test of your skills but an opportunity to showcase your expertise and stand out from other candidates. In today's competitive job market, where Microsoft Excel is a crucial tool in almost every organisation, a strong command of Excel can be a career game-changer. It can unlock doors to the best opportunities for you. So, why wait? Let's dive into this blog and take a step towards your career advancement! 

Table of Contents 

1) Beginner-level Excel Interview Questions 

2) Intermediate Excel Interview Questions 

3) Advanced-level Excel Interview Questions 

4) Conclusion 

Beginner-level Excel Interview Questions 

Here are some beginner-level Excel Interview Questions that will help you understand how you can answer these questions seamlessly: 

1) Define a spreadsheet. 

A spreadsheet is a computerised table of cells arranged in rows and columns to process data. The intersections of horizontal and vertical lines are called cells, which can hold text, numbers, or formulas.  

2) What is a cell address in Excel? 

A cell address identifies a particular cell in a worksheet containing the cell's column letter and row number, such as A1 or B2. 

3) State the difference between Excel's absolute and relative cell referencing. 

Absolute cell referencing involves using a cell address that does not change when the cell is copied (e. g. Absolute cell referencing refers to a specific cell anywhere in the worksheet ($A$1), whereas relative cell referencing is relative to the cell in which it is being copied (e. g. , A1). 

4) Can you sort data in Excel? 

With this question, the interviewer wants to know how to sort data in Excel.  

You can use this as your sample answer: "To sort the data, I will pick the range of cells I would like to sort and then go to the Data tab. Next, I will select “Sort A to Z” or “Sort Z to A” for Ascending or Ascending order, respectively." 

5) Can you freeze panes in Excel? 

This question assesses your knowledge of how to freeze panes in Excel.  

You can answer this as your sample answer: “To freeze panes, I will choose the row or column below or after the desired pane. Then I will go to the ‘view’ tab and press ‘freeze panes’.” 

6) How do you stop someone from copying a cell in Excel? 

This question assesses your knowledge of how to prevent a cell from being copied in Microsoft Excel.  

This question can be answered like this, "To limit copying, I will select “review,” then “protect the sheet,” and set a password. This will be helpful because whenever someone tries to copy a cell, it will be deselected to ‘Select locked cells". 

7) What is the easiest way to sort or filter data in Excel? 

This question assesses your ability to sort or filter data in Excel.  

You can use this as your sample answer: "To filter data, I will choose the header row and click on the “data” tab; then, click on the “filter” option. Then, the drop-down arrows will appear, which will help me filter data based on choice." 

8) What is the difference between a formula and a function in Excel? 

A formula is an expression the user creates to perform calculations, while a function is a predefined operation in Excel (e.g., SUM, AVERAGE) used within formulas.  

While both formulas and functions serve to automate calculations in Excel, functions offer a more streamlined approach by providing ready-made solutions for common tasks. 

9) Can you tell me what is VLOOKUP in Excel? 

This question will assist the interviewer in understanding if you are aware of how to apply VLOOKUP in Excel.  

You can use this as your sample answer: "VLOOKUP is a function that will look for a value in the first column and return a value in the same row from the specified column. I can use this to find specific data in large tables." 

10) What is the difference between VLOOKUP and LOOKUP functions? 

VLOOKUP looks up values vertically from the leftmost column of the range, and LOOKUP looks up values in the current row or column or an ordered range. 

11) How do you format data in Excel? 

This question will assist the interviewer in knowing whether you can format data in Excel.

You can use this as your sample answer: " If I want to format data, I will click on the cells and then click on the ‘Home' tab. There are options to change the font, number format, cell colour, borders, and alignment." 

12) Identify the difference between COUNT, COUNTA, and COUNTBLANK in Excel. 

COUNT is used to count numeric cells; COUNTA is used to count cells that are not empty, and COUNTBLANK is used to count empty cells within a range. 

13) Do you know how to create a hyperlink in Excel? 

This question will help the interviewer determine if you can make a hyperlink in Excel.  

You can use this as your sample answer: "If I want to make a hyperlink in Excel, I will select the cell, right-click it, and then click “Hyperlink.” After this, I will type the link address. I can also choose a file and position it in the document.” 

14) What is a Pivot Table in Excel? 

A Pivot Table is a data analysis tool that dynamically sorts group data to create reports. 

15) How does the IF() function work in Excel? 

IF() examines whether a condition is satisfied and then returns one value if it is accurate and another if it is false. For example =IF(A1>10, “Yes”, “No”). 

Learn how you can use Excel for Accounting with our Excel for Accounting Course – join now! 

Intermediate Excel Interview Questions 

These intermediate Excel Interview Questions will help you further understand the probable nature of your upcoming interviews: 

16) How do SUM, SUMIF, and SUMIFS differ in Excel? 

SUM adds all numbers in a range, SUMIF adds numbers based on a single condition, and SUMIFS adds numbers based on multiple conditions. For example, SUM(A1:A10) adds all values in the range, SUMIF(A1:A10, ">5") adds values greater than 5, and SUMIFS(A1:A10, B1:B10, "A", C1:C10, ">5") adds values meeting both conditions. 

17) What are wildcards used for in Excel? 

Wildcards in Excel are symbols used to represent one or more characters. The asterisk (*) portrays any number of characters, and the question mark (?) represents a single character. They are helpful in functions like COUNTIF, SUMIF, and VLOOKUP for flexible matching criteria. 

18) What is Data Validation in Excel? Provide an example. 

Data Validation in Excel is one of the validation techniques that limits the amount of data allowed to be entered into a cell.  

You can use this as your sample answer: "For example, if I want to restrict input to numbers from 1 to 100, I will choose Cells, Data, and Data Validation. The Data Validation dialogue box will open in the Settings tab. After this, I must select the option Whole number and enter 1 and 100 in the field." 

19) What is the ribbon in Excel? 

Excel Ribbon is a graphical control element that groups all the key commands and features on a series of tabs at the top of Excel window12. It superseded the traditional toolbars and pull-down menus present in earlier versions of the software. 

20) How are nested IF statements utilised in Excel? 

The IF functions combined with the AND compare two or more conditions within the same Excel cell. For example, =IF(A1>90,”A”,IF(A1>80,”B”,”C”)) tests whether A1 exceeds 90 and returns “A” if this is the case; A1 is tested again for an element of 80 and an element of “B” is returned if this is the case; finally an element of “C” is returned if the previous instance is not the case. 

21) How do you secure a workbook in Excel? 

To secure a workbook, go to "File" > "Info" > "Protect Workbook." You can encrypt with a password, protect the current sheet, or restrict editing. This prevents unauthorised access and changes to the workbook's content. 

22) How does a Slicer function in Excel? 

A Slicer in Excel is a visual tool that filters data in PivotTables and PivotCharts. It allows users to quickly select and filter data by clicking on buttons, providing a more interactive and user-friendly way to filter datasets. 

23) What is the difference between Excel's SUBSTITUTE and REPLACE functions? 

SUBSTITUTE replaces occurrences of a specific text in a string, while REPLACE replaces text based on the position.  

For example, SUBSTITUTE("hello world", "world", "Excel") changes "hello world" to "hello Excel", while REPLACE("hello", 1, 2, "H") changes "hello" to "Hello". 

24) How do you create a Dynamic Range for the Data Source of a Pivot Table in Excel? 

This question helps the interviewer understand if you can create a Dynamic Range for the Data Source of a Pivot Table in Excel. 

You can use this as your sample answer: "If I want to create a dynamic range, I will use a table or a named range with the OFFSET function. Then, I'll convert the data to a table (Ctrl+T) or define a named range using OFFSET and COUNTA to adjust the range as data changes. After that, I will use this named range as the Pivot Table data source. 

25) What are macros in Excel? Demonstrate how to create a macro to automate a task. 

Macros in Excel are recorded sequences of actions to automate repetitive tasks 

You can use this sample answer: "If I want to create a macro, I will go to "View" > "Macros" > "Record Macro," perform the actions I want to automate, and then stop recording. The macro can be run later to repeat those actions automatically." 

Enhance your skills on worksheets and data with our Excel Training with Gantt Charts – register now!  

Advanced-level Excel Interview Questions 

These advanced-level Excel Interview Questions will help you answer even the most complex of questions in your interview: 

26) Why is INDEX-MATCH better than VLOOKUP? 

INDEX-MATCH is more robust and dynamic than VLOOKUP. It supports horizontal and vertical lookups, supports left-to-right and right-to-left searches, and does not need the lookup value in the first column. It also eliminates the constraints of VLOOKUP’s static column reference number. 

27) How do you make a drop-down list with data validation in Excel? 

This question aims to determine whether you understand how to create a drop-down list using data validation in Excel.  

You can use this as your sample answer: "I can choose the cells to create a Drop-down List. Next, I will select “Data”>“Data Validation,” then select “List” from the “Allow” drop-down and input the range of cells which contain the list items. Finally, I will click “OK” to apply the drop-down list." 

28) Can multiple tables be used to build PivotTables? 

Yes, multiple tables can be used to build PivotTables by creating relationships between them. This can be done in Excel's Data Model, where you can establish relationships between tables and use them to make more complex Pivot Tables. 

29) How do you pass arguments to a VBA function? 

This question aims to understand how to pass arguments to a VBA function. 

You can use this as your sample answer: "I can pass arguments to a VBA function by listing them inside the parentheses in the function declaration and then providing values when calling the function”. Here’s an example-  

30) How do you find the last row and column in VBA? 

This question will assist the interviewer in understanding if you can find the last row and column in VBA. 

You can use this as your sample answer: "To find the last row, I will use 'Cells(Rows.Count, "A").End(xlUp).Row.' I will use Cells(1, Columns.Count) to find the last column.End(xlToLeft).Column. These lines will determine the last used row and column in the specified range." 

31) How can you generate an Excel (.XLS and . XLSX) file in C# without installing Microsoft Office? 

This question will help the interviewer understand if you have advanced knowledge of generating an Excel file in C#. 

You can use this as your sample answer: "I can create Excel files in C# without MS Office, by using libraries like EPPlus, ClosedXML, or NPOI. These libraries will allow me to programmatically generate, modify, and save Excel files without requiring Office installed on the machine." 

32) Can you unlock a password-protected Excel VBA project? 

This question will allow users to understand whether they want to unlock a password-protected Excel VBA project. It also aims to understand their ethical views regarding company policies. 

You can use this as your sample answer: "While it is technically possible to unlock a password-protected VBA project using specific tools or techniques, I would not generally recommend it as it may violate software policies or copyrights. Using the official recovery methods is best if you have legal access. 

33) Can you write a VBA code snippet to create a bar chart with given data? 

This question will allow the interviewer to understand if you can write VBA code from scratch to create a bar chart from any given data. 

You can use this sample answer: “Yes, I can craft a VBA code snippet to generate a bar chart by using the provided data.” Here’s an example-  

34) Do you know how to disable automatic sorting in Pivot Tables? 

This question lets the interviewer understand if you can automatically disable sorting in Pivot Tables. 

You can use this as your sample answer: "To disable automatic sorting in PivotTables, I will right-click a field in the PivotTable, choose "Sort," then select "More Sort Options." After that, I will also uncheck the "Sort automatically every time the report is updated" option." 

35) Summarise briefly the What-If Analysis in Excel. 

The What-If Analysis in Excel helps you analyse different outcomes and their effects on the data. It features Scenario Manager, Goal Seek and Data Tables that assist in analysing expected results. 

36) Explain the differences between a function and a subroutine in VBA. 

A function in VBA returns a value that can be used in formulas, while a subroutine (Sub) does not return a value and performs actions.  

Subs are called directly, whereas functions are called by name and argument. 

37) How would you debug VBA code? 

This question aims to understand if you have the advanced skills to debug VBA code. 

You can use this as your sample answer: "I can debug VBA code using the Debug toolbar and tools like breakpoints, the Immediate Window, Step Into (F8), Step Over (Shift+F8), and Step Out (Ctrl+Shift+F8). These tools help examine the code execution flow and identify errors." 

38) What is Goal Seek in Excel? 

Goal Seek is a What-If Analysis tool that allows users to find the input value needed to achieve a specific goal. It can also determine the required sales figure to reach a target profit by adjusting the input cell until the desired result is found. 

39) Can you write a VBA function to calculate the area of a rectangle? 

This question gives your interviewer an idea of whether you have the technical skills to calculate the area of a triangle using a VBA function. 

You can use this as your sample answer: "Yes, I can write a VBA function to calculate the area of a rectangle.” Here’s an example-  

40) Provide an example of using an approximate match in Excel. 

This question will help the interviewer understand if you can provide an approximate match in Excel. 

Use this as your sample answer: "An approximate match can be used with the VLOOKUP function by setting the range_lookup parameter to TRUE. For example, =VLOOKUP(85, A1:B10, 2, TRUE) finds the largest value less than or equal to 85 in the first column and returns the corresponding value from the second column." 

41) How can you extract the domain name from an email address in Excel? 

This question will help the interviewer understand if you can extract the domain name from an email address in Excel. This shows the depth of your technical knowledge in Excel. 

You can use this as your sample answer: "Yes, I can extract the domain name from an email address in Excel. I will use the formula to extract the domain name: =MID(A1, FIND("@", A1) + 1, LEN(A1) - FIND("@", A1)). This formula finds the "@" symbol and extracts the following text." 

Learn how you can collaborate easily and share data with Excel – sign up now for our Microsoft Excel Course! 

Conclusion 

From this blog, you can understand how to answer Excel Interview Questions effortlessly. It can also help the interviewer understand your technical skills and knowledge level. Essentially, you can stand out from other candidates by reading the sample answers to these questions. 

Supercharge your skills with our Microsoft Excel VBA and Macro Training - sign up today. 

Frequently Asked Questions

Upcoming office applications resources batches & dates.

Fri 27th Sep 2024

Fri 25th Oct 2024

Fri 22nd Nov 2024

Get A Quote

WHO WILL BE FUNDING THE COURSE?

My employer

By submitting your details you agree to be contacted in order to respond to your enquiry

• Business Analysis
• Lean Six Sigma Certification

Share this course

Our biggest summer sale.

We cannot process your enquiry without contacting you, please tick to confirm your consent to us for contacting you about your enquiry.

By submitting your details you agree to be contacted in order to respond to your enquiry.

We may not have the course you’re looking for. If you enquire or give us a call on 01344203999 and speak to our training experts, we may still be able to help with your training requirements.

Or select from our popular topics

• ITIL® Certification
• Scrum Certification
• ISO 9001 Certification
• Change Management Certification
• Microsoft Azure Certification
• Microsoft Excel Courses
• Explore more courses

Press esc to close

Fill out your  contact details  below and our training experts will be in touch.

Fill out your   contact details   below

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

Back to Course Information

Fill out your contact details below so we can get in touch with you regarding your training requirements.

* WHO WILL BE FUNDING THE COURSE?

Preferred Contact Method

No preference

Back to course information

Fill out your  training details  below

Fill out your training details below so we have a better idea of what your training requirements are.

HOW MANY DELEGATES NEED TRAINING?

HOW DO YOU WANT THE COURSE DELIVERED?

Online Instructor-led

Online Self-paced

WHEN WOULD YOU LIKE TO TAKE THIS COURSE?

Next 2 - 4 months

WHAT IS YOUR REASON FOR ENQUIRING?

Looking for some information

Looking for a discount

I want to book but have questions

One of our training experts will be in touch shortly to go overy your training requirements.

Your privacy & cookies!

Like many websites we use cookies. We care about your data and experience, so to give you the best possible experience using our site, we store a very limited amount of your data. Continuing to use this site or clicking “Accept & close” means that you agree to our use of cookies. Learn more about our privacy policy and cookie policy cookie policy .

We use cookies that are essential for our site to work. Please visit our cookie policy for more information. To accept all cookies click 'Accept & close'.

{{ activeMenu.name }}

• Python Courses
• JavaScript Courses
• Artificial Intelligence Courses
• Data Science Courses
• React Courses
• Ethical Hacking Courses
• View All Courses

Fresh Articles

• Python Projects
• JavaScript Projects
• Java Projects
• HTML Projects
• C++ Projects
• PHP Projects
• View All Projects

• Python Certifications
• JavaScript Certifications
• Linux Certifications
• Data Science Certifications
• Data Analytics Certifications
• Cybersecurity Certifications
• View All Certifications

• IDEs & Editors
• Web Development
• Frameworks & Libraries
• View All Programming
• View All Development

• App Development
• Game Development
• Courses, Books, & Certifications
• Data Science
• Data Analytics
• Artificial Intelligence (AI)
• Machine Learning (ML)
• View All Data, Analysis, & AI

• Networking & Security
• Cloud, DevOps, & Systems
• Recommendations
• Crypto, Web3, & Blockchain
• User-Submitted Tutorials
• View All Blog Content

• Python Online Compiler
• JavaScript Online Compiler
• HTML & CSS Online Compiler
• Certifications
• Programming
• Development
• Data, Analysis, & AI
• Online Python Compiler
• Online JavaScript Compiler
• Online HTML Compiler

Don't have an account? Sign up

Forgot your password?

Already have an account? Login

Have you read our submission guidelines?

Go back to Sign In

• Career Development

42 Excel Interview Questions and Answers [2024]

If you’re interested in a career in data analysis or business intelligence (BI) in 2024, you will need strong Excel skills. And if you have any upcoming interviews, not only should you be prepared to answer programming interview questions , but you’ll also need to tackle Excel related interview questions, as these will almost certainly come up.

MS Excel continues to be a diverse and essential program for working with data, meaning there are lots of potential Excel interview questions for data analysts and BI.

To help you prepare, we’ve compiled a comprehensive list of 42 common MS Excel questions and answers, with options for beginners, intermediate users, and advanced Excel wizards.

• What to Expect With Excel Interview Questions

Microsoft Excel is an exceptionally common and important skill in various roles and industries. Chances are, if you’ve ever dealt with any data, you’ve used Excel.

And while most people know a little Excel, distinguishing yourself as a power user can give you the edge over the competition when applying for data analytics and business intelligence roles.

When it comes to Excel questions for interviews, your interviewer will have a large list of Excel questions to determine your skill level, and on occasion, they may forego verbal Microsoft Excel interview questions in favor of technical interview questions.

If you do encounter Excel technical interview questions, you’ll be expected to solve problems in real-time with an Excel simulation. But just remember to take your time and think through the solution. 

• Beginner-Level Excel Interview Questions

These basic Excel interview questions cover the fundamentals, so anyone that’s worked with Microsoft Excel should be able to answer these fundamental questions on MS Excel. Let’s dive into Excel basic interview questions.

1. What Is a Cell?

A cell is the basic unit of a worksheet, in which you enter data. Each cell has an address, denoted by a column letter and row number. For example, the address of the cell in the image above is B5.

2. What Is the Difference Between a Range and a Cell?

A range is a group of cells adjacent to each other. A cell is part of a range if it is adjacent to at least one other cell in the range. You can select a range of cells either moving vertically (across a column) or moving horizontally (across a row).

3. What Is the Difference Between a Column and a Row?

A column is a vertical grouping of cells in a worksheet. A row is a horizontal grouping of cells in a worksheet.

4. How Do You Enter and Format Text in a Cell?

To enter text into a cell, simply type it directly in the cell. You can also use the keyboard shortcut Ctrl+Enter to enter text into a cell. To format the cell, you can use the ribbon bar.

5. What Can a Cell Contain?

A cell can contain any type of text, number, date, or formula that calculates a solution. A cell can even contain a URL. A cell cannot contain images. While you can paste images into Excel, they won’t be held in a specific cell.

6. How Do You Format Numbers in a Cell?

To format numbers in a cell, select the cell number(s) and then use the formatting options available on the ribbon. You can change the format, decimal places, and thousands separators. For example, some countries stylize $1,000 as “$1,000,” while others use “$1.000.”

7. What Is an Excel Formula?

A formula is an equation that calculates a result based on the values of other cells in a worksheet. Formulas always start with an equals sign (=).

8. How Do You Enter a Formula Into a Cell?

To enter a formula into a cell, type it in the cell as a value. Every formula will start with the equals sign (=). You can also use the keyboard shortcut Shift+Enter to enter a formula into a cell.

9. How Do You Edit a Formula?

To edit a formula, double-click on the cell that contains the formula. This opens up the Formula Editor, where you can change the formula. As you edit a formula, suggestions will pop up; the auto-suggest can be very useful for remembering the parameters of a formula.

10. What Is a Function?

A function is a predefined equation that calculates a result based on given values. For example, the SUM() function calculates a given sum of values. You can use a function in a formula by typing it in along with its parameters.

11. How Do You Use a Function in a Formula?

To use a function in a formula, simply type the function name into the Formula Editor and then provide the required values. For example, the SUM() function requires you to specify a range of cells to add up.

12. How Do You Change the Page Formatting or Margins in a Worksheet?

To change the margins or page options in a worksheet, go to the Page Layout section of the ribbon. In older versions of Excel, you may need to use File > Page Setup to open the Page Setup dialog box, where you can adjust the margins for your worksheet.

13. What Is a Header or a Footer?

Like Microsoft Word , a header and footer in Excel are displayed at the top and bottom of each page in a worksheet. You can customize these headers and footers to include the date, time, sheet name, and more.

14. What Is a Worksheet?

A worksheet is a document that contains data and information. A worksheet is divided into rows and columns to organize and group data. Each Microsoft Excel worksheet is one sheet in a total workbook.

• Intermediate Level MS Excel Interview Questions and Answers

Intermediate questions delve a little deeper than basic questions of Excel, so if you use Microsoft Excel for many of your job functions, you should be able to answer intermediate questions on Microsoft Excel. These slightly harder interview questions for Excel will cover topics you should know if you’ve had one to two years of Excel experience.

15. What Are the Major Competitors of Excel?

Google Sheets , Zoho Sheet, and LibreOffice are common spreadsheet applications and alternatives to Excel. For analytics purposes, MATLAB and PowerBI are ideal, and you can even create an SQL database like MySQL to crunch larger volumes of more complex data.

16. What Are the Benefits of Microsoft Excel?

Microsoft Excel is easy to use, portable, and universal, meaning you can import and export it into many utilities. Most people have some familiarity with Microsoft Excel, making it easily shareable, and Microsoft Excel is also available on desktops and online.

17. What Are the Downsides of Microsoft Excel?

Microsoft Excel is a proprietary, paid solution, so not everyone uses it. Some prefer free solutions like Google Sheets. Microsoft Excel is primarily used as a spreadsheet to collect data and perform minor analysis functions, so it might not be ideal for complex database tasks.

18. What Are Some of the Most Popular Microsoft Excel Functions?

SUM() is likely the most popular Microsoft Excel function, as it adds up given values. Another common Microsoft Excel function is AVERAGE() , which will return the mean value for a range of numbers. More advanced users often use IF() functions and LOOKUP() functions.

19. How Do You Sort Data in Excel?

Once data has been entered into Excel, it can be automatically sorted and filtered. Go to Data in the ribbon, and use either Sort ascending , Sort descending, or Custom sort shortcut . You could also use a FILTER function or the SORT function.

20. What Is the Order of Operations in Excel?

Excel follows PEMDAS: parentheses, exponents, multiplication, division, addition, and subtraction. If you type in “=1 + 2/4” the answer will be 1.5 rather than 0.75.

21. What is VLOOKUP?

The Excel VLOOKUP() function is designed to find data within exceptionally large spreadsheets. It can be used to find data that is identical to or similar to a given string and locate associated data. This means VLOOKUP() is a bit like a database function, even though Excel is not a database

As an aside, Excel now has the XLOOKUP , which can search both vertically and horizontally.

22. What Charts Are Available in Excel?

Excel has many types of charts, including column charts, pie charts, line charts, area charts, scatter charts, bubble charts, surface charts, and donut charts. Of these, column charts, pie charts, and line charts are the most popular.

23. What Are Comments, and How Do You Use Them?

On a cell, you can right-click and add a comment. Comments are useful for conveying information to others viewing an Excel sheet without directly editing the data.

24. Why Would You “Freeze Panes” in Microsoft Excel?

Freezing a pane means that a section of the sheet will never move, even if you’re scrolling through other data. It is frequently used to create a static element (such as an index) on a page.

25. What Does It Mean to “Hide” a Sheet?

When you “hide” a sheet, it’s no longer visible in your sheet navigation. The sheet is still there and can still be revealed and manipulated unless you take further action to protect the workbook. Hiding a sheet can be useful for “archiving” old information, although the information will still be present in the file.

26. How Do You Protect a Workbook in Excel?

You can protect workbooks with a password. The “Protect Workbook” tool provides numerous options: read-only, password encryption, sheet and workbook protection, and digital signatures. You get there by heading to File > Info > Protect Workbook .

27. What Does “Merge” Do in Excel?

Merge combines multiple cells into a single cell, which is useful for formatting. You can combine all the cells in a row, for instance, to create a title.

• Advanced Excel Interview Questions and Answers for Excel Experts

Advanced Excel interview questions are much harder than intermediate and basic questions on Excel, as they involve complex features like Questions on MS SQL.

Most Microsoft Excel users won’t need to know these advanced interview questions, as these questions are for power users and people who use Excel extensively for operations pushing the boundaries of Excel’s capabilities.

28. What Is a Pivot Table in Excel, and How Would You Use One?

A pivot table performs a deeper data analysis by creating a view to reorganize, average, count, or otherwise analyze data held within a table.

29. What Is a Macro in Excel, and How Would You Use One?

A macro is a hard-coded sequence of events, such as opening a spreadsheet and entering specific text into the header. Macros can be used to save time and can be very powerful when in the hands of the right programmer.

30. What Is a What-if Analysis in Excel?

Excel’s what-if analysis uses your spreadsheet data to conduct data-driven simulations. Under the what-if analysis, you can select certain cells that will change to get different reporting metrics.

31. Where Can You Import Data From Into Excel?

Excel can import data directly from SQL Server Database, Microsoft Access Database, Analysis Services, and SQL Server Analysis Services. Excel can also bring in Google Sheets, CSV, and several other popular formats.

32. What Is a Surface Chart in Excel?

A surface chart is a unique 3D topological map that can be used with data that provides the correct coordinates.

33. What Is a Bubble Chart in Excel?

A bubble chart is a special type of scatter chart. While a scatter chart tracks two variables (X and Y) a bubble chart tracks a third variable, which is represented by the size of the scatter chart dot, creating a bubble . Put simply, a bubble chart has larger bubbles for larger numbers.

34. What Is a Donut Chart in Excel?

A donut chart uses concentric rings. Like most Excel charts, donut charts can provide a powerful visualization depending on the data that’s being presented. It is similar to a pie chart but does not have a center, hence the name.

35. What Is VBA Code and How Is It Used?

VBA code is a type of Visual Basic used to create Excel macros. For example, the following VBA would open a workbook :

Workbooks.open(“test.XLSX”)

VBA is a powerful scripting language and one of the most advanced uses of Microsoft Excel. You can edit VBA code directly through the Excel sheet or create standalone code snippets that the program will run.

36. What is XLM?

Today, Microsoft Excel sheets use VBA. But in the past, they used XLM for macros, a language that was designed specifically for Excel. If you’re working with older Excel sheets, you may be called upon to code in XLM.

37. How Can You Import Data From Another Workbook?

Microsoft Excel makes it easy to import data from another workbook. You can create an external reference between workbooks by typing the source workbook name and the sheet you are pulling data from, as shown in the command below.

Note that if the file is moved, the sheet is renamed, or the data is shifted, the data that is pulled will be incorrect.

38. What Is a Logical Test in Excel?

Excel can be used to perform basic logical tests with the IF() function. The IF() function produces a test scenario and an output based on the scenario. The following code would produce “Yes” if a number was greater than 0 but “No” if a number was equal to or less than 0.

39. What Is a Timeline Used for in Excel?

When crunching data, a timeline can separate data based on a given sequence of dates. A powerful form of analysis, a timeline shows how the information changes over time. You can interactively sort dates by year, quarter, month, or day.

40. How Can You Create a Pivot Table With Multiple Data Sources?

You can create a pivot table using multiple data sources with the Pivot Data Modeling function. Data can be pulled dynamically into the pivot table for a broader analysis.

41. How Can You Debug Code in VBA?

The most basic method of debugging code in VBA is to press the F8 button to go through code execution. Code execution shows you exactly where your code may be failing. You can also use breakpoints (F9).

42. What Are the Major Types of Errors You Can Encounter in Excel?

There are six major types of error: #N/A, #DIV/0, #VALUE, #REF, #NAME, and #NUM . Each of these refers to an element that is either missing or incorrect, except for DIV/0 (which is a division by 0 error).

As an example, the #VALUE and #REF errors refer to a value or a reference that is either the wrong type (such as a text being used as a number) or simply unavailable.

• 7 Tips for an Excel Interview

Beyond the Excel basic questions, what do you need to know about Excel for an interview? This depends on the position, as the MS Excel interview questions for someone that’s pursuing data analysis will differ to interview questions related to Excel for a simple desk position.

Some positions list “Excel” as a skill when all they really need is for you to be able to make a worksheet. On the other hand, some positions will need you to dig deeper, use macros, and handle merging.

Either way, let’s look at 7 tips to feel prepared that extend beyond the Excel practical questions.

• Know the basics: Refresh yourself on the basic features of Excel. Create a workbook, enter data into a cell, and practice making a sheet. Another great way to refresh skills for interview questions on MS Excel is to look over Excel cheat sheets .
• Consider taking an Excel class: Taking an Excel class or tutorial might only take an hour or two, but it can teach you vital information for interviews. Microsoft Excel changes with every new release, so while you may know a lot about Excel, a course can help. Udemy has a fantastic Excel class to progress from beginner to advanced.
• Think about real-world situations: When interviewers ask you questions, think about a situation you might be in where you’d need to manipulate this data in real-life. This can help if you’re having trouble thinking abstractly.
• Remain calm when you don’t know an answer: Because Excel is such a common but complex program, everyone’s experience with Excel can be vastly different. You can learn as long as you’re relatively competent in the program.
• Practice simulations: Many interviews today include a technical exam requiring you to manipulate data in a simulated Excel window. Practicing these simulations in advance can help.
• Use your experience as examples: When answering interview questions on Excel, try to use examples from your real-life experience. This helps you come across as experienced and adds personality to your answers.
• Don’t lie: If you’re asked a question, and you don’t know the answer, try to respond to the best of your ability, but don’t lie. Make sure to stress that you are aware of your limits and are willing to work on them.

Overall, don’t be afraid to clarify or say that you don’t know something. Excel skills are very trainable, and your interviewer is likely trying to ascertain your skill level rather than discount your experience. Do the prep work, and you’ll be ready for basic questions on MS Excel. 

And there you have it, 42 of the most common Excel interview questions and answers you need to know in 2024 if you’re planning to attend an interview for a job in data analytics or business intelligence.

As long as MS Excel continues to be an essential program for working with data, there will be a need to remain ready to tackle potential Excel interview questions for data analysts and BI. But with our comprehensive list of MS Excel questions and answers, you’ll be ready to ace any interview question, whether you’re a beginner, intermediate user, or power user. 

• Frequently Asked Questions

1. How Do You Test Excel Skills in an Interview?

Sometimes interviewers will ask basic, intermediate, or advanced questions. Other times, interviewers will ask you to make a spreadsheet and perform certain functions.

2. What Should You Expect in an Excel Interview?

If the position is Excel-centered, then you may have a technical interview (simulations) and various questions to determine your experience level with Excel. If the position merely includes Excel, you may face three to four questions about Excel and how you’ve used it in the past. If the position is solely based on Excel, you may need to prepare for a more robust interview, including an exam.

3. What Should I Say in an Excel Interview?

Talk about a time when you used Excel to solve a specific problem. Discuss the features you used, what worked, what didn’t work, and how you learned from it.

People are also reading:

• Best SQL Server Certifications
• Best SQL Certifications
• SQL Cheat Sheet
• Difference between NoSQL and SQL
• What is MongoDB?
• DBMS Interview Questions
• Differences between MongoDB and MySQL
• MongoDB Interview Questions
• MariaDB vs MySQL
• PL-SQL Interview Questions and Answers

Jenna Inouye currently works at Google and has been a full-stack developer for two decades, specializing in web application design and development. She is a tech expert with a B.S. in Information & Computer Science and MCITP certification. For the last eight years, she has worked as a news and feature writer focusing on technology and finance, with bylines in Udemy, SVG, The Gamer, Productivity Spot, and Spreadsheet Point.

Subscribe to our Newsletter for Articles, News, & Jobs.

Disclosure: Hackr.io is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.

In this article

• Top 48 Networking Interview Questions and Answers in 2024 Computer Networks Career Development Interview Questions
• Top 20 REST API Interview Questions & Answers [2024] Web Development Career Development Interview Questions
• Top 45 QA Interview Questions and Answers in 2024 Software Engineering Software Testing Career Development Interview Questions

Please login to leave comments

Always be in the loop.

Get news once a week, and don't worry — no spam.

{{ errors }}

{{ message }}

• Help center
• We ❤️ Feedback
• Advertise / Partner
• Write for us
• Privacy Policy
• Cookie Policy
• Change Privacy Settings
• Disclosure Policy
• Terms and Conditions
• Refund Policy

Disclosure: This page may contain affliate links, meaning when you click the links and make a purchase, we receive a commission.

50 Excel Interview Questions and Answers for 2024

Excel is one software that all companies expect you to know and going unprepared for it would be disastrous. Do not worry because here an article dedicated to preparing you for your interviews with the most frequently asked Excel Interview Questions and Answers.

The questions here will be divided into three sections as mentioned below:

• MS Excel General Questions
• Excel Interview Questions for Pivot Tables and Pivot Charts
• Excel Interview Questions on Formulas and Functions

Ms Excel General Questions:

Q1) explain ms excel in brief..

Microsoft Excel is a spreadsheet or a computer application that allows the storage of data in the form of a table. Excel was developed by Microsoft and can be used on various operating systems such as Windows, macOS, IOS and Android.

Some of the important features of MS Excel are:

• Availability of Graphing tools
• Built-in functions such as SUM, DATE, COUNTIF, etc
• Allows data analysis through tables, charts, filters, etc
• The availability of Visual Basic for Application (VBA)
• Flexible workbook and worksheet operations
• Allows easy data validation

Q2) What do you mean by cells in an Excel sheet?

The area which falls at the intersection of a column and a row where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.

Q3) Explain what is a spreadsheet?

Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given them. Take a look at the image below:

Q4) What do you mean by cell address?

The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.

Q5) Can you add cells?

Yes, you can insert new cells into a sheet. To add a new cell, simply select the cell where you want to insert it and then select the Insert option. you will see the following window:

Select the desired option and then click on OK.

Q6 ) Differentiate between the terms “absolute cell referencing” and “relative cell referencing” in Microsoft Excel.

In Microsoft Excel, “absolute cell referencing” and “relative cell referencing” are two methods for specifying cell references in formulas. They determine how Excel treats cell references when you copy or fill a formula to other cells. Here’s the differentiation between the two:

• Absolute Cell Referencing:

In absolute cell referencing, the cell reference remains constant or fixed when you copy or fill the formula to other cells.

To create an absolute cell reference, you use a dollar sign ($) before both the column letter and the row number, like this: “$A$1”.

When you copy a formula with absolute references, the references do not change. For example, if you copy a formula containing “$A$1” from cell B1 to cell B2, it will still reference cell “$A$1.”

Formula in Cell B1: “=$A$1”

Formula in Cell B2 (after copying): “=$A$1”

• Relative Cell Referencing:

In relative cell referencing, the cell reference is adjusted relative to the position of the formula when you copy or fill it to other cells.

By default, Excel uses relative referencing. To create a relative reference, no additional special symbols are required.

When you copy a formula with relative references, Excel automatically changes the references based on the formula’s relative position. For example, if you copy a formula with “A1” from cell B1 to cell B2, it will become “A2.”

Formula in Cell B1: “=A1”

Formula in Cell B2 (after copying): “=A2”

In summary, absolute cell referencing uses “$” symbols to keep cell references fixed, regardless of where you copy the formula, while relative cell referencing does not use any symbols and adjusts the references relative to the formula’s position when copied. The choice between absolute and relative referencing depends on your specific needs for formula behavior in Excel.

Q6) Can you format MS Excel cells? If yes, then how?

Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands present in the Font group of the Home tab. When you open the Font window, you will see the following options:

Q7) Can you add comments to a cell?

Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New Comment option. These comments will be visible to all those people who have access to the Excel sheet.

Q9) How can one restrict copying a cell from a worksheet?

Follow the instructions below to prevent someone from copying a cell from our worksheet: 

• Choose the data that you want to protect first.
• Input Ctrl, Shift, and F. After that, the Format Cells tab appears. Choosing the Protection tab. After selecting Locked, click OK.
• Select Protect Sheet from the Review menu after that. Enter the password to protect the sheet.

Q8) Can you add new rows and columns to an Excel sheet?

Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.

Q9) What is Ribbon and where does it appear?

The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users to access many of the most important commands directly. It consists of many tabs such as File, Home, View, Insert, etc. You can also customize the ribbon to suit your preferences. To customize the Ribbon, right-click on it and select the “Customize the Ribbon” option. You will see the following window:

You can select or unselect any option of your choice from here.

Q10) How do you freeze panes in Excel?

MS Excel allows you to freeze panes that will help you see the headings of the rows and the columns even if scroll down a long way on the sheet. To Freeze Panes in Excel, follow the given steps:

• First, select the Rows and Columns you wish to freeze
• Then, select Freeze Pane present in the View tab
• Here, you will see the following three options to selectively freeze the rows and columns as shown in the image below:

Q11) How do you add a Note to a cell?

To add a Note, select the cell and right-click on the same. then select the New Note option and type in any note that you wish to. In case you want to delete the Note, follow the same procedure and select the Delete Note option. Notes are indicated by a red triangle at the top-right corner of the cell.

Q12) Can you protect workbooks in Excel?

Yes, workbooks can be protected. Excel provides three options for this:

• Passwords can be set to open Workbooks
• You can protect sheets from being added, deleted, hidden or unhidden
• Protecting window sizes or positions from being changed

Q13) How do you apply a single format to all the sheets present in a workbook?

To apply the same format to all the sheets of a workbook, follow the given steps:

• Right-click on any sheet present in that workbook
• Then, click on the Select All Sheets option
• Format any of the sheets and you will see that the format has been applied to all the other sheets as well

Q14) What do you understand by Relative Cell Addresses?

Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.

Take a look at the image below where I have written the formula in C9 and copying the same formula to C10. As you can see, C10 shows the sum of A10 and B10, unlike A9 and B9.

Q15) In case you don’t want to modify the cell addresses when they are copied, what should you do?

If you do not want Excel to change the addresses when you copy formulas, you must make use of Absolute Cell Addresses. When you use Absolute Cell References, the row and the column addresses do not get modified and remain the same.

For absolute referencing, you will need to use the $ sign before column and row number. Take a look at the example shown in the given image:

Q16) What will you do if you want to change either the column letter or the row number but not both?

To do this, you must make use of Mixed Cell Addresses where either the row or column is relative while the other is absolute.

Take a look at the image below where the columns hold relative referencing while the rows are absolute. Therefore, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original formula and hence the result.

Q17) Can you protect cells of a sheet from being copied?

Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given steps:

• Select the cells that you want to protect
• Open up the Font window from the Home tab
• From the Protection pane, select Protection and then check the Hidden box
• Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required cells unless you do this)
• Specify a password (This will help you in unprotecting the sheet later)

Q18) How do you create Named Ranges?

To create named ranges, follow the given steps:

• Select the area to which you intend to give a name
• From Ribbon, select Formulas
• Click on Define Name from Defined Names group
• Give any name of your choice

Q19) What are macros?

Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. For example, if you have to record the sales of each item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc and use the same for the future instead of manually calculating it every day.

Q20) How do you create dropdown lists in Excel?

To create dropdown lists, follow the given steps:

• Click on Data tab present in the ribbon
• Then, from the Data Tools group, click on Data Validation
• Navigate to Settings>Allow>List
• Select the source list array

Excel Interview Questions on Pivot Tables and Pivot charts:

Q21) explain pivot tables along with their features.

Pivot Tables are statistical tables that condense data of those tables that have extensive information. The summary can be based on any field such as sales, averages, sums, etc that the pivot table represents in a simple and intelligent manner.

Some of the features of Excel Pivot Tables are as follows:

• Allow the display of exact data you have to analyze
• Provide various angles to view the data
• Allow you to focus on important details
• Comparison of data is very handy
• Pivot tables can detect different patterns, relationships, data trends, etc
• They can create instant data
• Accurate reports
• Serve the base for Pivot charts

Q22) How do you create Pivot Tables?

In order to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the following points in mind while preparing the data:

• Arrange the data into   rows and columns
• The   first row   should contain unique heading   for each of the columns
• The c olumns   should have only one type of data
• Rows   must have data for a   single recording only
• No blank rows  
• Columns   should not be   completely blank
• The data for creating Pivot table should be separate from other data present in the sheet

For example, let’s create a Pivot chart for the table shown in the image below:

To create a Pivot table, select the table and click on the Insert tab. then select Pivot Table command  and you will see the following window:

Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty pivot table has been created. Also, PivotTables Fields pane will open that will help you configure the Pivot table. Take a look at the image below where I have created a Pivot Table:

Q23) What are Pivot charts in MS Excel?

MS Excel charts are data visualization tools that help you visualize data in various ways. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc. For example, take a look at the Pivot table in the image below:

Now, if you wish to create a pivot chart for this table, select any cell from the table and then from the Insert tab, choose the Pivot Chart option. You will see the following options:

Choose any chart of your preference and click on OK. You can also format these charts respectively.

Q24) Can you create Pivot tables using multiple tables?

Yes, you can create Pivot tables using more than one base table. To do this, follow the given steps:

• Select I will create the page fields option and click on Next
• Once that is done, click on Next

Q25) What happens when you check the Defer Layout Update option present in the PivotTable Fields window?

In case you check this option, you will not see dynamic changes while interchanging the table fields. By default, this option is off or unchecked.  All the changes will appear only after you click on the Update button when you check this box.

Q26) Can you create a pivot table using tables from different worksheets?

If both the sheets are from the same workbook, you can create a pivot table for tables from different sheets as well. To create a pivot table from two different sheets, follow the same steps as shown in Q24 and when you specify the tables, go to the respective sheet and select the tables you intend to merge.

Q27) Is it possible to see the details of the results displayed in a pivot table?

Yes, it is possible to see the details of the results shown by the pivot tables in Excel. In order to see the details for any result, double-click on the value and you will see that a new sheet has been created with a new table having details about the factors that have led to that particular result. For example, if I double-click on one of the city values for Brad in the pivot table shown in Q15, I will see the following table that displays the details regarding the same:

Q28) How are PivotTables used to filter data?

Excel PivotTables allow you to filter data according to your requirements. To do this, place the field based on which you wish to filter out the data. Then from the pivot table, open the dropdown list present for the field you have placed in the Filter area and select the section of your choice. For example, in the table shown in Q22, if you wish to filter the data for different cities, you can do it easily as shown below:

As you can see, I have filtered the data for Chicago.

Q29) How do you change the value field to show some other result other than the Sum?

In order to change the value field to show results other than the Sum, right-click on the Sum of Amount values and then click on Value Field Settings . Here is the dialog box that you will see:

From here, you can select any value of your choice and then click on OK.

Q30) How to stop automatic sorting in PivotTables?

Excel automatically sorts the data present in the Pivot Tables. In case you do not want Excel to do this, open the dropdown menu fro the Row Labels or the Column Labels, and then click on More Sort Options. You will see the Sort dialog box opening. Click on More Options and unselect the Sort automatically option.

Excel Interview Question on Formulas and Functions:

Q31) What do you understand by Excel functions?

 Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate results of various formulas thereby helping in time conservation. Also, these functions make it very easy to execute formulas which would have been difficult to manually write down.

Q32) What are the various categories of functions available in Excel?

Functions in Excel are categorized as follows:

Q33) What is the operator precedence of formulas in Excel?

Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are executed first (if they are present) followed by multiplication, division, etc. An example of the same is shown in the image below:

As you can see, the output is 27 i.e obtained by first adding 4+5 and then multiplying it by 3. In case you do not specify the brackets, you will get the result by first multiplying 3×4 and then adding 5 to it i.e 12+5 resulting in 17.

Q34) Explain SUM and SUMIF functions.

SUM: The SUM function is used to calculate the sum of all the values that are specified as a parameter to it. The syntax of this function is as follows:

SUM(number1, number2, …)

As you can see in the image, the SUM function is calculating the total price for all the vegetables.

SUMIF: This function is used to calculate the sum of values that comply with a given condition.

SUMIF(range, criteria, [sum_range])

• range specifies the range of cells to be evaluated
• criteria provides the condition to be met
• sum_range is optional and provides the actual cells to be summed up

As you can see, the SUMIF function is calculating the sum of goals scored only by Dybala.

Q35) What are the different types of COUNT functions available in Excel?

Excel provides five types of COUNT functions i.e COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.

The COUNT returns the total number of cells that have numbers in the range that is specified to it as a parameter.

COUNT(value1, value2, …)

In the above image, you can see that the COUNT function is used to calculate the number of cells having numerical data in them.

COUNTA: Counts the number of cells in a given range that are not empty.

COUNT(value1, [value2], …)

The above image shows the functionality of the COUNTA function that returns the number of cells that are not between A4 and B10.

COUNTIF: This function counts the number of cells that comply to a given condition.

COUNTIF(range, criteria)

Take a look at the image below, where the COUNTIF function is used to calculate the number of cells that have the name, Dybala.

COUNTBLANK: Counts all the blank cells in a given range.

COUNTBLANK(range)

COUNTIFS: This is a special function that allows you to specify a set of conditions in order to count them.

COUNTIFS(criteria_range1,range1,[criteria_range2, criteria2], …)

Q36) How do you calculate the percentage in Excel?

Percentages, as we all know, are ratios that are calculated as a fraction of 100. Mathematically, the percentage can be defined as follows:

Percentage = (Part/ Whole) x 100

In Excel, the percentage can be calculated in a similar manner. Take a look at the image below where the percentage has been calculated for the values present in A1 and A2.

Here are the steps followed in order to obtain the result:

• Select the cell destination cell to display the percentage
• Then, type a “=” sign
• Type in A1/ A2 then hit the Enter key
• Click on Home tab, select % symbol from the numbers group

Q37) Explain how to calculate compound interest in Excel?

To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an investment based on the periodic, constant interest rate and payments.

FV(rate, nper, pmt, pv, type)

To find the rate, the number of periods are used to divide the annual rate (annual rate/ periods). nper is obtained by multiplying the no. of years (term) with the periods (term * periods). Periodic payment (pmt) can be any value (including zero).

The investment amount is $500, rate is 10% for 5 years. There are no periodic payments hence the value for pmt is 0. -B1 means that $500 has been taken from you. Therefore, the FV for this is $822.65.

Q38) How do you find averages in MS Excel?

Average can be calculated using the AVERAGE function.

AVERAGE(number1, number2, …)

To calculate the average marks scored by Dave and Ava, I have used the AVERAGE function.

Q39) What is VLOOKUP in Excel?

VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP stands for Vertical and to use this function, data should be arranged vertically. VLOOKUP is very useful when you have to find some piece of data from a huge amount of data. 

Q40) How does the VLOOKUP function work?

The VLOOKUP function, in Excel, a lookup value and begins to look for the same in the leftmost column. When it finds the first occurrence of the given lookup value, VLOOKUP starts to move right i.e in the row where the value was found. It goes on until the column number specified by the user and returns the desired value. This function is used to match exact and approximate lookup values. However, the default match is an approximate match.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value gives the value to be looked out for
• table_index  is the range from where the data is to be taken
• col_index_num specifies the column from which you want to fetch the value
• range_lookup  is a logical value i.e TRUE or FALSE ( TRUE will find the closest match;   FALSE  checks for exact match)

Q41) Explain the exact match with an example.

For an exact match, set the   range_lookup   value as FALSE.

In case you want to look for the designation of an employee, follow the given steps:

• Select the destination cell and type “=”
• Use VLOOKUP
• Specify the   lookup_value  (Here, it is the ID) along with the other parameters
• Set range_lookup value to FALSE
• The function will be: =VLOOKUP(104, A1: D8, 3, FALSE)

As you can see, VLOOKUP has returned the designation of the employee having 104 as his ID.

Q42) Explain the approximate match with an example.

For an approximate match, VLOOKUP will fetch values when there are no exact matches of the given loopup_value. For an approximate match, set the range_lookup  value to TRUE . Remember that the table must be sorted in ascending order for VLOOKUP to do an approximate match. So here, VLOOKUP basically starts to look for an approximate match of the given lookup value and the, stops at a value that is next largest of the given lookup value. It then moves into that row to return the value from the column that has been specified.

The following image shows an example of an approximate match by VLOOKUP:

• Follow the same steps specified for exact match
• For the range_lookup value, use TRUE
• The function will be: =VLOOKUP(55, A12: C15, 3, TRUE)

The lookup value is 55 and the next largest of the lookup value present in the first column is 40. Hence, the output is ‘Second Class’.

Q43) Can you use VLOOKUP for multiple tables?

Yes, you can use VLOOKUP for multiple tables as well.  In case you have two lookup tables, create named ranges for each table, and then use the IF function to select between each table based on some given condition. Click here to know more about this.

Q44) How do you perform a horizontal lookup in Excel?

To perform a horizontal lookup, you will have to make use of the HLOOKUP function.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• row_index_num specifies the row from which you want to fetch the value

Q45) How will you fetch the current date in Excel?

You can make use of the TODAY function. This function will return the current date in the MS Excel date format.

Q46) How does the AND function work?

The AND function in Excel is used to whether a given condition or a set of conditions is TRUE or not. In case all the conditions are satisfied, this function will return a boolean TRUE.

AND(logical1, [logical2], …)

logical1, logical2, … are conditions from 1-255 that you want to check

Q47) What is the What If Analysis?

What If Analysis is the technique of performing changes to one or more formulas present in the cells in order to see how it affects the result of those formulas in the worksheet. Excel provides three types of What If Analysis tools:

• Data Tables

Scenarios and Data Tables take a set of inputs to check for the potential results. Scenarios can work with many variables but input values can be at the max 32. Data tables, on the other hand, work with just one or two variables but can accept many distinct values for each of those variables.

Goal Seek, in contrast to Scenarios and Data Tables, takes the outputs and determines the possible inputs for the same.

Q48) Can you create shortcuts for most frequently used formulas?

Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick Access Toolbar and select the Customize Quick Access Toolbar option. You will see the following window:

From here, select Formulas and then choose any formula that you wish to create a shortcut for.

Q49) What is the difference between formulas and functions in Excel?

Formulas are that are defined by the user that is used to calculate some results. Formulas either be simple or complex and they can consist of values, functions, defined names, etc.

A function, on the other hand, is a built-in piece of code that is used to perform some particular action. Excel provides a huge number of built-in functions such as SUM, PRODUCT, IF, SUMIF, COUNT, etc.

Q50) How do you use wildcards with VLOOKUP?

Wildcards can be used when you are not sure of the exact lookup value. In order to use wildcards in Excel, you should make use of the “*” symbol.

For example, in the table that you see in the image below, if you enter “erg” and then use wildcards with it, VLOOKUP will fetch the output that corresponds to “Sergio”.

This brings us to the end of this article on Excel Interview Questions. I hope you are clear with all that has been shared with you.  Make sure you practice as much as possible and revert your experience.   

Got a question for us? Please mention it in the comments section of this “Excel Interview Questions” blog and we will get back to you as soon as possible.

To get in-depth knowledge on any trending technologies along with its various applications, you can enroll for live  Edureka MS Excel Online training  with 24/7 support and lifetime access.

Recommended videos for you

Data visualization-how to make sense of data, introduction to pentaho bi, visual analytics with tableau, qlikview – what’s your business question, recommended blogs for you, power query: unleash the potential of data transformation, tutorial on advanced excel formulas, top 15 power bi projects to develop your skills in 2024, what is data modeling in power bi and its best practices, tableau charts and its different types, cluster analysis steps in business analytics with r, uses and benefits of pentaho, what is ms excel and how to use it, google acquires looker, salesforce acquires tableau | what does this mean for techies, introduction to microsoft bi, power bi tutorial for beginners, rls in power bi: how to implement row level security, how to become a tableau developer, talent management: models and key fundamentals, how should you be filtering your data in tableau, how to use donut charts in tableau, how to use tableau public for beginners in 2024, tableau tutorial for beginners and experienced in 2024, mastering excel formulas: essential functions for every spreadsheet user, quick guide for tableau desktop 9 qualified associate exam, join the discussion cancel reply, trending courses in bi and visualization, business intelligence internship program with ....

• 3k Enrolled Learners
• Weekend/Weekday

Microsoft Power BI Certification Training: Pw ...

• 86k Enrolled Learners

Tableau Certification Training Course

• 58k Enrolled Learners

Applied Business Intelligence for Decision Ma ...

Advanced ms excel 2016 certification training.

• 13k Enrolled Learners

MSBI Certification Training Course

• 15k Enrolled Learners

QlikView Certification Training

• 11k Enrolled Learners

Pentaho BI Certification Training

• 4k Enrolled Learners

MicroStrategy 10 Certification Training

Browse categories, subscribe to our newsletter, and get personalized recommendations..

Already have an account? Sign in .

20,00,000 learners love us! Get personalised resources in your inbox.

At least 1 upper-case and 1 lower-case letter

Minimum 8 characters and Maximum 50 characters

We have recieved your contact details.

You will recieve an email from us shortly.

• Case Studies
• Schedule a Consultation
• Executive Search
• Professional Search

How it Works

• Our Approach
• Search Team

Our Technology

• Search Platform
• Operations & Finance
• Engineering & Technology

By Business Stage

• Early Stage
• Growth Stage

By Industry

• Gaming, Sports, Entertainment
• Healthcare/Health Tech
• Venture Capital & Private Equity
• AI & Machine Learning
• Atlas Platform

By Investment

• Private Equity
• Venture Capital
• How It Works
• ExpertAccess Program
• Job Descriptions
• Market Reports
• Tools & Templates
• Our Leadership Team
• DEI & B
• Atlas Login
• Expert Login
• Get In Touch

Top 25 Excel Interview Questions: A Guide

Many of us have used Microsoft Excel spreadsheets to keep track of household expenses, create personal budgets, and handle simple business tasks. But Excel is capable of far more complex formulas and functions  than most people give it credit for.

When you’re applying for a new professional role, you may be expected to understand Excel  to an entirely different level. While design and creative roles likely wouldn’t require these kinds of questions, those looking for administrative and clerical roles should expect to demonstrate at least an intermediate understanding of the software. Don’t be surprised if your hiring manager tests your proficiency by asking questions about how the software works.

Here, we’ll take a look at 25 questions about Excel that you should be able to answer when you sit down for an interview.

What You Need to Know About Microsoft Excel

First, what exactly is Excel? Let’s zoom out from the rows and columns for a big-picture view of this world-famous Microsoft application.

Excel was first released  by Microsoft in 1985 as a follow-up to its previous spreadsheet program, Multiplan. The first edition was only available on Apple Macintosh computers, with a Windows version coming out two years later, in 1987.

Excel really took off with the release of version 5.0 in 1993, which included Visual Basic for Applications , a macro programming language that expanded its functionality.

These days, Excel still has the largest market share  for spreadsheet software, although Google Sheets is becoming increasingly popular with younger users.

Excel is the preferred spreadsheet for professional users, in part due to the advanced data modeling capabilities of the PowerPivot add-in.

25 Excel Interview Questions You Should Be Able to Answer

Excel interview questions are designed to test your understanding of the software, as well as how it is used in specific industries. Your interviewer won’t ask you all of these questions, but they may pick and choose the most relevant ones.

Some questions may have a single correct answer, while others call for more personal answers, such as how you would use the software in practice.

As you prepare for your interview, be sure to consider which questions are relevant to your particular industry. From IT to accounting, you should be prepared to impress the HR team with your knowledge of the software based on your own experience.

Here are 25 of the most common Excel interview questions:

1) What are some of the data types used in Excel and how are they displayed?

Numbers are one of the basic data types used in Excel. You can format numbers with or without commas, and to a set number of decimal places. Percentages are another way to format numbers. For example, .05 could also be displayed as 5%.

Dates can be formatted to regional specifications, such as the standard MM/DD/YYYY format used in the U.S. Dates are stored as numbers, counting up from the number of days that have passed since January 1, 1900.

Strings are lines of text that can be made up of numbers, letters, and punctuation.

2) What is the ($) symbol used for?

The ($) symbol, or dollar sign , has two uses in Excel. It can be used to denote currency within a cell, in either the Currency or Accounting format.

It can also be used as an absolute cell reference within a formula. For example, $B$2 will always refer to B2, even if you move the formula.

3) What are absolute, relative, and mixed cell references?

An absolute cell reference  means that the cell in question stays consistent, even if the formula is moved. The ($) symbol is used to denote an absolute cell reference.

A relative reference means that when the formula is moved, the reference is changed based on the number of rows and columns by which it is moved.

A mixed reference is one in which either the row or column is absolute, and the other one is relative. For example, B$2 means that when the formula is moved, the column will change, but the row will not, as opposed to $B2, which does the opposite.

4) What order of operations does Excel use?

Excel formulas follow the standard PEMDAS order of operations. Following the (=) sign, Excel works from left to right, starting with Parentheses , then Exponents . Next comes Multiplication and Division , followed by Addition and Subtraction.

5) What is the VLOOKUP function and how is it used?

The VLOOKUP function  allows you to find specific data within a given range. You could look up a part number to find a price, or an employee ID to find a name.

The value that you want to find should be to the right of the value that you use to look it up. Use TRUE to find an exact match, and FALSE to find an approximate match.

6) What is the maximum number of rows and columns Excel can support?

MS Excel 2007 and all later versions support a maximum of 1,048,576 rows and 16,384 columns for a total cell count of 17,179,869,184 per worksheet.

If you import a source file with more rows or columns than is allowed, you may get an error message  saying “File not loaded completely.”

7) How can you find out the number of rows and columns in your worksheet?

Put your cursor in an empty column and press Ctrl + Down Arrow to go to the last row. Put your cursor in an empty row and press Ctrl + Right Arrow to go to the last column.

8) How can you count the number of cells that contain data?

You can count the number  of rows and columns that contain data by clicking on the row selector or column header, or by using the COUNTA function.

9) What is the difference between the COUNT and COUNTA functions?

The COUNT function  counts all cells that contain numbers , while the COUNTA function counts all cells that contain data . This means that COUNTA counts all cells that aren’t blank, including those that include non-numerical data.

10) What is a pivot table and what kind of reports do they display?

A pivot table  is used to show statistics, such as sums or averages, that are drawn from a larger table. Reports can be displayed in Compact, Outline, and Tabular forms.

11) What are the six options for formatting a cell ?

Number, Alignment, Font, Border, Patterns, and Protection

12) What are the six types of data  that Excel formats automatically?

Currency, Percentage, Date, Time, Fraction, Scientific

13) What does a red triangle in the upper right corner of a cell mean?

A red triangle is an indicator that there is a note or comment  attached to that cell. You can hover your cursor over the cell to view it.

14) What is the difference between a note and a comment?

Notes don’t have a “reply” function, and are used for single annotations and reminders. Comments do have a “reply” function and can be used for threaded conversations .

15) What is the Ribbon and what does it contain?

The Ribbon  refers to the row of buttons and icons at the top of your worksheet. These include common tabs like Home, Insert, Page Layout, and Data.

You can customize the Ribbon and collapse or expand it using CTRL+F1 . Some tabs only appear when you select a relevant item, such as a chart or table.

16) How can you keep the data in your worksheet safe?

Select Review > Protect Sheet > Password to create a password and lock your sheet. Users cannot copy and paste data from a password protected sheet . If you only want to lock some of the cells, you can select those cells rather than lock the entire sheet.

17) What is the difference between SUBSTITUTE and REPLACE?

Both functions are used to replace part of the text  in a string. The main difference is that REPLACE is based on the position of the text you want to replace, while SUBSTITUTE is based on the content of the text and is case-sensitive.

For example, you would use SUBSTITUTE to replace a given string located anywhere in your worksheet. REPLACE, on the other hand, can be used to replace strings located in specific positions, such as to redact the digits in a bank account number with ****.

18) How does the IF function work?

The IF function  checks to see whether a given statement is true or false. For example, it can determine whether a sum is greater or lesser than x , and show the result.

19) What are the WEEKDAY and WORKDAY functions used for?

The WEEKDAY function  displays a number from 1-7 based on the day of the week for a given date. For example, it would return 01/01/2020 as a 4 for Wednesday.

The default setting is to count from 1, starting on Sunday, but this can be customized to suit your counting scheme.

The WORKDAY function  can be used to count the number of workdays from a start date, excluding weekends and holidays.

20) What is an Excel macro?

A macro is a piece of programming code that can be used to automate actions that you do repeatedly. You can record a macro  using the Record Macro tool in the Developer tab. Simply perform the tasks that you want to record, then run back the macro .

Use the Visual Basic Editor to make changes to the code. You can direct Excel when to run the macro automatically, such as every time you open a workbook.

21) What are some of the chart types you can use in Excel?

Excel offers standard charts, such as pie charts, line charts, and column charts. It also has more complex charts such as XY (scatter) charts and map charts.

22) What is a volatile function  and what are its risks?

A volatile function is one that is recalculated every time a change is made, even if none of the cells in question have been updated. Volatile functions can slow down processing time, especially if they’re used in a worksheet with a large data set.

23) What are some ways to reduce the size of an Excel file?

• Save it as an Excel Binary Workbook (XLSB)  file
• Compress images using the Picture Tools Format tab
• Clear the Pivot Cache and remove unused Pivot Tables
• Remove data formatting and delete unused data

24) How can you avoid running malicious macros in Excel files?

Microsoft Word and Excel files can be used to hide malicious malware. By default, Excel disables all macros when opening a file unless you choose to run them. You can change your security settings  in the Trust Center to allow trusted macros.

25) What are the three wildcard characters  in Excel searches?

A question mark (?) replaces a single character. For example, “Jo?n” returns “John” and “Joan.” An asterisk (*) replaces multiple characters. For example, “J*n” will return''John `` and''Joan,” as well as “Jon.” A tilde (~) before a wildcard character is used to search for that character. For example, to find “*” you would search for “~*.”

Your interviewer may ask you more subjective questions, such as what kinds of Excel spreadsheets you’ve made, or best practices for designing a dashboard.

Remember, your task isn’t to simply recite functions you’ve memorized, but to show that you have a working knowledge of how they’re applied.

Use these questions as a starting point to brush up on your knowledge of Excel, and be prepared to share your personal experience with the program.

Refer your peers to rewarding positions at high-growth organizations.

Topics Discussed

• Interviewing

TOP STORIES

SUBSCRIBE TO OUR NEWSLETTER

Get the latest in your inbox., find your future leader with hunt club.

Get Started

• Business Analyst

MS Excel Interview Questions

Do you need excel interview questions and answers? Don’t worry! MindMajix content team has curated the top 60 excel interview questions and answers in a detailed way. This blog will help you know the basic operations and calculations you can perform with spreadsheets. You will also know how to create a pivot chart, columns in pivot tables, and much more. Undoubtedly, all these frequently asked excel interview questions and answers will help you get your job easily.

Excel is a tool that every employee needs to know to manage a large set of data and perform easy calculations. So, if you attend interviews with expertise in excel, that will boost your interview score, undoubtedly. MindMajix content team has framed the top 60 excel interview questions and answers intending to help aspirants to succeed in their interviews.

This blog packs the frequent-asked excel interview questions with answers and suitable images. They will support becoming familiar with excel operations, various advanced uses of spreadsheets, etc., in greater detail. By considering the different levels of learners, the MindMajix content team has included three sections in this blog as follows:

For Intermediate-Level

For Advanced-Level

So, based on your expert level, you can directly jump into the section

Top 10 Frequently Asked Excel Interview Questions

• What is Microsoft Excel?
• What do you mean by cell address?
• What is the use of the VLOOKUP function?
• What do you mean by pivot table in excel?
• What do you mean by macros in excel?
• What are the different VBA procedures used in excel?
• How would you create a cell dropdown list in excel?
• What is the use of freeze panes in excel?
• What are basic excel interview questions?
• What are the seven basic excel formulas?

Basic MS Excel Interview Questions and Answers

  1. what is microsoft excel.

It is an application with which we can arrange and sort data in various ways. We can add, move, delete, name, rename, hide, and show the sheets in excel. It is also known as a spreadsheet.

2. What do you mean by cells in Excel?

It is the basic unit of an excel spreadsheet. They are rectangular-shaped and store values in numbers, dates, text, etc. In other words, cells are the intersection of columns and rows of an excel sheet. No wonder there are more than 15 billion cells in excel.

3. What is the use of ribbon in Excel?

The ribbon is the topmost part of an excel sheet that consists of menu items and toolbars. We can show or hide a ribbon.

4. What do you mean by cell address?

The cell address is also called a cell reference. The cell address is used to identify a cell in an excel sheet. It is the combination of the cell column alphabet and row number of the sheet. It means that every cell address has a letter followed by a number.

5. What do you mean by relative reference in excel?

In the Relative reference type, if we copy a formula from one cell to another cell, the cells' position will change, but the formula remains the same. We can use relative referencing if we want to use a single formula for multiple rows.

We can understand relative referencing from the following example. The cell C2 in the sheet has the formula of adding A2 and B2. For this operation, it takes values from cells A2 and B2. While copying the formula is copied to other cells, such as C3 and E4, the formula doesn't change, but the values are taken from A3 and B3 for added results in C3. Similarly, the values are taken from A4 and B4 to get added results in E4.

 6. What do you understand by absolute referencing in excel?

It refers to a specific address where we cannot see the values of the cell while copying them. It means the references remain the same, which is necessary when working with excel formulas and functions.

7. What is the order of operations performed in excel to evaluate a formula?

Following is the execution order is followed to evaluate a formula.

• Parentheses or brackets
• Multiplication
• Subtraction.

8. What is the use of the VLOOKUP function?

It searches values vertically from the columns of data. It also returns a value from a different column in the same row.

9. What are the different report formats available in excel?

10. name the different data formats available in excel..

• Text formats

11. What do you mean by pivot table in excel?

It is an easy-to-use tool for summarising, calculating, and analyzing data. With pivot tables, we can make comparisons and identify patterns in data effectively.

12. List the crucial features of pivot tables. 

• Value field settings
• Top and bottom filtering
• Slicers and timelines
• Power pivot

13. What are the different functions used in excel?

• Mathematical and financial functions such as SQRT, RAND ( ), DEGREE, etc.
• Logical functions such as AND, IF,  FALSE and TRUE.
• Date and time functions such as DATEVALUE(),NOW(),WEEKDAY()
• Index match functions such as VLOOKUP and INDEX MATCH
• Pivot tables.

14. What are the various logical functions used in excel?

• IF function

15. How would you differentiate a formula from a function?

A formula is an equation we can design to calculate the excel sheet. A function can be a part of a formula. At the same time, a function is the prebuilt code of excel that we can use to make calculations.

16. What do you mean by array formula in excel?

An excel array formula can process many values instead of a single value. The array formula evaluates all the values in an array and performs multiple operations based on the conditions expressed in the formula.

17. What is the use of SUMIF functions?

We can use the SUMIF function to sum values in a range. But the essential thing is that they must meet specific criteria.

18. What are the two different macro languages used in excel?

XLM and VBA are the two languages used in excel. Here, VBA stands for Visual Basic Applications.

19. What is the use of .value, .text,.value2 in excel?

• Text displays a string or group of characters within a cell.
• value2 is used to represent the underlying value of the cell as an empty cell, error, string, or number.
• value is used to show formatted currency if the cell is formatted as currency.

20. What are the different COUNT functions you can use in excel?

Excel comes with five different count functions as follows:

• COUNT: by using this function, we can count the total number of cells that have numbers.
• COUNTBLANK: by using this function, we can count the blank cells
• COUNTIF: by using this function, we can count the cells that meet a specified criterion.
• COUNTIFS: by using this function, we can count cells that meet two or more criteria.

Intermediate-Level Excel Interview Questions and Answers

21. what are the different charts provided by excel.

Charts give a graphical representation of the data. Excel offers different chart types such as Columns, lines, bars, scatter, pie, and many more.

22. Outline the different wildcards used in excel?

• Asterisk, question mark, and tilde are the three wildcards used in excel.
• Asterisk represents 0 or more characters. For example, ex* represents Excel, expertise, and so on.
• The question mark represents any one character. For instance, R?ain means Rain or Ruin.
• Tilde is used to identify a wild card.

23. What do you mean by macros in excel?

A macro is essentially an action or set of steps that we can use multiple times. We can create a macro, record it, save, name it, and execute it. Macros are mainly used for performing iterative operations. It means that we can use macros for executing repetitive functions and instructions. Note that we can edit macros as we wish to make minor changes.

24. What is the use of freeze panes in excel?

We can use freeze zones to lock any row or column. Not only that, we can lock a group of rows and columns. Once it is locked, we can view the locked rows and columns even if we scroll the page down and horizontally.

25. What is the use of the IF function?

We can use the IF function to make a logic test. Using the IF function, we can check whether a condition is true. When the state is true, there will be output. If not, there will be another output.

26. What is the use of nested IF statements?

In nested IF statements, one IF function has another IF function inside of it. Nested IF statements are used to test multiple criteria.

27. How would you add a column in a pivot table?

• First, go to ‘PivotTable Analyse’ in the pivot table, then click on the ‘Fields, Items, and Sets’ tab.
• Select ‘Calculated Field’ from the dropdown list
• An ‘Insert Calculated Field’ window will open up. This is where we can enter the name of the column and formula.
• Finally, click OK and ADD. as a result, you will add a new column to the pivot table.

28. How does a slicer work in excel?

Slicers of software filters are used to filter a data from a large amount of data. This feature of Excel allows you to understand the details of the extracted information. Note that slicers are one-click software used to filter data very quickly.

29. How would you create a pivot chart?

• First, choose a cell in your spreadsheet
• Choose PivotTable Tools>Analyse>PivotChart
• Then, select a chart and click OK.

30. What are the uses of Name Box in excel?

• We can use the count formula
• We can perform a sum operation
• We can perform VLOOKUP operations
• we can create constants using the excel name feature

31. Can you provide a dynamic range in the data source of pivot tables?

Yes, we can provide a dynamic range in the following way.

• First, we must create a named range using the offset function
• Then, base the pivot table using the named range.

32. How would you disable the automatic sorting of data in pivot tables?

• Click the ‘more sort options’
• Right-click on ‘Pivot Tables’
• Choose ‘sort menu’
• Choose ‘More Options’
• Now, deselect ‘sort automatically’

33. How can you protect excel files and workbooks in different ways?

• We can use passwords to open or modify an excel file or workbook
• We can mark a file as final. After that, no one can change the file.
• We can use a digital signature to access a file.

34. Which function will you use to find the weekday of a date?

We can use the WEEKDAY function to find the weekday for a given date. The function's syntax is as follows:

WEEKDAY (Serial_number,[return_type])

35. What is the use of AND function?

Essentially, it is a logical function with which we can test multiple conditions. The output of the AND function will be either true or false.

The formula for the AND function is given as follows;

   “= AND (logical1,[logical2]….)

Here, logical 1 and 2 represent the various conditions.

36. How would you use cell references to perform calculations in excel?

If we use a formula in a cell, we can add cell references in the arguments of the formula. It allows getting data from other cells of the worksheet for performing calculations using the formula.

37. How would you add comments and notes to a cell in excel?

For adding comments

• Right-click the cell and insert the comment
• Or else, you can press shift +F2
• Now enter your comments
• Click outside the cell to close the comment box.

For adding notes

• Right-click the cell and insert new notes
• Enter your details in the notes
• Click outside of the cell.

38. What are the various errors that may occur in excel?

• Circular reference

39. What is the significant thing about the SUBTOTAL function?

This function is used for columns of data and not for horizontal data. It is essential to note that hiding a row while performing SUBTOTAL won't affect the process.

40. What are the different VBA procedures used in excel?

Sub procedures, as well as function procedures, are the two procedures used in excel.

Advanced-Level Excel Interview Questions and Answers

41. what is the use of what-if analysis tools.

These tools can apply several sets of values in more than one formula. As a result, we can explore different results in the end. We can perform experiments with data, complex mathematical calculations, etc.

42. What are the critical differences between subroutines and functions in VBA?

43. Differentiate: ThisWorkbook and ActiveWorkbook in VBA?

This workbook denotes the name of the workbook in which the code is running. On the other hand, ActiveWorkbook indicates the workbook that is currently active.

44. How do you create named ranges in excel?

• Choose the range that you want to name.
• Now click formulas, then go to create from the selection
• Choose the checkboxes in the ‘Create names from selection’ dialog box
• Choose the top row if the table has a header row at the top.
• f you have a top row and column header, choose the top row and left column options.
• Finally, click OK.

45. How would you create a cell dropdown list in excel?

• First, choose the cells that need to contain lists
• Click DATA>Data Validation on the ribbon
• Then, set Allow to List in the dialog box
• Click source and then type the text or numbers you need to add to the dropdown list.
• Lastly, click OK.

46. How would you apply the same formatting to multiple sheets?

• First, control+click the sheet tabs for selecting the sheets.
• The selected sheets will turn into white color.
• If you make changes in any of the selected sheets, it will be reflected in all other sheets.
• Again, double-click the selected sheets to unselect them once the format changes are over.

47. How can you link a cell in excel to a file or webpage?

• First, click the cell for which we want to create a link
• Click ‘Link’ in the ‘Links’ group on the ‘insert’ tab.
• Then, click the existing file or web page under the ‘Link to’ option.
• Click the current folder and select the file we want to link
• If we want to link a web page, we need to click 'browsed pages' to choose a web page.

48. How would you rearrange columns in an excel sheet?

• Locate your cursor on the top of the column that you want to move
• Highlight the column
• Hold the shift button and move the column right or left
• Release the click in the mouse once you move to the location where you want to move the column.

49. What do you understand about the red triangle at the top of a cell?

It is shown to represent a comment associated with that cell. When we move the mouse over the symbol, it will show the comment.

50. How does INDEX MATCH work in excel?

INDEX MATCH is essentially a combination of the INDEX function and the MATCH function. When these two functions are combined, they return a value resulting from these two functions. Note that INDEX returns a value based on the column and row number, whereas MATCH returns a value based on the position of a cell.

51. How would you convert an excel file into a pdf file?

• Choose the part of the excel sheet that you want to convert into excel
• Click the file ‘menu’ and go to the ‘Export’ option
• Then click create pdf and then click options to make settings of the pdf file
• Click ‘OK’ and name the file.

52. How to apply the transpose function?

• Select the cells for which you apply the transpose function
• As transpose is an array formula, you need to select the exact number of cells
• Now, you can enter the formula  “=TRANSPOSE(A1:F5)”
• You need to press Ctrl+Shift+Enter together
• Finally, the data will be transposed.

53. How would you pass arguments to the VBA function?

We can pass an argument to the VBA function as a value or reference.

Consider the following VBA Code.

If we pass arguments as a reference, it will display 30. The original value of x will be changed.

If we give arguments as values, it will show 10, and the original value is unchanged.

54. How can you insert a hyperlink in excel?

First, select the text in a cell for which we want to create a hyperlink. Then, press Ctrl+K. Choose the existing file from the folders or enter the web page link. Press OK to create the hyperlink.

55. How would you use advanced filters in excel?

• Select the data in the excel sheet
• Click the data tab and go to Sort & Filter
• Click the Advanced button
• A pop-up will open
• Choose either filter or copy to another location
• Click OK, finally.

56. How would you filter data in pivot tables?

• Choose the cell in the pivot table
• Go to PivotTable analyze and choose insert slicer
• Choose the fields and click OK
• Then, arrange the size and position of the slicers on the table
• Choose the items that you wish to show in the pivot table

57. Can you write a VBA code for finding the cone volume?

58. what shortcut can you use to apply a formula for an entire column.

We can double-click on the cell's bottom right corner containing the formula. Suppose the formula is in the first cell of the column, we can use the following method.

• Select the complete column by clicking the column header
• Press Ctrl+Space
• Use Ctrl+D to fill the consecutive cells.

59. What filter would you use to analyze a list using a database function?

We will use the advanced criteria filter to analyze the list. We can use this filter to test more than two conditions.

60. Why do we use cross-tabulation in excel?

Cross tabulation or cross tab is the statistical method applied for quantitative analysis. We can use this method to summarise large datasets. This approach helps to identify the essential data from the large data. We can use cross-tabulation to analyze the relationship between different variables.

Most Common Excel FAQs

1. what are basic excel interview questions.

Basic excel interview questions cover cells, formulas, cell referencing, data formats, functions, and report formats. You must prepare questions and answers relevant to these topics so you can quickly attend basic excel interview questions.

2. What are the basic functions of excel?

3. how do i prepare for the excel interview.

The main thing is that you must have hands-on experience while learning excel concepts. Therefore, you can quickly answer your excel interview questions with practical examples.

4. How to crack an excel interview?

If you want to crack an excel interview easily, you must be familiar with the basic operations of excel. Moreover, you must be familiar with functions, filters, and report formats. It will benefit you if you are an expert in macros, pivot tables, and subroutines.

5. What are the seven basic excel formulas?

6. what is a vlookup in excel.

VLOOKUP stands for vertical lookup. It helps to find a specified value vertically in the table. Also, we can use VLOOKUP to find the exact match as well as the approximate match from a table in an excel sheet.

7. How many cells are in excel?

There are 10, 48,576 rows and 16,384 rows in an excel sheet.

9. What is a PivotTable example?

Essentially, PivotTable is nothing but a statistical tool that summarises the data in an excel sheet. It manages a large set of data efficiently to generate desired reports. With PivotTables, we can perform sum, range, average, as well as outliers. For example, we can count sales by department, compare the sale of a product with another product, and so on.

Tips To Clear Excel Interviews

• Be clear with basic excel operations: You must be very clear with excel basic operations such as creating sheets, and tables, adding rows and columns, etc. And you must be thorough in using filters and formulas. This is because they will help save time enormously—no wonder every employer encourages saving time and enhancing productivity.
• Show up your expertise: When questions come relevant to the advanced use of excel, take the chance. It will impress interviewers. You show up your expertise in brief.
• Quote real-time examples: While answering questions, it is essential to add real-time examples of the excel application. You can quote how you have used excel for past projects and works – not everything in detail, but how you used excel and the outcomes.
• Be Honest: If you don’t know the answer to any complicated question, please openly say 'Don't know' instead of giving irrelevant answers, wasting interviewers' time. At the same time, note down the questions carefully to learn them later. It will show your commitment to learning new and unknown concepts.

Excel is one of the basic skills that every employee must have. Suppose you are an expert in excel, you can definitely contribute to the productivity of any organization. We hope this blog helped you by providing the top 60 excel interview questions and answers. If you go through online Bussiness Analyst training in addition to learning the excel interview questions, it will improve your hands-on experience and expertise to high levels. MindMajix offers training courses on excel concepts for aspirants who wish to become experts in excel application

Madhuri is a Senior Content Creator at MindMajix. She has written about a range of different topics on various technologies, which include, Splunk, Tensorflow, Selenium, and CEH. She spends most of her time researching on technology, and startups. Connect with her via LinkedIn and Twitter .

• Business Analysis Framework
• Business Analyst Interview Questions
• Business Analyst Tutorial
• ELK Stack Tutorial
• Kibana Tutorial
• Top 10 Business Analytics Tools For Business
• What Is Business Analytics?
• Product Analyst Interview Questions
• Goldman Sachs Interview Questions
• Deloitte Interview Questions
• Uber Interview Questions
• Accenture Interview Questions
• EY Interview Questions
• Mphasis Interview Questions
• Business Analyst Projects and Use Cases
• Explore real-time issues getting addressed by experts
• Test and Explore your knowledge

Excel Expert Interview Questions

The most important interview questions for Excel Experts, and how to answer them

Getting Started as a Excel Expert

• What is a Excel Expert
• How to Become
• Certifications
• Tools & Software
• LinkedIn Guide
• Interview Questions
• Work-Life Balance
• Professional Goals
• Resume Examples
• Cover Letter Examples

Interviewing as a Excel Expert

Types of questions to expect in a excel expert interview, technical proficiency questions, problem-solving and case study questions, data analysis and interpretation questions, scenario-based excel challenges, behavioral and communication questions, stay organized with interview tracking.

Preparing for a Excel Expert Interview

How to prepare for an excel expert interview.

• Brush Up on Advanced Excel Functions: Ensure you have a strong grasp of advanced Excel functions such as VLOOKUP, INDEX/MATCH, pivot tables, data validation, array formulas, and macros. Practice using these features in real-world scenarios.
• Understand Data Analysis and Reporting: Be prepared to discuss how you analyze large datasets and generate reports. Familiarize yourself with Excel's data analysis tools like Power Query, Power Pivot, and the ability to create dynamic dashboards.
• Review Case Studies and Business Scenarios: Practice solving business problems with Excel. Be ready to explain how you've used Excel to streamline processes, improve data accuracy, or provide actionable insights in previous roles.
• Prepare for Practical Tests: Many interviews include a hands-on Excel test. Refresh your skills on common test topics, such as data manipulation, formula writing, and chart creation.
• Stay Updated on Excel Developments: Excel is constantly evolving, so make sure you're familiar with the latest features and updates, including integration with other Microsoft tools like Power BI.
• Communicate Your Thought Process: Be ready to explain your reasoning and approach to solving Excel-related tasks. This demonstrates your analytical thinking and attention to detail.
• Ask Insightful Questions: Prepare questions that show your interest in how the company uses Excel and data analysis in their operations. This can also reveal opportunities where your skills could be particularly beneficial.
• Practice Explaining Complex Concepts: You may need to explain complex Excel functions or data analysis concepts to non-technical stakeholders. Practice communicating these ideas clearly and concisely.
• Mock Interviews: Conduct mock interviews focusing on both technical Excel questions and behavioral questions to refine your communication skills and technical proficiency.

Excel Expert Interview Questions and Answers

"how do you ensure data accuracy when creating complex excel models", how to answer it, example answer, "can you describe a time when you used excel to solve a complex problem", "explain how you use macros to improve efficiency in excel.", "what methods do you use to analyze large datasets in excel", "how do you ensure your excel spreadsheets are user-friendly for others", "describe your experience with excel's advanced functions, such as index/match, sumifs, or array formulas.", "how do you approach creating a dashboard in excel", "can you walk us through a time when you had to troubleshoot a complex excel issue", which questions should you ask in a excel expert interview, good questions to ask the interviewer, "can you describe the types of projects that the previous excel expert worked on and what their biggest achievements were", "what are the most common challenges your team encounters with data analysis and management, and how do you expect an excel expert to address these", "how does the organization ensure the accuracy and integrity of its data, and what role would i play in this process as an excel expert", "in what ways does the company support ongoing learning and development for specialized roles like excel experts", "could you provide an example of how an excel expert's work has directly contributed to a strategic decision or outcome within the company", what does a good excel expert candidate look like, advanced technical proficiency, analytical and critical thinking, data visualization and reporting, problem-solving abilities, attention to detail, effective communication, continuous learning and adaptability, interview faqs for excel experts, what is the most common interview question for excel experts, what's the best way to discuss past failures or challenges in a excel expert interview, how can i effectively showcase problem-solving skills in a excel expert interview.

Excel Expert Job Title Guide

Related Interview Guides

Unlocking business insights through data, driving strategic decisions with numbers

Driving business growth and efficiency through data-driven insights and strategic analysis

Unearthing insights from data, driving strategic decisions with predictive analytics

Driving data-driven decisions, transforming raw data into actionable business insights

Transforming raw data into valuable insights, fueling business decisions and strategy

Transforming data into actionable insights, driving business decisions and growth

Start Your Excel Expert Career with Teal

20 Most-Asked Excel Job Interview Questions for Business Analyst

• Updated on July 17, 2024

It can be difficult sometimes to pinpoint the exact tasks of a business analyst . That is because every business analyst has multiple tasks that they need to accomplish at the same time. Therefore, even the title of ‘business analyst’ can be considered a very broad term which can include titles such as data, process, project analyst and manager. This article will explore Excel for business analysts .

The key responsibility of a business analyst can consist of establishing plans and models for the finance departments of companies. It is not only an important task for the department but also this position decides on crucial financial decisions for the company itself. That is why a successful business analyst should focus on learning and improving their financial reporting, forecasting and strategizing.

Some of the duties of business analysts are analyzing the financial opportunities and threats of the company; and giving and sharing business information with and from the shareholders of the company. Establishing comprehensive analyses, identifying and solving financial problems or challenges, and budgeting the internal and external business also comes with the job position of business analyst. Also, if you need a portfolio for your works, you can use a essay writers service and build you a business analysis article collection.

One of the essential skills for a business analyst is the ability to be technically literate. That means that a business analyst should be aware of the various functions of various applications and useful software. One of these programs (and perhaps the most useful and popular of them all) is Microsoft Excel.

Below in the later chapters of this blog post, you can find 20 job interview questions related to Excel for business analysts.

Question 1: What are the common data formats in Microsoft Excel?

If you are applying for a business analyst job position, you are expected to know certain details about Microsoft Excel. The data formats which Excel allows to run in are one of these details. Due to the fact that Microsoft Excel is a business analyst’s one of the most essential tools at the workplace, knowledge about the common data formats would be crucial. This knowledge is not only required at the work but also is needed in your job interview.

A sample answer for this question can be: “The most common data formats used in Microsoft Excel are numbers, percentages, dates and sometimes texts (as in words and strings of texts).”

Question 2: How are these data formats used in Microsoft Excel?

Similar to every job position and every job interview, only knowing the information is just the half of the work. You need to justify your knowledge with your applications, actions and also, practical knowledge. Therefore, only memorizing the data formats would not be helpful, if you have not used them yourself. You need to know what they are and on top of that, you need to know how they are used in the job.

A sample answer for the uses of data formats in Excel can be: “Numbers can be formatted in data cells as decimals or round values. Percentages show a part of a whole and the whole being 100%. The dates can automatically change depending on the region and location Microsoft Excel is connected from. And the text format is used when analyses, reports or other documents are entered into the Excel spreadsheet as data.”

Question 3: What are the cell references?

Cell references (or in fact, references are general) should be one of the most useful tools an analyst can utilize. The different cell references and their uses can sometimes be seen as trivial details; however, they are absolutely not. They are actually incredible time savers, especially for an always busy business analyst.

A sample answer on the definition of cell references: “Cell references are used to refer to data located in the same Excel spreadsheet but to data in a different cell. There are three different cell reference types: Absolute, relative and mixed cell references.”

Question 4: What are the functions of different cell references?

A business analyst should be able to know specifically the difference between the various cell references. This knowledge will not only come in handy at your job interview but also in communicating and collaborating with your coworkers and employees.

A sample answer can be: “The absolute cell reference forces the data to stay in the cell which it was put in. No matter how many formulas are used on the data itself, an absolute cell reference stays with the data. The relative cell reference moves with the cell when the formula on the cell is moved to another one. And the mixed cell reference indicates that the row or the column related to the data cell is changed or moved.”

Question 5: Which key or combination of keys allow you to toggle between the absolute, relative and mixed cell references?

Supervisors, bosses, company owners, employees, your coworkers and even you have something in common: Everybody wants to save time. No one wants to spend unnecessary time on something they can otherwise do it in a much lesser time. Therefore, everyone loves shortcuts. In addition to that if you are applying for a business analyst position, they are going to expect that you know certain Microsoft Excel shortcuts.

A sample answer on the key to press to shift between the cell references can be: “In Windows devices the F4 key lets you to change the cell references. In Mac devices the combination of keys Command + T will allow you this shift.”

It would be useful to note here that if you are an expert on using Microsoft Excel on only one of the devices, do not worry. You do not need to know every shortcut in each of the operating systems. If you efficiently can use one and show that to the employers, they would be satisfied.

Question 6: What is the function of the dollar sign ($) in Microsoft Excel?

In fact, the dollar sign ($) serves many purposes in Excel and none of them is related to the dollar currency at all. The dollar sign has multiple functions including some of the formulas you can type in. It also can denote any of the currencies written in a spreadsheet. However, in terms of cell references the dollar sign has only one purpose.

A sample answer can be: “The dollar sign when written tells Excel whether to change the location of the reference or not, if the formula for it is copied to other cells.”

Question 7: What is the LOOKUP function in Microsoft Excel?

As you are going to see in the later chapters of this blog post, the LOOKUP function is one of the most popular tools in Excel. If you are applying for a business analyst job, it is also one of the functions that you must be familiar with.

A sample answer on the LOOKUP function can be: “The LOOKUP function allows the user to find exact or partial matches in the spreadsheet. The VLOOKUP option lets the user search for data located in the vertical position. The HLOOKUP option functions the same way but in the horizontal plane.”

Question 8: What is the disadvantage of using the VLOOKUP function?

These kinds of questions test you in a way that you are familiar with the advantages and the good functions of Excel as well as the disadvantages. It also offers you a chance to exercise your critical thinking muscle and combine it with your analytical knowledge.

A sample answer can be: “The major disadvantage of the VLOOKUP function is that it can become ineffective very easily. If you shift data from a column to another, it will also change the column numbers that data was located. This forces you to manually enter the same formulas into (now) different data cells.”

Question 9: How do you insert a comment into an Excel spreadsheet?

A sample answer about the way you can insert comments can be: “You can insert comments into your spreadsheet simply by selecting the cell, right clicking and choosing the ‘Insert Comment’ option.”

Question 10: What is the difference between a comment and a note?

A sample answer on the difference between the two terms can be: “While replying to comments is available; replying to notes attached to cells is not. The notes can be looked at as single annotations.”

Question 11: How important is VBA for a business analyst?

It can be important to actually sit down, do some research on the areas that you think you are lacking and practice your knowledge of Visual Basic for Applications (VBA). These actions should be done before the job interview. Otherwise, a topic such as VBA which every business analyst should be familiar with can be your weakness. And it also can become the reason that you are rejected for the job position that you have applied for.

A sample answer can be: “If a business analyst wants to be successful and work in respected companies, she or he needs to learn and get familiar with the required tools. They should always follow the trends and practice with the most modern devices and tools. VBA is perhaps the most important function of Microsoft Excel and it is crucial for a business analyst to easily use this tool.”

Question 12: What is conditional formatting?

No matter which job position you hold at the moment or which position you are looking to apply for, conditional formatting is an essential function of Excel. It is so simple and so useful at the same time that you might have been using even without knowing what it is called!

A sample answer can be: “Conditional formatting allows you to change the visual aspect of cells. For example, you want all the cells which include a value of 3 to be highlighted with a yellow highlighter and made italic. Conditional formatting lets you achieve this action in only seconds.”

Question 13: What are the most important functions of Excel to you as a business analyst?

The functions you will list as an answer to this question will show the interviewer what you do in Microsoft Excel. You might me more ready for the interview, if you have at least 5 functions ready for this question. You do not have to memorize every formula and list the most complicated ones. Ask yourself this question about the function you want to list: What are my most used functions?

A sample answer for the functions that a business analyst most use can be: “I most often use the LOOKUP function; followed by COUNT and COUNTA functions. The IF and MAX and MIN functions are also one of the ones I usually use.”

Recommended Reading: Most Used Excel Functions

Question 14: What does the COUNTA function execute in an Excel spreadsheet?

Now, you have listed your favorite and most used functions. Regardless of the functions you list, the interviewer can ask any function’s purpose or use. That is why it is important for you to generally know the most important functions. You can find the functions and formulas every business analyst needs to know later in the chapters below in this blog post.

A sample answer on the COUNTA function can be: “The COUNTA can scan all the rows and columns that contain data, identify them and ignore the empty cells.”

Question 15: What is the difference between the functions of COUNT and COUNTA?

Once again, if the interviewer is certain that using Microsoft Excel is one of the essential skills you need to have in order to be accepted, you will be asked a lot of ‘function questions.’

A sample answer on the difference can be: “While COUNT function counts the cells that contain numbers as their data source; the COUNTA function returns the number of cells that contain any kind of data in it. These can include numbers, texts, letters, dates, etc.”

Question 16: Can you import data from other software into an Excel spreadsheet?

You, as a business analyst, might be using other programs and software to enter your data. However, if the company wants you to fully transfer into Microsoft Excel, you should be able to do that without any worry and more importantly, without any delay.

A sample answer on data can be: “Importing data from various external data sources into an Excel spreadsheet is available. Just go into the ‘Data tab above in the toolbar. And by clicking the ‘Get External Data’ button, you will be able to import data from other software into Excel.”

Recommended Reading: Google Sheets vs. Microsoft Excel: Which is better?

Question 17: Why do you think the knowledge of Microsoft Excel is important for a business analyst?

A sample answer for the importance of Microsoft Excel for a business analyst can be: “Using Excel and dealing with the company’s data is crucial because it’s the only data the organization has. And it is in the hands of the business analyst to analyze and come up with results and solutions for problems. The business analyst is also the financial consultant as well as an analyst. You can become the person that the CEO listens to in order to ‘make’ or ‘break’ certain deals.”

Question 18: As a business analyst, do you choose to store your sensitive data in a Microsoft Excel spreadsheet?

In order to answer these kinds of direct questions, the only thing you need to do is to be honest. That is it. No interviewer wants to see a ‘yes man’ when conducting a job interview. If there are some things you think that are lacking in Microsoft Excel, you should speak and voice your opinion.

A sample answer can be: “Yes, I do store my client’s data in Microsoft Excel. However, if the data I am dealing with is confidential, then I would not be storing that sensitive data in an Excel file.”

Question 19: As a business analyst, how would you operate with sensitive data in Microsoft Excel?

A sample answer for the security of information in an Excel file can be: “Due to the fact that I would be responsible for the transfer, the possible disappearance or the leak of the data, I would store confidential data in a software other than Microsoft Excel.”

Question 20: How can you protect your data in Microsoft Excel?

A sample answer for the security of an Excel spreadsheet can be: “From the Review tab, you can choose to protect your sheet with a password. That way the spreadsheet will be password protected and cannot be opened or copied without the password.”

What Skills Do You Need to Be A Business Analyst?

The required skills for a business analyst consist of both soft and hard skills. That means only graduating from a business school or with a business degree will not automatically turn you into the ‘ideal candidate’ for the job.

A business analyst’s skill set should be analytical and technical as much as it should be social and communicative. You can find required skills for a business analyst in regards to both hard skills and soft skill below:

Social Skills

A business analyst should be able to present their findings to a group setting. The presentations can demonstrate the social skills a business analyst has. You should be effectively communicating with your coworkers, supervisors and clients in order to discuss certain issues. This applies to presentations with your employees to the meetings with the CEO itself.

Critical Thinking Skills

One of the most significant actions that business analysts are responsible for is evaluation. And evaluation and assessment are actions that require critical thinking. As it was mentioned above in the previous chapter, being a ‘yes man’ is not something that will boost your confidence and allow you to do a better job.

That is why critical thinking will help you focus on what you can ask and question. This skill will not only help you improve your work but also develop new skills and improve other abilities.

Problem Solving Skills

No matter what position you work in, no matter which company you work for, if you are unable to solve the problems you face, you will not be successful. Period, it is that simple. Because every imaginable job itself is some way related to solving problems.

That means you have to be good at identifying the problems themselves. Then you have to consider which ones can be or should be solved. And after that you would be able to create a plan in order to solve the problems the organization develops and the challenges you as a business analyst face.

To improve your Excel or PowerBI skills for more understandable dashboards and reporting, the most important three tips from experts are to start small, to learn how to create visuals and also to learn by doing. It’s very important to practice what you learn. 

What Do Business Analysts Use Excel For?

Simple and complicated spreadsheets.

When a business analyst is working for a client and is processing results or when she or he is doing personal side work, they will be using Microsoft Excel. And they are going to create spreadsheets no matter how basic or complicated they are. In order to analyze and report your results, you are going to always need to create spreadsheets and operate in them.

Filtering, Sorting and Processing Large Amounts of Data

Using Microsoft Excel for filtering and sorting data is something every Excel user does. However, not every user might have the amount of data a business analyst handles and deals with. That is why in order to quickly and efficiently sort and filter vast quantities of data business analysts use Microsoft Excel for the job.

Creating Charts and Graphs

Excel’s endlessly customizable charts and graphs can come in handy for a business analyst when she or he is doing a presentation. These visual illustrations of data can make the audience understand and look at the data in a much clearer way.

Which Microsoft Excel Functions and Formulas Do Business Analysts Need to Know?

• Basic Math Operations: There are four basic mathematical operations. They include addition, subtraction, multiplication and division.
• LOOKUP Functions ( VLOOKUP and HLOOKUP )
• SUM Function
• COUNT Function
• COUNTA Function
• AVERAGE Function
• MAX and MIN Functions
• IF Function

Here, you can find some of the most essential functions a business analyst should be familiar with. However, there are a lot more to this list. If you are feeling like you should practice more on the functions you know and learn different ones, you should try the Someka Excel Formulas Training Kit .

This Excel template provides you various real life Excel problems and teaches you how to solve them with the necessary functions. There are three levels of function complexity: Beginner, Medium and Advanced Level. The Full Package Training Kit has the total 30 functions and 100 real world questions with it. If you are looking to train and practice on your own, this is the only necessary tool for you!

Other Excel Functions for Business Analysts 

Pivot tables.

Pivot table is perhaps the tool that will save you the most time in Excel. It is capable of summarizing and analyzing data in a matter of seconds. And regardless of the size of the data itself, it will provide you the summary of it. With these features, it can be one of the best ‘friends’ of a business analyst who is using Microsoft Excel.

Charts are the essential way for you to present your data. Everyone can turn in pages upon pages of numbers. And sometimes that kind of presentations are required as well. However, as detailed and customizable as the Charts section in Excel is going to help you create incredible looking many types of charts:

• Column Chart
• Pie Chart (2-D and 3-D)
• Doughnut Chart
• XY (Scatter) Chart
• Bubble Chart
• Stock Chart
• Surface Chart
• Radar Chart
• Combo Chart

Calculated Items

Calculated items work as custom formulas that use the sum of different items.  It calculates the sum of different items and put into a pivot field. It is almost a supportive feature of Pivot Tables. You can use it in calculating delivery and shipment details and also, for the product orders as well.

Conditional Formatting

This feature also goes well when you are presenting your results and the processed data to a group. It helps you differentiate different data cells from each other. You can highlight, emphasize and change the format of the cells to your choosing. As a business analyst, you are going to always deal with numbers. Therefore, presenting them in a much fashionable way will help everyone to easily realize and understand what they are looking at.

Excel Templates for Business Analysts!

Flowcharts can be a handy way to demonstrate your analyses. Someka Automatic Flowchart Maker has incredible customization features. You can change the shapes, colors, categories and decision labels with only the click of a few buttons! You can also use the drag-and-drop tool if you want to stay with the basics.

– This workflow is created by Someka’s Flowchart Maker – 

Also, conducting SWOT analysis for the company you are working for can lead to more profit and less problems. A business analyst’s job is to analyze, and so analyzing the company’s strengths, weaknesses, opportunities and the threats is crucial. And you can conduct this analysis with Someka’s free SWOT Analysis Template !

Another important tool is FMEA Excel Template. This tool is for risk analysis to detect possible failure modes and plan preventive actions.

– This Dashboard is from Someka’s FMEA Excel Template –

Someka’s BCG Matrix Template creates a framework in which you can assess the value of your investments. With the four different sections titled Stars, Question Marks, Cash Cow and Poor Dog; you can analyze the market growth rate and relative market share in the Excel template. As a business analyst, this free template will be one of the tools to achieve that goal.

– This is the Dashboard section of Someka’s BCG Matrix Template – 

With the Competitive Analysis Template , you can evaluate your company’s competitors. Following that, you can even develop new strategies and financial models and manage the competition in the market. The template provides charts for each of your competitors and visual illustrations of market shares. Download this free and professional template for presentations right now and do not ever lose your place in the market!

FINAL WORDS ON EXCEL FOR BUSINESS ANALYSTS

The technical skill of using Microsoft Excel is not the only factor for becoming a successful business analyst. You need to integrate good social and communicative skills, combine them with analytical and managerial skills as well. It is a job with important and life-changing consequences for the company which you are working for. And perhaps using Microsoft Excel is not the only skill for the job, but it is without a doubt one of the most important ones.

Recommended Readings:

Complete List of Things You Can Do With Excel

Career Guide: Business Analyst

How to conduct a gap analysis?

Related Posts

Get the Reddit app

A vibrant community of Excel enthusiasts. Get expert tips, ask questions, and share your love for all things Excel. Elevate your spreadsheet skills with us!

I have an interview on Friday that said they will be testing Excel capabilities, and I'm nervous

So, there's a good job I'd like and I had the screening today. It went well and they wanted to push me through to a 30 minute Zoom call with an Analytics guy to go into Excel proficiency.

Can anyone tell me what to expect at a point? It's not a senior role, but I've been unemployed since October due to the pandemic. I've been pulled in a lot of directions at once. Some interviews want a case study, some want SQL, some want Python, etc. It's not been easy I'm constantly pulled from one thing to the other so I'm not really a master of anything. To do so I need to be in a work environment where I do these things daily and there's some focus.

On the whole, can someone tell me what to look out for? I'm not sure if it will be a full-on whiteboarding. The HR rep said it'd be a "quiz" and then sort of hesitated and said "well, that makes it sound more intense than it is." So, I don't know if it'll be horrible, but I'm not sure what to expect. Live demonstrations kill me. I'm so anxious and not confident. I could probably figure out just about anything with time, but my anxiety has shot through the roof. Like, I can do a pivot table but it takes me forever to figure out.

But, I have a huge data set to work with (it's my own) and I'm wondering what i maybe can do so I don't cancel out of anxiety.

By continuing, you agree to our User Agreement and acknowledge that you understand the Privacy Policy .

Enter the 6-digit code from your authenticator app

You’ve set up two-factor authentication for this account.

Enter a 6-digit backup code

Create your username and password.

Reddit is anonymous, so your username is what you’ll go by here. Choose wisely—because once you get a name, you can’t change it.

Reset your password

Enter your email address or username and we’ll send you a link to reset your password

Check your inbox

An email with a link to reset your password was sent to the email address associated with your account

Choose a Reddit account to continue

50 advanced Excel interview questions

Do you need to hire an Excel expert for your organization?

Finding the right talent isn’t always simple, and it can be challenging to evaluate their skills. However, there are a couple of useful ways to hire a professional with exceptional Excel skills. 

When you source candidates, you can give them an Advanced Excel skills test to instantly identify those with advanced Excel skills.

Then, when you have a shortlist of candidates, you can invite them to an interview and ask them advanced Excel interview questions to further gauge their skills.

If you’re wondering which questions you should ask during the interview, we’ve made it easier for you.

Here are 50 advanced Excel interview questions you can ask your candidates.

Table of contents

18 general advanced excel interview questions, 5 critical general advanced excel interview questions and some potential answers, 23 advanced excel interview questions related to visual basic for applications, 5 critical advanced excel interview questions related to visual basic for applications and some potential answers, 9 advanced excel interview questions related to macros, 5 critical advanced excel interview questions related to macros and some potential answers, 8 tips to help you use advanced excel interview questions, select the right advanced excel interview questions to evaluate your candidates.

Let’s start with some general Excel interview questions. Asking these questions will help you assess a range of advanced Excel skills and learn if your candidates have the right skill set for the role you’re hiring for.

What does a nested “If” statement do?

How is “VLOOKUP” different from “LOOKUP”?

How do you disable the automatic sorting function in pivot tables?

How do you view specific results in a pivot table?

What is meant by “data validation” in Excel?

Describe what conditional formatting is in Excel.

Describe how to use conditional formatting in Excel.

How do you preserve the formatting of pivot tables when refreshing them?

Which approach would you use to measure percentages in Excel?

How do you create a feature shortcut in Excel?

How do you apply an identical format to all workbook sheets in Excel?

How do you return to a certain cell in a worksheet?

Explain what a “relative cell address” is.

Explain the order of operations Excel follows when evaluating a formula.

Describe an advantage of using sheet formulas in Excel.

How do you build named ranges?

Describe how you would protect workbooks, sheets, and cells in Excel.

Explain what a variant data type is.

When you are interviewing candidates for your vacancy, look for these answers in response to 5 of the most important general advanced Excel interview questions.

1. Describe how you would protect workbooks, sheets, and cells in Excel.

Candidates may mention that it’s possible to protect a workbook by setting a password, which the user must enter to open the workbook.

They may say that it’s possible to protect sheets by navigating to the “Review” tab and selecting “Protect Sheet”. This prevents users from adding or removing sheets. Candidates might also explain that you must set a password for the worksheet and choose the permissions that you wish to give to users.

Candidates may also tell you that since cells are locked by default in Excel, they are all protected when you protect the worksheet. However, it’s possible to choose cells that you don’t want to protect by right-clicking them and selecting “Format Cells”. From there, navigate to the “Protection” tab and deselect the “Locked” option. Finally, click “OK”.

2. How do you disable the automatic sorting function in pivot tables?

Do your candidates know how to disable the automatic sorting function? This is done by right-clicking on the pivot table, selecting “More Sort Options” to open the “Sort” dialog box, clicking “More Options”, and deselecting the “Sort automatically” option.

3. What is conditional formatting in Excel?

Candidates should know that the conditional formatting feature enables users to format cells that meet certain criteria. For example, they may note that it’s possible to format a cell to have a bold font if the value the cell contains is greater than 1,000.

4. Describe an advantage of using sheet formulas in Excel.

Candidates may point out that sheet formulas can not only make calculations but also update the result of a calculation if you replace a number in a cell that is part of the formula.

5. Explain the order of operations Excel follows when evaluating a formula.

Excel follows the PEMDAS acronym when it calculates a formula. Promising candidates should know that PEMDAS refers to the following order of operations: 

P arentheses

E xponentiation

M ultiplication or D ivision (left to right)

A ddition or S ubtraction (left to right)

The following 23 advanced Excel interview questions will help you learn whether your candidates have advanced skills in and knowledge of Visual Basic for Applications (VBA) in Excel. 

What does “Option Explicit” do in VBA?

Name three core modules in VBA.

How do you pass arguments in VBA?

How is “ThisWorkbook” different from “ActiveWorkbook” in VBA?

How are subroutines different from functions in VBA?

Explain what user forms are in VBA.

Explain what ADO is.

Describe some of the ADO objects.

Which functions are commonly used in VBA?

What does “ByVal” do in VBA?

What does “ByRef” do in VBA?

How are “ByVal” and “ByRef” different in VBA?

Explain how to assign a value to an array.

What does it mean to format expressions in VBA?

What is the purpose of comments in VBA, and how do you write a comment?

Name three ways to correct code in VBA.

Explain what type of language VBA is.

What is the code window in VBA, and where is it located?

Explain the different types of VBA procedures.

Explain how variables are declared in VBA for Excel.

Explain what loops are in VBA.

Explain how to test conditions in VBA.

Which shortcut will take you to the VBA editor?

To learn whether your candidates have advanced VBA skills, compare their responses with these potential answers to the 5 crucial Excel interview questions related to VBA.

1. What is the code window in VBA, and where is it located?

The VBA code window is a space in which users write code for VBA projects. Do your candidates know that the VBA code window is located on the right-hand side of the VBA pane? Candidates may mention that the shortcut key used to view the code window is F7.

2. Explain what loops are in VBA.

Candidates should know that loops can be used in VBA to repeat code blocks until certain conditions are met. They can also be used to highlight empty dataset rows or strings of text. There are different types of loops in VBA, including the following:

3. Name three core modules in VBA.

Three of the main core modules in VBA are:

Class modules. Users can make new objects and enhance existing objects using class modules.

User forms. User forms are a type of module used for building a graphical user interface (GUI) for an application.

Code modules. Code modules are usually the default modules in which functions and procedures are written.

4. Explain what ADO is.

ADO stands for ActiveX Data Objects. Candidates should know that ADO can help you access data from several sources. You can retrieve and gather data from all data sources so that it’s located on one platform. Without ADO, you would have to write the code for every required data source.

5. What is the purpose of comments in VGA, and how do you write a comment?

Do your candidates know that they can write comments in VBA code that describe the code’s logic? Can they explain that these comments help other users work with the code? Are they aware that VBA comments are created by placing an apostrophe at the beginning of a line of code?

Ask your candidates these 9 advanced interview questions to find out whether they have the comprehensive and advanced Excel skill set required for your open role.

What are the advantages of macros?

How do you delete a macro from a workbook?

How do you run a macro automatically when you open a workbook in VBA?

How do you save macros? 

How do you hide macros?

Describe four ways to run macros.

Is it possible to pull data from other workbooks with macros? How do you do this?

How do you assign a macro to a particular button?

How do you stop recording a macro?

Find out whether your candidates have advanced skills with Excel macros by comparing their responses with the potential answers listed below.

1. What are the advantages of macros?

Your candidates may say that macros offer two main advantages:

They save time by automating repetitive tasks

They enhance productivity and accuracy by reducing the need to input commands manually

2. How do you run a macro automatically when you open a workbook in VBA?

In response to this question, candidates may state that you should start by opening the VBA editor with the shortcut Alt+F11. Then, on the left side of the project explorer, double-click “ThisWorkbook”. Input the code “Private Sub Workbook_Open()” and hit the enter key. Next, write the recorded code between “Private Sub Workbook_Open()” and “End Sub”. After you close the VBA editor, save your workbook in Excel Macro-Enabled Workbook (XLSM) format.

3. How do you save macros?

Candidates may describe two ways to save macros. You may only need your macro for the particular workbook in which you made it. In this case, just select “File” and then “Save” or “Save As”. 

Alternatively, you may need to save a workbook that contains macros and preserve them after you save the workbook. This requires you to select “No” when asked if you want to continue saving as a macro-free workbook. Then, save the workbook in XLSM format.

4. How do you hide macros?

Candidates may mention that users can hide macros by first opening the macro that they want to hide. They must then go to the first line of code, which contains the “Sub” command. At the start of the line, they need to write the word “Private”, leaving a space between it and the macro’s name. Once the macro is saved, it will be hidden.

5. How do you stop recording a macro?

Are your candidates aware that to stop recording a macro, users must navigate to the “View” tab and select “Macros”? They must then click on the “Stop Recording” option.

Here are best practices you can use before and during interviews to get the most out of our list of advanced Excel interview questions and make a well-informed hiring decision.

1. Give candidates an Advanced Excel skills test before the interview

As mentioned in the introduction, since pre-employment skills tests help you instantly spot candidates who don’t have the right Excel knowledge and skills, you should use an Advanced Excel skills test to ensure that you only spend time interviewing the most promising candidates for the role. 

2. Choose a set of questions that you will ask all candidates

Consistency is essential when conducting an interview . When you use advanced Excel interview questions, ensure that you choose a set of questions that will apply to every candidate. You can ask follow-up questions to get the information you need from a candidate who fails to provide enough detail, but be consistent with the questions you ask your candidates.

3. Describe the responsibilities of the position before asking any questions

Always explain the responsibilities of the position to the candidate in more detail than the job ad before you ask them technical questions related to the role.

For example, if you mentioned that VBA is an essential part of the role in your job description , explain how this advanced Excel skill will help with the role. Or, if conditional formatting is a regular part of the role, explain why it is important. 

4. Use advanced Excel interview questions and skills tests for senior positions

It’s important that you use advanced Excel interview questions and skills tests for senior positions as opposed to General Excel skills tests or basic Excel interview questions. Always ensure that you match the assessments you use to the level of the role you’re hiring for.

5. Ask open-ended questions

Answers to open-ended questions will generally be more detailed than those to closed-ended questions. Sticking to open-ended questions such as “What do you find most challenging about using Excel?” will help you learn more about your candidates than questions like “Do you find using Excel challenging?”

6. Start the interview with general interview questions

Before you dive into a bunch of technical and advanced Excel interview questions, try to ask candidates questions about their backgrounds and previous roles. You can also ask them questions related to how they think they fit your role and organization.

7. Allow your candidates to ask you questions

Toward the end of the interview, allow your candidates to ask you questions about the role. Be prepared to answer technical questions that might come up. Ensure that you’re also prepared to give information about the team your candidates will be working with. 

8. Give candidates an approximation of how long the final selection will take

To help boost the candidate experience and close the interview, inform your candidates about your hiring decision timeline, and give them an estimate of when you will make your final selection. 

Since it can be a challenge to hire talent that fits your organization and has the right advanced Excel skills for your team, remember that skills testing can make the process easier.

Although you may have hundreds of applicants, you can narrow them down with our Advanced Excel skills test. You should make skills testing your priority and complete it before reviewing resumes .

When you have decided on a shortlist, select the advanced Excel interview questions that suit your interview process and evaluate your candidates with ease.

Hire the best talent using skills assessment tools and the right interview questions. Try TestGorilla for free .

Related posts

9 steps to effective competency mapping

How to write a project manager job description

IQ tests for employment are obsolete – here’s what to do instead

You've scrolled this far

Why not try TestGorilla for free, and see what happens when you put skills first.

Latest posts

The best advice on pre-employment testing, in your inbox.

No spam. Unsubscribe at any time.

Hire the best. No bias. No stress.

Our screening tests identify the best candidates and make your hiring decisions faster, easier, and bias-free.

Free resources

This checklist covers key features you should look for when choosing a skills testing platform

This resource will help you develop an onboarding checklist for new hires.

How to assess your candidates' attention to detail.

Learn how to get human resources certified through HRCI or SHRM.

Learn how you can improve the level of talent at your company.

Learn how CapitalT reduced hiring bias with online skills assessments.

Learn how to make the resume process more efficient and more effective.

Improve your hiring strategy with these 7 critical recruitment metrics.

Learn how Sukhi decreased time spent reviewing resumes by 83%!

Hire more efficiently with these hacks that 99% of recruiters aren't using.

Make a business case for diversity and inclusion initiatives with this data.

• Explore Interests & Self Assessment
• Prepare a Resume & Cover Letter
• Internship Resources
• Search for a Job & Internship
• Make Connections & Network
• Prepare for an Interview
• Negotiate an Offer
• Prepare for Graduate School
• Agriculture, Food & Natural Resources
• Architecture & Construction
• Arts, Audio/Video Technology & Communications
• Business Management & Administration
• Education & Training
• Government & Public Administration
• Health Science
• Hospitality & Tourism
• Human Services
• Information Technology
• Law, Public Safety, Corrections and Security
• Transportation, Distribution & Logistics
• Science, Technology, Engineering & Mathematics
• Asian/Pacific Islander
• Black/African American
• First Generation Students
• Hispanic/Latinx
• International Students
• Student Athletes
• Students With Disabilities
• Undocumented/DACA Students
• Meet the Team

Lunch & Learn: Acing the Case Interview - What to Expect, How to Prepare

• Share This: Share Lunch & Learn: Acing the Case Interview – What to Expect, How to Prepare on Facebook Share Lunch & Learn: Acing the Case Interview – What to Expect, How to Prepare on LinkedIn Share Lunch & Learn: Acing the Case Interview – What to Expect, How to Prepare on X

Join CareerSpring for our first Lunch and Learn Webinar after the summer break!

In this session, you’ll hear from industry experts on how to best prepare for a case interview. Case Interviews are when an employer asks you to solve a complex business scenario during your interview. Companies are looking for candidates who can think analytically and creatively and communicate clearly. Join us so you can understand what to expect and how best to prepare for your next case study interview.

Register to receive the meeting link and details. This event will take place on Thursday, September 5th at 12:30pm CST.

— What is a Lunch & Learn?

Lunch and Learns are a series of webinars that are hosted for CareerSpring Advisees by experienced Human Resources professionals. They cover a variety of topics to help our FGLI students and graduates along their career journey. Lunch and Learns are hosted on the first and third Thursday of each month, and are not recorded. They are designed to be somewhat interactive and presented in a small group format. All Lunch and Learns are free to attend.

If you are not yet registered as a CareerSpring Advisee, please register here .

One more step:

Spread the word by sharing this event with your social networks, save it to your calendar, add to calendar.

• Guidelines to Write Experiences
• Write Interview Experience
• Write Work Experience
• Write Admission Experience
• Write Campus Experience
• Write Engineering Experience
• Write Coaching Experience
• Write Professional Degree Experience
• Write Govt. Exam Experiences

MathCo | Interview Experience | On-Campus | Analyst Role

Eligibility: All Candidates above 6 CGPA

For shortlisting candidates to interviews a virtual online test was conducted following by communication test.

Round 1: It was a Cognitive Test which includes Quant Aptitude, Logical Reasoning, Data Interpretation, Situation Based Questions and 2 Pseudo Codes. Time: 1 hour Level: Easy – Medium

Round 2: Communication Test, it is very similar to test that was kept by Cognizant. Speaking and Reading abilities were checked along with Verbal Aptitude (30 Questions) Time: 30 minutes. Level: Easy – Medium Note: Only candidates who have cleared Cognitive Test are eligible to Communication Test.

The above rounds were conducted on the same day. Out of ~1500 applicants 145 are eligible for interview.

After a week the Interviews were conducted. There were 2 rounds: Round 1: Technical Interview Most Questions were asked based on Resume. Questions based on Python, SQL, Academic projects were asked. A Business Case Study was asked.

Candidates who cleared round 1 are asked to appear for HR interview.

Round 2: HR Interview Basic HR questions were asked. Situation based questions and Puzzles were asked.

Please Login to comment...

Similar reads.

• Experiences
• Interview Experiences
• Best 10 IPTV Service Providers in Germany
• Python 3.13 Releases | Enhanced REPL for Developers
• IPTV Anbieter in Deutschland - Top IPTV Anbieter Abonnements
• Best SSL Certificate Providers in 2024 (Free & Paid)
• Content Improvement League 2024: From Good To A Great Article

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

Join Our Newsletter

Join our subscribers list to get the latest news, updates and special offers directly in your inbox

• Interview Q & A
• Common Interview Q & A

Common SOC Analyst Interview Questions 2024

Prepare for your soc analyst interview with our comprehensive guide on common interview questions. explore key questions about soc analyst roles, responsibilities, skills, and experience to boost your interview readiness and stand out in the cybersecurity job market..

1. Can you explain the role of a SOC Analyst?

2. how do you handle a security alert from a siem system, 3. what are some common types of cyber threats you have encountered, 4. how would you respond to a suspected malware infection, 5. what is your experience with log analysis, 6. can you describe a time when you successfully handled a security incident, 7. how do you stay updated on the latest cybersecurity trends and threats, 8. what tools and technologies are you proficient with, 9. how do you prioritize and manage multiple security incidents, 10. what is your process for creating incident reports, 11. how would you handle a situation where there is a disagreement within the soc team about the severity of an incident, 12. what steps do you take to ensure compliance with security policies and regulations, 13. how do you approach threat hunting in a soc environment, 14. can you describe a challenging technical problem you solved, 15. what are some key performance indicators (kpis) for a soc analyst, 16. how do you approach learning and mastering new cybersecurity tools, 17. what is your experience with network traffic analysis, 18. how do you ensure effective communication with non-technical stakeholders during an incident, 19. what strategies do you use for threat intelligence gathering, 20. how do you handle a situation where a critical system is down due to a security incident, 21. what experience do you have with digital forensics, 22. how do you manage and prioritize your workload during high-pressure situations, 23. what are your favorite tools for security monitoring, and why, 24. how do you assess and improve the security posture of an organization, 25. can you describe your experience with vulnerability assessments, 26. how do you handle false positives in security alerts, 27. what is your experience with scripting or automation in a soc environment, 28. how do you stay organized when dealing with multiple security incidents, 29. what are some common signs of a potential security breach, 30. how do you ensure that security policies are consistently applied across all systems and departments, 31. what role does threat intelligence play in your daily activities as a soc analyst, 32. how do you deal with a situation where you do not have enough information to resolve an incident, 33. how do you ensure that incident response procedures are effective and up-to-date, 34. can you describe a time when you had to learn a new security tool quickly, 35. what is your experience with handling insider threats, 36. how do you evaluate the success of a security incident response, 37. how do you balance proactive threat hunting with reactive incident response, 38. what are your thoughts on the importance of collaboration within a soc team, 39. how do you handle conflicts or disagreements with team members regarding security decisions, 40. what are the key factors for maintaining a strong security posture in an organization.

• SOC Analyst interview questions
• SOC Analyst role
• SOC Analyst responsibilities
• cybersecurity interview questions
• SOC Analyst job preparation
• common SOC Analyst questions
• SOC Analyst skills
• SOC Analyst experience
• cybersecurity job interview

Ashwini Ghugarkar

Related Posts

[2024] Top Common Interview Questions for Entry-Level Positions

[2024] Top Questions for Technical Interviews in IT

Common Cyber Security Interview Questions 2024

Popular posts.

How to Install Red Hat Enterprise Linux (RHEL) 9 ? RHEL...

Aayushi   May 18, 2022  12196

Get 50% Discount on Azure Certification Exam Voucher AZ...

Aayushi   Oct 15, 2022  9626

50% Discount on CKA, CKAD and CKS Certification 2023 |...

Aayushi   Oct 11, 2022  9451

What is Linux Operating System and its Evolution and Fu...

Aayushi   May 3, 2020  7520

50% Discount on Cisco( New CCNA & CCNP) Certification f...

Aayushi   Feb 25, 2021  7133

Know Everything about RHCSA (Red Hat Certified System A...

Aayushi   Sep 15, 2022  1496

Red Hat Remote Individual Certification Exams of RHCSA,...

Aayushi   May 22, 2020  2676

Why is Certified Ethical Hacker (CEH v12) So Popular Ce...

Aayushi   May 21, 2020  3409

What is kubernetes and Containers? Why is So Popular?

Aayushi   May 15, 2020  2099

• Networking (5)
• Security (147)
• Interview Q & A (254)
• Python Interview Q & A (13)
• Common Interview Q & A (17)
• Cloud Admin Interview Q & A (39)
• Linux System Admin Interview Q & A (14)
• Networking Interview Q & A (1)
• Penetration Testing Interview Q & A (0)
• WAPT Interview Q & A (0)
• VAPT Interview Q & A (50)
• Ethical Hacking Interview Q & A (77)
• Study Material (2)
• IT Exams (40)
• Red Hat Certification (6)
• AWS Certification (1)
• Cyber Security Certification (3)

Random Posts

Cyber Security Course for Non Technical

SOC Analyst Interview Preparation Tips 2024

[2024] Top VAPT Ethical Hacking Questions

[2024] Top VAPT Tool Proficiency Interview Questions

How Continuous Cybersecurity Training Can Keep Your Skills Up-to-Date ...

• Cybersecurity interview questions
• RHCE interview questions
• cloud-native applications
• IPv4 and IPv6 addressing
• how artificial intelligence is used in cybersecurity
• strengths and weaknesses interview questions
• contribution to society
• advanced Python interview questions
• VAPT career
• cost management strategies
• OpenShift routing
• AWS Elastic Beanstalk
• IP addressing concepts CCNA
• cybersecurity compliance
• web application security

Blog The Education Hub

https://educationhub.blog.gov.uk/2024/08/20/gcse-results-day-2024-number-grading-system/

GCSE results day 2024: Everything you need to know including the number grading system

Thousands of students across the country will soon be finding out their GCSE results and thinking about the next steps in their education.   

Here we explain everything you need to know about the big day, from when results day is, to the current 9-1 grading scale, to what your options are if your results aren’t what you’re expecting.  

When is GCSE results day 2024?  

GCSE results day will be taking place on Thursday the 22 August.     

The results will be made available to schools on Wednesday and available to pick up from your school by 8am on Thursday morning.  

Schools will issue their own instructions on how and when to collect your results.   

When did we change to a number grading scale?  

The shift to the numerical grading system was introduced in England in 2017 firstly in English language, English literature, and maths.  

By 2020 all subjects were shifted to number grades. This means anyone with GCSE results from 2017-2020 will have a combination of both letters and numbers.  

The numerical grading system was to signal more challenging GCSEs and to better differentiate between students’ abilities - particularly at higher grades between the A *-C grades. There only used to be 4 grades between A* and C, now with the numerical grading scale there are 6.  

What do the number grades mean?  

The grades are ranked from 1, the lowest, to 9, the highest.  

The grades don’t exactly translate, but the two grading scales meet at three points as illustrated below.  

The bottom of grade 7 is aligned with the bottom of grade A, while the bottom of grade 4 is aligned to the bottom of grade C.    

Meanwhile, the bottom of grade 1 is aligned to the bottom of grade G.  

What to do if your results weren’t what you were expecting?  

If your results weren’t what you were expecting, firstly don’t panic. You have options.  

First things first, speak to your school or college – they could be flexible on entry requirements if you’ve just missed your grades.   

They’ll also be able to give you the best tailored advice on whether re-sitting while studying for your next qualifications is a possibility.   

If you’re really unhappy with your results you can enter to resit all GCSE subjects in summer 2025. You can also take autumn exams in GCSE English language and maths.  

Speak to your sixth form or college to decide when it’s the best time for you to resit a GCSE exam.  

Look for other courses with different grade requirements     

Entry requirements vary depending on the college and course. Ask your school for advice, and call your college or another one in your area to see if there’s a space on a course you’re interested in.    

Consider an apprenticeship    

Apprenticeships combine a practical training job with study too. They’re open to you if you’re 16 or over, living in England, and not in full time education.  

As an apprentice you’ll be a paid employee, have the opportunity to work alongside experienced staff, gain job-specific skills, and get time set aside for training and study related to your role.   

You can find out more about how to apply here .  

Talk to a National Careers Service (NCS) adviser    

The National Career Service is a free resource that can help you with your career planning. Give them a call to discuss potential routes into higher education, further education, or the workplace.   

Whatever your results, if you want to find out more about all your education and training options, as well as get practical advice about your exam results, visit the  National Careers Service page  and Skills for Careers to explore your study and work choices.   

You may also be interested in:

• Results day 2024: What's next after picking up your A level, T level and VTQ results?
• When is results day 2024? GCSEs, A levels, T Levels and VTQs

Tags: GCSE grade equivalent , gcse number grades , GCSE results , gcse results day 2024 , gsce grades old and new , new gcse grades

Sharing and comments

Share this page, related content and links, about the education hub.

The Education Hub is a site for parents, pupils, education professionals and the media that captures all you need to know about the education system. You’ll find accessible, straightforward information on popular topics, Q&As, interviews, case studies, and more.

Please note that for media enquiries, journalists should call our central Newsdesk on 020 7783 8300. This media-only line operates from Monday to Friday, 8am to 7pm. Outside of these hours the number will divert to the duty media officer.

Members of the public should call our general enquiries line on 0370 000 2288.

Sign up and manage updates

Follow us on social media, search by date.

Comments and moderation policy