Excel Interview Questions for Beginners and Advanced

Excel interview questions and answers for Beginners

1. Explain MS Excel briefly.

MS Excel is a spreadsheet that allows storage of data in the form of a table. It was developed by Microsoft in 1985 and can be operated on any operating system like Windows, MAC OS, or Android. It has built-in functions like SUM, DATE, etc that makes calculation easier. It allows eady data validation and allows data analysis in the form of pie charts or bar graphs.

This question forms one of the most asked MS Excel interview questions by an interviewer.

2. What are cells in MS Excel?

Cells are areas that are formed by the intersection of a row and a column in MS Excel. Cells are specifically made to store and display information. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet. 

This is one of the very basic MS Excel interview questions for beginners that should be answered comfortably.

3. What is a cell address?

A cell address is used to find a particular cell on the spreadsheet. It is denoted by a combination of the respective column letter and a row number. For example-

4. What is the difference between Relative cell referencing and Absolute cell referencing?

Relative Cell Referencing	Absolute Cell Referencing
i) In this type of referencing, there is a change when copying from one cell to another with respect to the cell address.	i) There is no change when a cell is copied irrespective of its cell address.
ii) It is present by default.	ii) It has to be added by a dollar sign before and after the column and row address.
iii)	iii)

These types of MS Excel interview questions can be better explained with the help of an example that draws attention of the interviewer. 

Nourish your I.T skills by reading our blog on “Top I.T skills that are in demand in 2022.”

5. How can you add cells?

To add a new cell in Excel, select the cell where you want to insert it and then select the ‘Insert’ option. This can be better understood with the help of an example-

6. How can you format MS Excel cells?

In order to format MS Excel cells, you can use the commands present in the Font group of the Home tab. Some of them are- Name, Alignment, Font, Border, Fill and Protection.

These are non-technical MS Excel interview questions which do not require any example.

7. What is the difference between a Formula and a function in MS Excel?

Formula	Function
i) Formula is like an equation in Excel. It can be any type of calculation.	i) Function is a pre-defined calculation which is already built in the excel.
ii) It is time-consuming because of manual typing.	ii) It is less time-consuming and more comfortable while working with functions.

8. How to add comments in excel?

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.

In these types of MS Excel interview questions, you may or may not illustrate an example. 

9. What is Ribbon?

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 and consists of many tabs such as File, Home, View, Insert, etc.

This is one of the most frequently asked Excel interview questions for freshers. 

10. How do you freeze panes in excel?

To freeze panes in MS Excel, follow these steps-

Select the rows and columns you wish to freeze.

Select Freeze pane in the View tab.

Select from the three given options- Freeze panes, Freeze Top Row and Freeze First Column.

11. What is the order of operations used while evaluating a Formula in Excel?

The order of operations in Excel is known as PEDMAS and it is in this way-

• Parentheses
• Exponentiation.
• Division/ multiplication
• Addition
• Subtraction

12. What is the difference between Count, Counta and Counblank?

The Count function is often used in Excel and it has two variations- Counta and CountBlank. The Count function counts the number of cells that contain numeric values. 

Counta- It counts the number of cells that contain a form of content. These include string values, characters and numeric values. 

Countblank- It counts the number of blank cells only. 

13. How do you create hyperlink in Excel?

To create a hyperlink in MS Excel, the shortcut key is Ctrl+K. After this, ‘Insert Hyperlink’ dialog box appears. Enter the address and the text to be displayed. 

These types of Excel interview questions are technical-based and expose the technical skills of candidates. 

14. How do you add a note in Excel?

To add a Note, select the cell and right-click on the same. 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. 

15. How do you protect a cell from being copied?

To protect a cell in a sheet from being copied, follow the steps-

i) Select the cells you want to protect.

ii) Open the Font window from the Home tab.

iii) Select Protection from the Protection pane and check the Hidden Box.

iv) Click on the Review tab and select the Protect Sheet option. 

v) Specify a password for security.

16. What are macros?

