MS Excel Interview Questions

Microsoft Excel interview questions will be technical in nature as well as they offer an opportunity for us to speak about our experience and skill level with the business resources as well. The following are some of the typical Excel questions that we might receive in an interview round respectively:

1) What do you mean by Microsoft Excel?

Microsoft Excel is basically a software programs which are effectively included in the Microsoft Office suite, and can be used for the purpose of creating spreadsheets, which are documents in which data is laid out in the form of rows as well as the columns.

2) What do is meant by the cell address in Microsoft Excel?

In Microsoft Excel, a cell address is used for the purpose of identifying out a particular cell on a given worksheet, and a combination of the respective column letter as well as the row number usually denotes it.

3) List out the order of operations that are extensively used in Microsoft Excel while evaluating formulas?

The order of the operations in Microsoft Excel is basically known as the "PEDMAS". And the orders of precedence which can be used for the purpose of performing an Excel operation are as follows:

• Parentheses
• Exponentiation
• Division/Multiplication
• Addition
• Subtraction

For Example: ((B1*20) +5)/2.

As per the above question first of all the data that are present inside the parentheses will be get operated, and then followed by the exponentiation operation as well. After that, the division or the multiplication operations can be solved, and the obtained result is then added to give the final outcome as well.

4) How is Formula different from the Function in Microsoft Excel?

5) How will we write the formula for the following? - Multiplying the value in cell C1 by 20, add the result by 6, and divide it by 2.

To write out the formula for the above-stated question, we are required to follow the PEDMAS Precedence. And the correct answer is ((C1*20) +6)/2.

Answers like as =C1*20+6/2 and = (C1*20) +6/2 must need to be corrected, and we are required to put parentheses brackets just after the completion of the particular operation.

6) Can we add cells to the given sheet?

Yes, we can easily insert new cells into a sheet, and for the purpose of adding a new cell, simply we will be selecting out the cell where we want to insert it and then will select the Insert option, and then we will be get encountered with the below following window as well:

Afterward, we are about to select the option from the given dialog box and click on the OK button.

7) Is formatting of MS Excel cells is possible? If yes, then how?

Yes the formatting of Microsoft Excel cells is possible and it can be easily formatted in order to format these cells, we are required to make use of the commands that are usually present in the Font group of the Home tab, and when we open the Font window, we will be able to see the following options as well:

7) Can we add comments to a cell in Microsoft Excel?

Yes, the comments can be easily added, and to add comments to a particular cell, we are required to select the cell and will right-click on it, after that we will be selecting the New Comment option, as these comments will only be visible to those who have access to the Microsoft Excel sheet as well.

8) Can we add new rows as well as the new columns to an Excel sheet?

Yes, off course we can easily add new rows as well as the new columns to an Excel sheet. And in order to add new rows as well as columns, we are required to select the place where we intend to add them and will right-click on it. Then, after that we will select the Insert option, from where we can select an entire row or column.

9) What do you mean by Ribbon in Microsoft Excel?

In Microsoft Excel, the respective Ribbon primarily refers to the top area of the application, which usually contains menu items and the toolbars that are efficiently available in MS Excel.

Moreover, the Ribbon can be shown or hidden by using the CTRL+F1. The Ribbon runs on the very top of the application, and it is considered to be the replacement for the toolbars as well as the menus.

10) How many types of data formats are available in Microsoft Excel? Name some of them?

In general there are eleven data formats which are often available in Microsoft Excel for the purpose of data Storage.

Example:

• Number: Stores out the respective amount of data as a number.
• Currency: It is used to store the data in the form of currency
• Date: The data is stored as dates respectively.
• Percentage: It stores the numbers in the form of percentages.
• Text Formats: The text format stores data as strings of text respectively.

11) What do you mean by Macro in Microsoft Excel?

Micro in Excel is extensively used to iterate over a group of tasks, and the respective users can easily create macros for their customized repetitive functions as well as for their instructions in accordance to their requirements. Macros can be either written or recorded, depending on the user requirement and choice.

12) Which are the two macro languages in Microsoft Excel?

XLM, as well as the VBA (Virtual Basic Application), are the two primary macro languages that are used in the Excel sheet. And the Earlier versions of Excel make use of the XLM. And the VBA was introduced in Excel 5 and is mainly used in macro language nowadays.

