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 Visual Basic Application (VBA).
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!!!
Table of Contents:
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 updated.
Q #23) Name the different types of Functions in Microsoft Excel.
Answer:
Some of the different categories of Functions include:
- Financial
- Date and Time
- Math and Trig
- Lookup and Reference
- Database
- Text
- Logical
- Information
- Precedence
Q #24) Explain the Operator Precedence of Formulas in Microsoft Excel.
Answer: BODMAS rules are followed in formulas. The term is known as Bracket Order Division Multiplication Addition and Subtraction i.e. if we have a formula that has a bracket and division, then the expression enclosed in the bracket shall be calculated before the division operation.
Q #25) Explain the SUM and SUMIF functions.
Answer: SUM function takes n number of arguments and performs a summation of each one them. It basically sums up all the numbers in the range of cells. For example, =SUM (E1: G1), shall add up the numbers from the range E1 to G1.
SUMIF function is used to perform summation only if a certain condition is met. Thus SUM and SUMIF functions are almost identical except for the presence of criteria in SUMIF. For example, =SUMIF (E1: G1,”<10), shall add up the numbers from the range E1 to G1 which are lesser than 10.
Q #26) Explain the COUNT function.
Answer: There are 5 count functions available in excel.
- COUNT function shall return the total count of cells that have numbers in the range of cells mentioned in the parameter. Syntax is as =COUNT (val1, [val2], …)
- COUNTA function returns the total count of non-empty cells in the range of cells mentioned in the parameter. Syntax is as =COUNTA (val1, [val2], …)
- COUNTIF function returns the total count of cells in the range of cells mentioned in the parameter that satisfies the given condition. The syntax is as =COUNTIF (A5: I5,”<10”), this shall count the number of cells having a value less than 10.
- COUNTBLANK function is the opposite of COUNTA. This function returns the total count of empty cells in the range of cells mentioned in the parameter. The syntax is as =COUNTBLANK (A5: I5).
- COUNTIFS function returns the total count of cells that satisfy certain conditions. These conditions can be logical operators, numbers and so on. The syntax is as =COUNTIFS (F1: F6,”<10”, J2: J10,” black”), where two conditions have been set.
Q #27) Can Percentages be calculated in Excel? If yes, how?
Answer: Yes, we can do percentage calculation in excel. If the score of student A is in A1 cell and full marks for the subject is in A2 cell, then, first we need to select a cell to say A3, where we perform =A1/A2. Then from the Home tab, we need to select %.
Q #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.
Conclusion
We hope all your doubts about the MS Excel Interview Questions are cleared now. We have covered most of the questions that are asked for both beginners and experienced levels.
Stay Positive and practice them in MS Excel to gain more confidence.
Happy Learning! All The Best!