Macros are a set of actions that allow you to run the operation as many times as you want. If you repeat a task in Excel, you can record a macro to perform the task automatically. 

See the diagram below to answer better in your Excel interview questions.

17. 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 option.
• Give any name of your choice.

Do you know that MS Excel aids Data Science by allowing users to create named ranges? Get more details on Data Science by enrolling in our Data Science certification course now! 

18. How do you create Drop down lists in Excel?

To create dropdown lists, follow the given steps:

• Click on Data tab present in the Ribbon
• From the Data Tools group, click on Data Validation
• Navigate to Settings>Allow>List
• Select the source list array.

19. What is a Pivot Table?

A Pivot table is an interactive way to brief your data. It is a statistical table that condenses data with large amounts of information. It analyzes numerical data and answers unanticipated queries about your data.

This forms one of the most asked questions in Excel interview questions.

20. What are the features of a Pivot table?

A Pivot table allows the display of the data to be analyzed, providing various angles to the data. With a Pivot table, comparison of data can be done quickly with accurate reports. Pivot tables can detect different patterns, and relationships and allow you to focus on important details.

21. How do you create Pivot tables?

To create a Pivot table, follow these steps-

i) Arrange the data in a tabular form

ii) There should be a unique heading for each of the columns in the first row.

iii) The columns should contain only one type of data.

iv) There should not be any blank row or column.

v) The data in the Pivot table should not match with other data in the sheet.

22. What are Pivot charts in Excel?

A Pivot chart adds value and category instead of choosing the chart wizard. It is a visual representation of data which allows you to analyze data using various types of graphs and layouts. It is usually needed for business presentations where there is a need for large amounts of data. 

A Pivot chart in Excel looks like this-

Data Scientists work with data by creating pivot charts in Excel. Read out this interesting blog to find out “What does a Data Scientist exactly do?”

23. What is the difference between VLOOKUP and LOOKUP function?

VLOOKUP	LOOKUP
i) This function lets the user look for a value in the leftmost column of a table and returns the value from left to right.	i) This function allows the user to look for data in a row/column and returns the data in another row/column.
ii) It is not so easy to use.	ii) It is easier and can replace the VLOOKUP function.

24. What is Data Validation? 

Data Validation in excel is a feature that allows you to control the type of data entered in the sheet. It restricts the type of values that a user can enter into a particular cell. Data Validation is present in the Data Tools group on the Data tab.

25. How do you calculate your age from today’s date?

For this, use the YEARFRAC function and put the start and end date. After that press Enter.

26. What are the different Functions available in Excel?

The different Functions available in Excel are as follows-

DAY, DATE, MONTH- Date and Time.

AND, OR, NOT, IF, TRUE, FALSE- Logical

AVERAGE, COUNT, COUNTIF, MAX, MIN- Statistical

SUM, SUMIF, PRODUCT, SIN, COS- Math and Trig

27. How do you calculate the product of two numbers?

Product in Excel can be calculated by the PRODUCT function. Type any two numbers and type “=” PRODUCT and put the two numbers. The result will be displayed as shown below- 

28. How do you calculate the% of two numbers?

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 the Home tab, select % symbol from the numbers group.

See the image below to understand it-

Such types of technical interview questions for Excel skills detect the technical skills of a candidate. If you want to start an I.T career, we can guide you through our blog on “I.T Career pathway.”

29.How do you find the average of numbers?

To find the average of more than one number, Select AVERAGE formula from More Functions.

30. How will you fetch the current date in Excel?

For this, you need to use the TODAY function from the Date and Time option. This function will return the current date in the MS Excel date format.

31. How to add filters in excel?

To add a filter, select a specific cell. Then select Data> Filter. After that,  select the column header arrow and enter the Filter Criteria and press OK.

32. How do you find the sum of two numbers in Excel?

To calculate the sum of two numbers, select SUM function from Math & Trig option.

33. How many report formats are there in excel?

There are three report formats in Excel. They are- Compact report format, Tabular report format and Outline report format. These are available in the Report layout menu of the Excel sheet.

