Top 30 Questions For Excel Interview

1. Explain MS Excel in brief.

Answer: Microsoft Excel is a spreadsheet or a computer application that allows the storage of data in the form of a table. Excel was developed by Microsoft and can be used on various operating systems such as Windows, macOS, IOS, and Android.

Some of the important features of MS Excel are:

• Availability of Graphing tools
• Built-in functions such as SUM, DATE, COUNTIF, etc
• Allows data analysis through tables, charts, filters, etc
• The availability of Visual Basic for Application (VBA)
• Flexible workbook and worksheet operations
• Allows easy data validation

2. Does each cell have a unique address?

Answer: Yes, each cell has a unique address depending on the row and column value of the cell.

3. How would you format a cell? What are the options?

Answer: A cell can be formatted by using the format cell options. There are 6 format cells options:

• Number
• Alignment
• Font
• Border
• Fill
• Protection

4. What are Freeze Panes in MS-Excel?

Answer: Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.

5. Does VLOOKUP look up case-sensitive values?

Answer: No, it is not case-sensitive. The text ‘ram’ and ‘RAM’ is identical for VLOOKUP.

6. How to create cross-tabulation in Excel?

Answer: Use the Pivot Table and select one variable in the Row label and the other variable in the Column label.

7. Mention 2 ways to extract unique values in excel.

Answer: Use the Advanced Filter option (shortcut key: ALT D F A) and ‘Remove Duplicates’ option under the Data tab.

8. Explain the difference between SUBSTITUTE and REPLACE function in MS-Excel?

Answer: The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.

Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])

Example: Let text at A2 be Hello99,Hello99

SUBSTITUTE(A2,”9″,”8″,1) =>Hello89,Hello99

SUBSTITUTE(A2,”9″,”8″,2) =>Hello88,Hello99

SUBSTITUTE(A2,”9″,”9″) =>Hello99,Hello99

The REPLACE function swaps part of the text string with another set of text.

Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)

Example: Let text at A2 be Hello99

REPLACE(A2,5,1,”00″) =>Hello009

9. Difference between COUNT, COUNTA, COUNTIF, and COUNTBLANK in Ms-Excel.

Answer: COUNT is used to count cells containing numbers, dates, etc. any value stored as a number excluding blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching certain criteria.

10. To move to the previous worksheet and next sheet, what keys will you press?

Answer: To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use keys Ctrl + PgDown.

11. Explain workbook protection types in Excel.

Answer: Excel provides three ways to protect a workbook:

Password protection for opening a workbook.Protection for adding, deleting, hiding, and unhiding sheets.Protection from changing size or position of windows.

12.  Explain what a spreadsheet is?

Answer: Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given them. Take a look at the image below:

13. How do you add a Note to a cell?

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

14. What are macros?

Answer: Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. For example, if you have to record the sales of each item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc, and use the same for the future instead of manually calculating it every day.

15. How do you create Named Ranges?

Answer: 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
• Give any name of your choice

16. Explain Pivot tables along with their features?

Answer: Pivot Tables are statistical tables that condense data of those tables that have extensive information. The summary can be based on any field such as sales, averages, sums, etc that the pivot table represents in a simple and intelligent manner.

Features

Some of the features of Excel Pivot Tables are as follows:

• Allow the display of exact data you have to analyze
• Provide various angles to view the data
• Allow you to focus on important details
• Comparison of data is very handy
• Pivot tables can detect different patterns, relationships, data trends, etc.
• They can create instant data
• Accurate reports
• Serve the base for Pivot charts

17.  What are Pivot charts in MS Excel?

Answer: MS Excel charts are data visualization tools that help you visualize data in various ways. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc. For example, take a look at the Pivot table in the image below:

Now, if you wish to create a pivot chart for this table, select any cell from the table and then from the Insert tab, choose the Pivot Chart option. You will see the following options:

Choose any chart of your preference and click on OK. You can also format these charts respectively.

18.How many rows and columns are there in Microsoft Excel 2003 and later versions?

Answer: Here is the answer, Refer to the table below for the number of rows, columns, and cells for Microsoft Excel 2003 & later version:   

19. What is the Syntax of VLOOKUP?

Answer: VLoOKUP Syntax:=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) 

20. How many report formats are available in Excel and what are their names?

Answer: In Excel, we have three formats available:

• Compact
• Report
• Tabular

21. What is the central order of operations used to make calculations on MS Excel?

Answer: The fundamental order of operation used in Excel is the well known PEMDAS. It stands for: parentheses, exponents, multiplication, division, addition, subtraction. It is the universal order of operations used by Excel. If anomaly whatsoever is reported check the alignment of the above order to spot if any parameter has been left missing.

22. Which are the functions do you think you can essentially count on?

Answer: While functions must be selected in accordance with the color of the problem, possibly the most preferable functions include the likes of INDEX MATCH, PIVOT TABLES, and IF statements. For instance, the INDEX MATCH is a viable tool that is used to lookup values from a table. On the other hand, PIVOT TABLES should always be the professional’s pick in that they allow you to get through complex data and come up with accurate results.

23. Can you give an idea as to how a user can drag columns to the sections of the Pivot Table?

Answer: So far as sections are concerned, the pivot table feature has a number of such sections in order for the user to drag large columns of data to facilitate simpler calculations. They include:

• Report Filter: As the name suggests, the purpose of this is to filter a table parameter one at a time in order to consolidate a certain degree of lucidity.

• Column Labels: Chiefly, this section is used to label the various columns in order to summarize the data into segregated outlooks.

• Row Labels: Like the column label, the row label is concerned with labeling the various rows in order to simplify the given data.

• Values: Basically, this criterion is used to specify or name the data column or data row. For instance, the cost of items, date of delivery, etc.

24. What does a red triangle at the top right of a cell indicate?

Answer: The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.

25. What is the use of NameBox in MS-Excel?

Answer: Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the name box.

26. Can you give us an idea of how one can quickly return to a specified area in the worksheet?

Answer: By using a name box, one can easily return to the required area in a worksheet. All you need to do is type the address of the cell in the name box.

27. What will you do if you want to change either the column letter or the row number but not both?

Answer: To do this, you must make use of Mixed Cell Addresses where either the row or column is relative while the other is absolute.

EXAMPLE:

Take a look at the image below where the columns hold relative referencing while the rows are absolute. Therefore, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original formula and hence the result.

28. Can you protect cells of a sheet from being copied?

Answer: Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given steps:

1. Select the cells that you want to protect
2. Open up the Font window from the Home tab
3. From the Protection pane, select Protection and then check the Hidden box
4. Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required cells unless you do this)
5. Specify a password (This will help you in unprotecting the sheet later)

29.  What filter will you use, if you want more than two conditions or if you want to analyze the list using database function?

Answer: You will use the Advanced Criteria Filter, to analyze the list or if more than two conditions should be tested.

30. Can you give us the uses of the VLookUp function?

Answer: As the name suggests, the VLookUp does a quick vertical search for a particular value in the first column of the table. Next, the function returns the searched value in the same row in the index position.