Ms. Excel interview questions

 Most Common Microsoft Excel Interview Questions & Answers

List of the Top MS Excel Interview Questions and Answers. This Tutorial Answers Interview Question on Excel with Examples to Help you Prepare for the Interview:

Excel – a product of Microsoft, is a software that utilizes spreadsheets to arrange formulas, functions, and sort numbers & data in rows and columns. They can be operated mathematically.

Microsoft Excel is a part of the Microsoft Office group of applications. Its features include assisting in programming by VBA application

This tutorial on Excel questions includes its basic and intermediate features that shall help you to crack any interview easily.

Enlisted below is the list of the frequently asked interview questions and answers that you must prepare, learn and practice to have a thorough grip on Microsoft Excel.

Grab them, Guys!!!

Frequently Asked Excel Interview Questions And Answers

Q #1) How can Microsoft Excel be described in short?

Answer: Microsoft Excel is a software or a computer application that can be used as a repository of information in the form of rows and columns. It is available in most operating systems like Mac, Windows, Androids and so on.

Microsoft Excel has the following characteristics:

• Workbooks, worksheets, cells, and rows operation make it user-friendly and also make data verification and validation easier.
• Functions like count, sum, subtotal, date and much more that may be utilized to a large extent.
• Data visualization and its examination can be achieved with tables, filters, graphs and so on.
• Visual Basic Application in Excel extends support to programming.

Q #2) What are cells inside Microsoft Excel?

Answer:

As shown above, a cell can be defined as a portion that comes at the junction of horizontal rows and vertical columns in Excel. The address of each cell can be seen at the top left corner of the application.

In the image posted above, we can see ‘A1’, by default, the first address of the cell is always shown while we open an Excel workbook. It is basically the fusion of the column letter and the row number and is distinctive in nature.

Q #3) Explain the characteristics of a spreadsheet.

Answer: A group of cells is called a spreadsheet or worksheet. Its purpose is to arrange formulas, functions and sort numbers and data in rows and columns. They can be operated mathematically. The number of worksheets in a workbook can be more than one.

As shown below, there are two worksheets (‘Sheet1’ and ‘Added’) at the bottom. We can add, delete, rename, hide, unhide and perform other operations on sheets. By default, the sheets get added as Sheet1, Sheet2. Here, we have renamed Sheet2 as ‘Added’.

Q #4) Can we rearrange cells in Excel?

Answer:

Excel provides us the option of rearranging cells by insertion and deletion in the following ways:

• Shifting cells to the right
• Shifting cells down
• Inserting/Deleting entire row
• Inserting/Deleting entire column
• Shifting cells to left
• Shifting cells up

While we are inserting a row or a column in Excel, we end up shifting the cells, thereby resulting in a rearrangement of cells.

Q #5) How is the formatting of data achieved in MS Excel cells?

Answer: Some of the data formatting ways for cells are in terms of Number, Alignment, Font, Border, Fill and Protection.

Q #6) How to incorporate comments in MS Excel cells?

Answer: Comments can be added to a specific cell by doing a right-click and selecting insert the comment option. The cell which has a comment shall have a red/purple mark on the right upper corner for identification purpose. We can also edit, delete and reply to a comment.

Please note the cell under column L, having a comment added and how it’s different from a cell without a comment.

Q #7) Explain the features of the ribbon in MS Excel.

Answer: The ribbon appears at the top of the application. Users can access most of the common functionalities of Excel using the toolbars and menus that form a part of the ribbon.

The user also has the option of customizing the ribbon. For example, we often add the ‘Developer’ tab on the ribbon. On a need basis, we can also remove or add an option with the help of CTRL+F1.

Q #8) Explain the significance of Freezing Panes in Microsoft Excel.

Answer: Sometimes we need to have the view of headers of the columns and rows even if we scroll to a large extent up or down. In other words, in freeze panes, we are fastening up a row or a column.

Freeze pane is first achieved by selecting the cell than from View and we need to select one of the freeze options.

Q #9) How to enable Protection in MS Excel?

Answer:

Protection is achieved in three forms:

• Protection via password on the opening of the workbook.
• Protection against hide/unhide/add/deletion of worksheets.
• Window sizes/positions are protected from being modified.

Q #10) What is Relative Cell Address?

Answer: The Relative Cell Address is a type of cell reference in Microsoft Excel that is modified and replaced while the Autofill feature is used or while copied.

Q #11) What is the Absolute Cell Address?

Answer: Sometimes there are scenarios when the cell address must remain unchanged while the Autofill feature is used or while copied. This is called an absolute cell address. The ’$’ sign is used to keep the column and row address constant.

Q #12) How to protect cells of a worksheet from being copied?

Answer: We can protect the cells of a worksheet from being copied by navigating the ‘Review’ menu bar => Protect Sheet and then provide the password.

Once we protect the sheet, we can unprotect it by the ‘Unprotect Sheet’ option.

Thus once a cell is protected, the formula behind the cell is hidden.

Q #13) How do we have Named Ranges in Microsoft Excel?

Answer: We can have a named range by selecting a range that we want to name. Then select Formulas from Ribbon => Define Names => Provide the Name.

Q #14) What are Macros?

Answer: A macro is a step or a group of steps that we perform more than once. We can develop a macro for these tedious tasks. Macros are generally coded or recorded by the users

Learn more =>> What are Macros in Excel?

Q #15) Name the types of Report Formats available.

Answer: There are three types of formats available for reports i.e. Tabular, Compact, and Report.