34. How does the IF function work in Excel?

The IF function in Excel performs a logical test. It allows you to make logical comparisons between a value and what you expect. An IF function can have two results- First, if your comparison is True and second, if your comparison is False. 

35. How does the index-match function work in Excel?

This is a combination of two functions- INDEX and MATCH. The INDEX function is used to return an item from a specific position in the list, while the MATCH function is used to return the position of a value to the list. Here’s how an index-match function works in excel-

Data Scientists proactively work to bring data from different sources and analyze them. Check out what are the roles and responsibilities of a Data Scientist and their objectives. 

36. How do you find duplicate values in a table using Conditional Formatting?

To find duplicate values in a table through Conditional Formatting, go to the Home tab and select Conditional Formatting. Then select, Highlight Cells Rules and then select Duplicate Values.

These types of Excel interview questions are better explained through the use of an example.

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

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

38. How can you protect workbooks in excel?

To protect workbooks in excel, you can adopt any of these tree options-

i) By setting a password to open workbooks.

ii) By protecting sheets from being added or deleted.

iii) By protecting Window sizes from changing.

39. What are Relative cell addresses in Excel?

Relative cell addresses in Excel are the default cell references. It is the combination of a column name and a  row number without the dollar sign. Whenever you copy formulas in Excel, the addresses of the cells get modified. This system is called Relative cell address

40. What are the wildcards available in Excel?

In Excel, there are three wildcards that work only with text data.

i) Asterisk (*)- Refers to any number of characters.

ii) Question mark (?)- Represents one single character.

iii) Tilde (~)- Used for identifying any wildcard character in the text.

Learn and grow with our popular Janbask Training e-learning community and forum.

Advanced Excel Interview Questions and Answers

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

The What-if Analysis is the process of changing the values in cells to see how these changes affect the results in Excel. It is a powerful tool for carrying out complex mathematical calculations and data experimentation. There are three kinds of What-if Analysis in Excel. They are- Scenarios, Goal Seek and Data Tables. These take the input values of various data and display the results.

This is one of the most common MS excel interview questions and answers for experienced candidates.

42. What is the difference between a Function and Subroutine in Excel?

Function	Subroutine
i) It is responsible for returning the value of a task.	i) It does not return the value of a task it is performing.
ii) They are used in a fixed way in spreadsheets or formulas.	ii) They are not used directly in spreadsheets or formulas.
iii) They are used to carry out repetitive tasks.	iii) In this case, ussr are required to insert a value before the output.
iv) They are called by a variable.	iv) they can be recalled anywhere.

43. How do you debug a VBA code in excel?

To debug a code, use the F8 key. You can also create  a breakpoint from where you want the termination of execution. 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.

44. Compose a VBA capacity to figure the space of a square shape.

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

In the event that IsMissing(Width) 

Region = Length * Length 

Else 

Region = Length * Width 

End If 

The increased dependence of the organizations on Big Data has led to the increased demand for certified Data Scientists professionals whose salaries remain high due to the gap in demand and supply. Check this blog to know more about a Data Scientist’s salary based on different factors.

45. Explain what are Loops in VBA.

Loops are one of the most powerful programming tools in VBA. They allow users to repeat the same code block until a specific point or condition is attained. In VBA, Loops allow you to go through a set of objects/ values multiple times. 

46. How do you run a macro when you open a workbook in VBA?

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

47. What is ADO?

ADO stands for Activex Data Objects and is used to access data between client and server. This is Microsoft’s universal data-access technology. It can be used to extract any type of data from the source. There are three versions of ADO. They are- 2.1, 2.5 and 2.6.

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

    Else

    Area = Length * Width

    End If

End Function

49. How do you find the last row in VBA?

To find the last row, use the instructions given accordingly:

Sub FindingLastRow()

Dim lastRow As Long

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

MsgBox (lastRow)

End Sub

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

ThisWorkbook	ActiveWorkbook
i) Indicates the name of the workbook from where the code is running.	i) This is the workbook that is presently active from the other open workbooks.