MS Excel Interview Questions and Answers

This article contains a few questions and answers that might widely help the candidate to clear an interview. Following are the questions which are essential for everyday excel users to know.

1. What is cell range in Excel?

A group of cells selected together in a workbook is called cell range. Here, total of 10 rows and 4 columns are selected together and this selection of cells together is called the cell range.

2. Where does the fill handle appear in MS Excel?

The fill handle appears at the bottom right corner of the selected cell in the excel worksheet. It actually auto-fills the cells according to the series and the advantage is that we can add user-defined lists to automate the fillings in the cell.

3. What is the function of ‘sheets’?

The user uses the function of the sheet to calculate the total number of worksheets in that particularly referred workbook. When there are no arguments provided then it results in the total number of worksheets in that workbook including the hidden worksheets.

4. What are Absolute References and relative references in macro?

• Absolute References:

An absolute reference macro in excel will perform the same steps that are recorded. By default, the macros are recorded in an absolute reference type. All the steps from start to end are noted down in the absolute reference macro.

• Relative References:

Relative reference in excel is a type of function in macros. A lot of data can be arranged in a format using absolute references. To enable the relative reference macro, click on the macros under the view tab and go to “Use relative references”, then the user can start the macro recording. Relative references in the macro can perform the tasks at different parts on the worksheet.

5. How to unhide a personal macro workbook?

To Unhide a personal Macro workbook:

Choose the unhide option from the ribbon under the view tab. The user can unhide the worksheet only when the sheets are hidden. After unhiding, the user can run the macro.

6. What are the ways to view excel macro code?

The ways to view excel macro code are:

• Developer tab on the ribbon

When the macro is enabled, the user can view the macro under the developer tab and it leads to the vba code editor screen.

• Visual Basic Icon

The user can click on the visual basic icon under the developer tab to view the macro.

• Macro Dialog Box

The user can also open the code editor by selecting the macros under the view tab. Click the view macros and select the respective macro.

7. What are Macro Viruses?

Macros also can contain viruses. There’s a possible threat of the existence of a potential virus written as a macro and it’s hidden in excel. These varieties of macros cause a sequence of actions to start mechanically once the appliance is opened. Therefore, excel macros are terribly dangerous and it may infect the system.The best way to secure the system from macro viruses isn’t to run them.

8. What are the differences between subtotal and sumif function?

Subtotal function works on the filtered table data and sum if function executes on an ordinary excel sheet. The sumif function advantage is that it works on an excel sheet satisfying the defined criteria. The subtotal function computes the column data value when the filter is applied whereas the sumif function computes the column data value even without the filter being applied on the sheet.

9. What is an Approximate and exact Match in Vlookup?

• Approximate Match:

In an approximate match, the user is going to make an approximate match, make sure the table is in ascending order. Even when the lookup value doesn’t match the table lookup value, the approximate match works. If the lookup value is the middle of any two values, then it fetches the value with the smallest range among it. The approximate match is also known as the closest match as the function fetches the closest value to the lookup value.

• Exact Match:

In the exact match, the lookup value the user enters is searched in the table and it returns the value only when there is an exact match. The 0 in the formulae represents the exact match from the table.

10. How to start a macro?

To start a macro:

Choose the record macro option under the developer tab. A box appears when the user clicks on the record macro. Fill the box as required and press the ok button. This is how the user has to start a macro. Once the user presses ok, the macro recording gets started.

From then, each and every move is recorded. After recording every step in the sheet, it is easy to access in other sheets also.

11. How can a user change the chart type?

To change the type of the chart, the user has to select the chart under the design tab and click on the change the chart type option. Under all charts, choose the required chart from the pane and press the ok button.

12. When to use OR Function and how to open OR function in Excel?

When a user wants to check multiple conditions in a defined cell, they can make use of the OR function in excel. To execute OR function in Excel, choose the logical icon from the ribbon under formulas tab. The list of functions appears in the dropdown menu. Pick the OR function from the list.

13. How to format the chart axes titles?

Formatting the chart axes titles

To edit the chart or primary and secondary axis, execute the following steps:

1: Click on the add chart element under the design tab. Note: A drop down menu appears.

2: Choose Axes titles from the dropdown menu and select the primary vertical option from it.

3: Now, the user can edit the primary vertical axis title.

14. Why are Macros used in Excel and give an example?

Macros helps the user to complete everyday routine tasks on a single button click. Macro records the routine steps and one click on the macro button can repeat the steps again and again. It greatly saves time on repetitive tasks.

For example: You are a personal assistant and you wanted to design the meeting schedules everyday. This task may consume more time if you are doing it everyday. In these situations, you can switch to using macros. Macros automates the routine tasks. You can use the macro to record the format of the layout of the meeting template.

15. Can we provide absolute reference and relative reference together in a cell?

Yes, the user can add both absolute references and relative references in a cell. It is termed as mixed referencing. From the term, it is very clear that the user can add both absolute and relative referencing in a single cell.

Here, in the B3 cell we have entered the following formula =A2-$B$1 where A2 is a relative reference and $B$1 is an absolute reference in it.

16. How to calculate the nth root in excel?

We can calculate the nth root in excel but there is no direct function to perform the calculation. Hence we have to write the formula in such a way that it finds the nth root of the number.

