Top 40+ Excel Interview Questions and Answers

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-
 

“Function AddNumbers(a As Integer, b As Integer) As Integer      AddNumbers = a + b  End Function  "

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-
 

"Sub CreateBarChart()      Dim chart As Chart      Set chart = Charts.Add      chart.ChartType = xlBarClustered      chart.SetSourceData Source:=Range("A1:B10")  End Sub  "

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-
 

“Function RectangleArea(length As Double, width As Double) As Double      RectangleArea = length * width  End Function  "

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.