13) What do we mean by charts in Microsoft Excel?

Charts in Microsoft Excel are usually provided for the purpose of enabling the graphical representation of the respective data in Excel sheet, and moreover a user can make use of any chart type, including the column, bar, line, pie, scatter, etc., and this could be selected by just selecting an option from the Insert tab's Chart group respectively.

14) Explain a few useful functions which are used in Microsoft Excel?

Some of the useful function which is made available in Microsoft Excel to manipulate the data is as follows:

• Mathematical and Financial Functions: SQRT, DEGREE.
• Logical Functions: IF, AND, FALSE, TRUE.
• Date and Time functions: NOW (), DATEVALUE (), WEEKDAY (NOW ()).
• Index Match: VLOOKUP as well as the INDEX MATCH.

15) What does the red triangle at the top right of a cell in Excel indicate?

The red triangle at the top right of the cell indicates that some of the comments are generally associated with the respective cell. Hover the mouse over it, and we can easily read out the entire comment effectively.

16) How can we resize the column in Microsoft Excel?

To resize the column in the Microsoft Excel sheet, we just need to change the width of one column and then drag the boundary to the right side of the column heading until we are not getting the specific width of our choice.

17) Explain pivot tables as well as their uses in Excel.

A pivot table in Microsoft Excel is a tool which basically allows a quick summarization of the extensive data; and it automatically performs various operations such as:

sorting of the data, counting of the data, total, or average of the data that are stored in the spreadsheet and displays out the results in the other spreadsheet.

18) How can you add a Note to a cell in Microsoft Excel?

And for the purpose of adding a Note, we are required to select out the cell and then will right-click on the same; after that, we will select the New Note option and type in any note we wish to.

19) Can we protect workbooks in Microsoft Excel?

Yes, individual workbooks can be easily protected. And the Microsoft Excel provides three options for this as well which are as follows:

• Passwords can be easily set to open Workbooks respectively.
• Easy protection to the sheets from getting added, deleted, etc.
• And also protecting our window sizes or the positions from getting changed.

20) How one can apply a single format to all the sheets in a workbook?

For the purpose of applying out the same format to all the sheets of a given workbook, we need to follow the below given steps very carefully:

• We need to right-click on any sheet in the given workbook.
• After that click on the Select All Sheets option.
• Then format any particular sheets, after performing this we can see that the Format has been applied to all the other sheets.

21) What do we understand by the Relative Cell Addresses?

Whenever we copy formulas in Microsoft Excel, the addresses of the reference cells will be modified automatically to match the position in which the formula is being copied. This is could be achieved by the system that is none other than the Relative Cell Addresses.

22) Can we easily protect out the cells of a sheet from being copied to other cell?

Yes it is possible, and this could be achieved by protecting the required cells or the complete sheet as well. And for this we are required to follow the below mentioned steps as well:

• First of all, we will be selecting the cells which we want to protect it.
• And after that, we will open the Font window from the Home tab
• And from the Protection pane, we are required to select Protection, and then we will be checking out the Hidden box.
• After that, we will click on the Review tab in the Ribbon and then select Protect sheet
• And a last, we will be specifying a password (As this will help us unprotect the sheet later).

23) How do we create Named Ranges in Microsoft Excel?

For the purpose of creating named ranges, we are required to follow the given steps as well:

• First of all, we need to select out the area to which we are intend to give a name.
• And from the Ribbon, we need to select Formulas as well.
• After that, we will be clicking on the Define Name from the respective Defined Names group a well.
• And we are required to give any name of as per our requirements.

24) Can we create Pivot tables by using multiple tables?

Yes, we can quickly create Pivot tables using multiple base tables. And to do this, we need to follow the given steps very carefully:

Step 1: First of all, we will press out the Alt+D, and then after that, we will press P to open up the PivotTable Wizard respectively.

Step 2: We will be again selecting out the "Multiple consolidation ranges" option and will click on Next option, just after that we will be able see another dialog box as depicted below:

Step 3: After that, we need to select the "I will create the page fields' option and click on Next option.

Step 4: And in the next window, we will need to add all the required ranges as depicted

below:

