MS Excel Interview Questions

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:

• Basic
• For Intermediate-Level
• For Advanced-Level
• FAQ's
• Tips

So, based on your expert level, you can directly jump into the section

Top 10 Frequently Asked Excel Interview Questions

1. What is Microsoft Excel?
2. What do you mean by cell address?
3. What is the use of the VLOOKUP function?
4. What do you mean by pivot table in excel?
5. What do you mean by macros in excel?
6. What are the different VBA procedures used in excel?
7. How would you create a cell dropdown list in excel?
8. What is the use of freeze panes in excel?
9. What are basic excel interview questions?
10. 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.

If you want to enrich your career and become a professional Business Analyst, then enroll in "Business Analyst Training" - This course will help you to achieve excellence in this domain.

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
• Exponent
• Multiplication
• Division
• Addition
• 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?

• Compact
• Report
• Tabular

10. Name the different data formats available in excel.

• Number
• Currency
• Date
• Percentage
• 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.

Related Article: Creating a PowerPivot Data Model in Excel 2013

12. List the crucial features of pivot tables. 

• Grouping
• 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
• AND
• FALSE
• IFERROR
• IFNA
• NOT
• OR
• TRUE

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.

Related Article: Creating a Power View Report in Excel

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.

Related Article: Import Excel into MySQL

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?

• #DIV/0
• #NAME?
• #N/A
• #NUM!
• #NULL!
• #VALUE!
• #REF!
• ####
• 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?

Functions	Subroutine
They return values after performing a task	They don’t usually return a value after performing a task
They are used as formulas	They are not directly used as formulas
They perform repetitive tasks	Every time we need to insert inputs in the cells

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?

• Aggregate
• Sum
• Count
• IF
• VLOOKUP

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?

• SUM
• AVERAGE
• SUMIFS
• IF
• COUNT
• COUNTIFS
• VLOOKUP.

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.

Conclusion

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