Q #16) What is a Dropdown List in Excel?

Answer: Dropdown list in Excel is created by following the below steps:

Go to Data in the Ribbon => Select Data Validation => Select List from the Allow Dropdown => Add the values you want to add to the list under the Source field.

Output will be:

Q #17) Explain the characteristics of the Pivot Tables.

Answer:

The characteristics of the pivot tables are:

• A comparison of data is trouble-free.
• Presentation of required data that is required for the examination.
• Customized proper reports can be made.
• Various data movements and relationships can be determined.
• Data can be analyzed from different views.
• Operations like sort, sum, and many other mathematical functions.
• Links to other data sources can be added.

Q #18) What do you mean by Pivot Charts?

Answer: The pivot charts are imaged depiction of the pivot table. Pivot tables and Pivot charts are related to each other.

In order to have a pivot chart, we need to choose a cell from the pivot table and then select an option for a Pivot Chart. This is available under the Insert menu in the ribbon. Examples of charts include bar, pie, area and so on.

Q #19) Will it be feasible to have a Pivot Table from more than one table?

Answer: Yes it is possible to have a Pivot Table from more than one tables provided and all the tables must be on the same sheet.

Q #20) Will it be feasible to have a Pivot Table from more than one table in different sheets?

Answer: Yes it is possible to have a Pivot Table from more than one tables provided and all the worksheets must be in the same workbook.

Q #21) How is the Formula Feature helpful in Microsoft Excel?

Answer: Formula is a declaration that calculates the value of a cell or group of cells. Let’s take an example to understand this, =d6*d7*d8*d9 is known as a formula that multiplies the value of cells d6 through d9.

Functions are inbuilt formulas pre-existing in Microsoft Excel. They are responsible for mathematical operations depending on arguments.

The built-in functions of Microsoft Excel are all available under the Formula tab on the ribbon area.

Q #22) How is Automatic Sort prevented in Pivot Table?

Answer: This can be done by navigating to the More Sort Options => right-click on Pivot Tables. Then choose Sort Menu and proceed with More Options, after that we have to uncheck the Sort automatically every time the report is update

#28) Is it possible to reduce the size of an Excel file?

Answer:

Yes, we can reduce the size of an excel file in the following ways:

• By saving the file in the format -.XLSB.
• By getting rid of Pivot tables if not essential.
• By reducing / not adding/ compressing the images.
• By deleting the unutilized sheets and cells.

Q #29) Name the file formats that are used to save a Microsoft Excel file.

Answer: Some of the file formats to save Microsoft Excel files are csv, xlsm, xls, xla, xlb.

Q #30) How is the Average of numbers calculated in Microsoft excel?

Answer: The average of numbers can be calculated using the AVERAGE function. The syntax of Average function is =AVERAGE(J8: J13)

Here, E1 cell calculated the average of numbers in the range from A1: D1.

Q #31) Define VLOOKUP in Excel.

Answer: VLOOKUP is a built-in function of excel. It is utilized to find and get data from a cell range. This is actually called a vertical lookup. As the name suggests, the data has to be organized vertically.

While we are dealing with a large chunk of data, and we need to get hold of certain parts of the data fulfilling certain conditions, then that is the time when VLOOKUP is used.

Further reading =>> How to use VLOOKUP in Excel

Q #32) Explain the operation of VLOOKUP in Microsoft excel.

Answer:

The algorithm of VLOOKUP is as follows:

• Initiates with a lookup value.
• Starts exploring for the value from the leftmost column.
• As its first presence of the lookup value is detected, the search is moved to the right (that is to the row where the value is present).
• It then comes back with the value from the existing column.
• Returns both precise and imprecise value, but the default match is an inexact match.

Syntax of VLOOLUP is, =VLOOKUP (Val, giventable, col_no, [rnge_look]) ,

Where,

• Val is the value to be searched in the first column of the table.
• giventable is the table where the operation is to be performed.
• col_no is the column from which the lookup value is to be recovered.
• [rnge_look] is not a mandatory argument where TRUE (default) means inexact match and FALSE means exact match.

Q #33) How does Visual Basic Application (VBA) make our life easy?

Answer:

VBA has the following advantages:

• Develop customized functions.
• Mundane sets of tasks are automated so that the resources are not wasted.
• Our customized functions can be used by the other team members, thereby saving time and money for them as well.

Q #34) What is a Horizontal Lookup in Microsoft Excel?

Answer: Horizontal Lookup or HLOOKUP looks for a value from the topmost row of the table horizontally and then moves in a downward direction.

Syntax of HLOOKUP is, = HLOOKUP (Val, giventable, row_no, [rnge_look]),

Where,

• Val is the value to be searched in the first row of the table.
• giventable is the row/rows that are sorted in ascending order.
• row_no is the row from which the lookup value is to be recovered.
• [rnge_look] is not a mandatory argument where TRUE (default) means inexact match and FALSE means exact match.

Q #35) How to get the current date in Microsoft Excel?

Answer: We can get the current date by using = TODAY () function.

Q #36) How does the AND function work in Microsoft Excel?

Answer: AND is an inbuilt function that gives TRUE if all the conditions mentioned in the form of parameters are satisfied. The syntax is =AND (G6<=17, U8<59).

Q #37) How do we wrap a text in Microsoft Excel?

Answer: We can wrap a text within a cell by simply selecting the cell, and then clicking on the Wrap Text option which is a part of the Home tab.

 Other  interview questions 

For call center 

For tally.9 

Some basic  interview questions