To fetch the nth root, write the n value as 1/n where n = root number. Here, we have 2 as our n value and we are finding the nth root to the number 10, the answer is 3.162278

17. How can we capture the current date and time in a cell?

We can fetch the exact date and time in a cell by using the NOW function. This function captures the current date and time in a cell.

Syntax: NOW()

Example: We have applied the now() function in the A1 cell and the result appears as 04-08-2021 13:12

18. How does missing data work in sparklines?

While plotting the sparklines, the non numeric data are not taken into consideration.

The #N/A values in the dataset are also neglected while plotting the sparklines.
The sparklines are plotted with gaps when there are missing values in the dataset.

If the cells are hidden, then those values are not considered and if the user wants to plot the hidden cell data, then they should enable the hidden cells in the sheet.

19. List a few Data Types in VBA?

Some of the common data types in VBA are

• String
• Boolean
• Integer
• Double
• Date

20. Is it possible to use concatenation in excel? If so, provide an example?

Yes, there are possibilities to concatenate in excel. The concatenation is generally used to merge the contents between two or more cells in a single cell and usually to concat we either use the “&” symbol which is also known as concatenation operator or the concatenate function in excel.

Syntax: CONCATENATE(Cell1, Cell2…)

Example: In the below sample we have concatenate the contents from A1 and B1 cell in C1 cell using the concatenate() function.

21. Is there a possibility to clear the formatting without removing the cell content?

Yes, we can clear the cell formatting without removing the contents of the cells. To do so, click on the cell and then go to clear options on the ribbon under the home tab. Then choose the required clear option but here we wanted to clear the format alone, hence click on the clear format option from the drop down menu.

22. Name a few errors and list the reason behind those errors?

While working in excel, we may come across errors such as:

a. #N/A Error

We come through this error mostly when the lookup formula couldn’t fetch the value due to various reasons such as providing wrong references, when the spellings are misspelled etc. This error is called a value not available error.

b. #Div/0! Error

This error occurs when a number is divided by zero and it is called division error.

c. #Value! Error

The value error occurs when the data type is incorrect in the formula.

d. #Ref! Error

The reference error occurs when the reference is no longer available.

e. #Num Error

This type of error occurs when we try to calculate a very huge number and this error is known as number error.

f. #Name Error

The name error occurs when we misspell the function.

23. Can you change the order of precedence in excel?

We can change the order of precedence in excel and to do that we have to place those values between the brackets. If we provide the values in the brackets then those values will be prioritized and calculated first.

Example: =100/(5+5)

Explanation: If the (5+5) was out of parentheses then the calculation would have been carried out differently, the 100 would be divided by 5 and then a 5 will be added with it but whereas now, we have placed it in the brackets. Hence that specific calculation takes place first and then it proceeds with the order of precedence.

24. Should the user manually enable the developer tab if so, how to do it?

In excel, if the user is looking for the developer tab and if it is not available then it must be probably the first time they are looking for it. If it’s not found beside the view tab then they should enable it manually. Once it’s enabled, it will be available until they remove it personally. To enable the developer tab in excel, choose options under the file tab, select the customize ribbon label and check mark on the box beside the developer option and finally click on the “OK” button.

25. Define Range Offset property and its syntax?

The user uses the range offset property to choose the rows or column away from its original position. The cells are selected according to the range declaration.

Syntax:

Range(“cell”).Offset(Rowoffset:=2, Columnoffset:=2) = “DataFlair Websites!”

26. Is there any possibility to change the summary calculations in the pivot table?

Yes, the user can change the summary calculations as per their requirement. To make changes in summary calculations, we have to right click on the pivot table and should click on the “value field settings” options. From there we can select the functions as per the requirement.

27. How to add comments to the cell?

To add a comment to the cell, right click on the cell and select the insert comment option. The comment box opens up.

Once it is opened, you can add the required comment in it. After adding the comments, when you go back again to the same cell, the comment also appears near the cell.

Uses of adding a comment:

If the file is shared with more than one person, then the comment box plays a vital role as it indicates the use of the cell and what value it holds etc.

28. What are rules to be followed while naming the functions?

Some of the rules while naming the functions are:

• An alphabet or an underscore should be the first character.
• Special characters and numerics such as 1, !, @, &, ., # should be generally avoided.
• Do not use a reserved keyword as the function name.
• There should not be any space between the function name.

29. Are there any possibilities to create a dynamic table fetching data from different worksheets?

Yes, we can create a dynamic table for the datasets in the different worksheets but the worksheets should be in the same workbook. We can create the dynamic table which is also known as pivot table for the datasets from different sheets but we have to specify the tables and select the data tables from the respective sheet to merge with the calculations.

30. How is Wireframe 3-D Surface chart useful?

The wireframe 3-D surface graphs represent the relationships between huge datasets. These types of charts are generally made up of lines rather than being filled with colors in between.

Wireframe 3-D Surface chart is useful:

• When the user wants to indicate the trends in values in a continuous curve.
• When the data series are of numeric values and this is widely helpful to visualise the data when the curves in the graph are behind the axes.

Summary:

This article would help the candidate to get strong at intermediate and advanced level questions in Microsoft Excel. Hope this helps the candidate to crack the interviews.