Step 5: Once we finish the above, we can click on the Next option respectively.

Step 6: And in this step we will be specifying out the region where we want to create the table and then will click on the Finish option.

Step 7: At last, we will see the pivot table, which has been created by merging both the tables.

25) What happens when we check the Defer Layout Update options in the PivotTable Fields window?

If in case we are checking this option, then we will not be able to see the dynamic changes while interchanging the table fields. And by default, this particular option is off or unchecked, and all the desired changes will get appear only after when we click on the Update button.

26) How many report formats are available in Microsoft Excel?

Microsoft Excel has three reports formats which are as follows:

• Compact Format.
• Outline Format.
• Tabular Format.

27) What do you mean by Pivot charts in Microsoft Excel?

Microsoft Excel charts are the data visualization tools which primarily help us to visualize data in various ways. And these charts can be of any type, such as Bar, Pie, Area, etc.

• For example: Let us now take a look at the Pivot table in the image below:

Now, if in case we want to create a pivot chart for the above table, we will be selecting out any cell from the given table, and then from the Insert tab, we will choose the Pivot Chart option.

28) What is the What If Analysis in Microsoft Excel?

The "What If Analysis" is the technique that can be used for the purpose of changing one or more formulas which are efficiently present in the cells and to see how it affects the result of those formulas in the given worksheet as well. Basically, the Microsoft Excel provides three basic types of What If Analysis tools:

• Scenarios
• Goal Seek
• Data Tables

Scenarios as well as the Data Tables usually take a set of inputs to check for the possible outcome. And most probably the Scenarios can work with many different variables, but the input values can be at the max 32.

Goal Seek, in contrast to the Scenarios as well as the Data Tables, we can take the outputs and can quickly determine the possible inputs.

29) What are the basic differences between COUNT, COUNTA and COUNTBLANK?

COUNT: In COUNT function, the number of cells with simply numerical values is being counted. And in this the Blank cells, special characters, as well as the ells with string values will not be included in the calculation.

COUNTA: In this the number of cells with any kind of content is being counted. Numeric data, special characters, as well as the string values contained in cells will also be counted. A blank cell will not be taken into the consideration.

COUNTBLANK: It solely counts the number of the available blank cells, as the name would imply.

30) How does the AND function work in Microsoft Excel?

The AND function in Microsoft Excel is used for the purpose of determining whether a given condition is TRUE or False, and this function will return a Boolean TRUE if all the conditions are satisfied.

SYNTAX For Add Function In Excel:

AND (logical1, [logical2] …)

In which,

The logical1 and logical2 are conditions that are ranging from 1-255 which we want to check as well.

EXAMPLE:

31) What do is meant by VLOOKUP in Microsoft Excel?

VLOOKUP is a function in Microsoft Excel that can look up data from a given range. Moreover, V in VLOOKUP stands for Vertical; data should be arranged vertically to use this function.

32) How does the VLOOKUP function work in Microsoft Excel?

The VLOOKUP function in Microsoft Excel is a lookup value which begins to look for the same in the leftmost column. When it finds out the first occurrence of the given lookup value, VLOOKUP moves to the right direction, which means that in the row where the value was found. And it continues until the user specifies the column number and returns the desired value to it as well. This function is primarily used to match exact as well as the approximate lookup values.

Syntax:

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

Here in this,

lookup_value: It gives the value to be looked out for.

table_index: It is basically a range from where the respective data is need to be taken

col_index_num: It is responsible for specifying out the column from which we want to fetch the value as well

range_lookup: It is a logical value, which means: TRUE or FALSE (TRUE will find the closest match; FALSE checks for the exact match)

33) Explain the exact match in Microsoft Excel with an example.

For an exact match, we need to set the range_lookup value as FALSE.

EXAMPLE:

If in a scenario we we want to look for the designation of an employee, then in that case we are required to follow the below given steps:

• We need to select the destination cell as well as their type "=."
• And then we make use of the VLOOKUP
• After that we need to specify the lookup_value along with the other parameters.
• And at last we will be setting the range_lookupvalue to FALSE

The function will be: =VLOOKUP (104, A1: D8, 3, FALSE)

It could be easily seen that the VLOOKUP has returned the employee's designation having 104 as his ID respectively.