MS Excel Most Important 50+ Interview Question and Answer

In today’s article, we have brought very useful Microsoft Excel Interview Questions and Answers for you, which if you read well, then your knowledge will increase, so let’s start today’s article.
And yes don’t read alone please share with your friends. Share now MS Excel Most Important 50 Interview Question and Answer.

What is Microsoft Excel?

Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns.

It also provides the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.

What are cells?

The area which falls at the intersection of a column and a row where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.

What is ribbon?

Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.

Explain what is a spreadsheet?

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.

What do you mean by cell address?

The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.

Can you add cells?

Yes, you can insert new cells into a sheet. To add a cell, row or column in Excel, right click the cell you want to add to and after that select insert from the cell menu. The insert menu makes you able to add a cell, a column or a row and to shift the cells affected by the additional cell right or down.

Can you format MS Excel cells? If yes, then how?

Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands present in the Font group of the Home tab. When you open the Font window, you will see the following options:

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

Specify the order of operations used for evaluating formulas in Excel.

The order of operations in Microsoft Excel is same as in standard mathematics. It’s defined by the term “PEMDAS” or “BEDMAS”.

• Parentheses or Brackets
• Exponent
• Multiplication
• Division
• Addition
• Subtraction

What are charts in MS-Excel?

Charts are used to enable graphical representation of the data in Excel. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab’s Chart group.

How many data formats are available in Excel? Name some of them.

Eleven data formats are available in Microsoft Excel for data Storage. Example:

• Number – Stores data as a number
• Currency – Stores data in the form of currency
• Date – Data is stored as dates
• Percentage – Stores numbers as a percentage
• Text Formats – Stores data as string of texts

Can you protect workbooks in Excel?

Yes, workbooks can be protected. Excel provides three options for this:

1. Passwords can be set to open Workbooks.
2. You can protect sheets from being added, deleted, hidden or unhidden.
3. Protecting window sizes or positions from being changed.

What do you understand by Relative Cell Addresses?

Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.

What are macros?

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.

Can you add comments to a cell?

Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New Comment option. These comments will be visible to all those people who have access to the Excel sheet.

Which are the two macro languages in MS-Excel?

XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now.

How can you resize the column?

To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want.

The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.

What is Freeze Panes in MS-Excel?

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.

What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?

COUNT is used to count cells containing numbers, dates, etc. any value stored as 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 a certain criteria.

What are the several report formats in Excel?

There are three report formats in Excel:

• Compact
• Report
• Tabular

Is it possible to make Pivot table using multiple sources of data?

If the multiple sources are different worksheets from the same workbook, then you can use these multiple sources of data to make Pivot table.

How can you check whether the Pivot table is modified or not?

To check whether the Pivot table is modified or not, you should use the “PivotTableUpdate” in worksheet containing the pivot table.

What is the quick way to return to a particular area of a worksheet?

The quick way to return to a specific area of the worksheet is by using name box. You can type the cell address or range name in name box to return to a specific area of a worksheet.

Can you define a function on MS Excel?

A function may be defined as something which furthers the motive of an operation. For instance, the most common examples of function include the likes of SUM, AVERAGE and VLOOKUP. The numbers put into a function for the respective operation are basically known as arguments. The result is known as the output.

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

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 which is used to look up 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.

Difference between FUNCTION and FORMULA in excel?

FORMULA – is a statement which written by the user for calculations. Ex: =1+2+3

FUNCTION – is a built-in formula by Excel. Ex: =SUM(1+2+3)

What are the advantages of using formula in Excel sheet?

Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.

What does the IF function in Excel?

IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.

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

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

Can we create shortcuts to Excel functions?

Yes. ‘Quick Access Toolbar’ above the home button can be customized to display most frequently used shortcuts.

What is conditional formatting?

Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value in less than 30 with a red color, you can do that with Conditional Formatting.

Explain pivot tables and its uses.

A pivot table is a tool that allows for quick summarization of large data. It automatically performs a sort, count, total or average of the data stored in the spreadsheet and displays result in another spreadsheet. It saves a lot of time. Allows to link external data sources to our Excel.

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

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

How would you provide a Dynamic range in “Data Source” of Pivot Tables?

To provide a dynamic range in “Data Source” of Pivot tables, first, create a named range using offset function and base the pivot table using a named range created in the first step.

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

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

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

What is the use of LOOKUP function in Excel?

In Microsoft Excel, the LOOKUP function returns a value from a range or an array.

How is Automatic Sort prevented in Pivot Table?

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.

Can you use VLOOKUP for multiple tables?

Yes, you can use VLOOKUP for multiple tables as well. In case you have two lookup tables, create named ranges for each table, and then use the IF function to select between each table based on some given condition.

What is VLOOKUP in Excel?

VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP stands for Vertical and to use this function, data should be arranged vertically. VLOOKUP is very useful when you have to find some piece of data from a huge amount of data.

What is the Absolute Cell Address?

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.

How cell reference is useful in the calculation?

In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct Excel the specific location of that data.

This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.

How do we wrap a text in Microsoft Excel?

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.

How to use advanced filters?

We can use Advanced filters to extract the unique list of items or we can extract the specific item from different worksheets.

Is It Possible To Display The Text In The Data Area Of Pivot Table?

No its not possible to dislplay the text in data area of pivot table.You could display the text fields in the Row and Column areas, and show a count of the records in the data area or we can combine Custom Number Formats with the Pivot Table to produce Text based answers.

How is the new Excel worksheet added?

In order to add a new Excel worksheet, users can click on insert worksheet tab, which is visible at the bottom of the screen.

State the purpose of “Name Box” in MS-Excel?

In MS-Excel, “Name Box” is used for returning to an area of the worksheet, by means of typing the range name or cell address in the name box particular area can be found easily within a fraction of seconds.

What is the role of AND function in Excel?

AND function also performs the logical function like IF function. To know whether the stated output is true or false. The AND function also evaluates the expression located in another cell in the spreadsheet.

Can we merge cells in excel?

Yes, we can merge cells in MS-Excel, to do so, first of all, you need to select the cells that you wish to merge then in-home tab click on the ‘Merge and Centre’ option from the Alignment group.

Why is Format painter Used?

Format painter in excel tool helps you out in copying the format from one item to another one. For example, you have written something in word, and you have formatted according to the style you want using a specific font, color, type. Using format painter you can copy the same format to another section.

What is the use of the ‘WhatIf’ condition?

The “What If” condition is used to modify the data or make changes to data using excel formulas and to give different answers. For instance: you are buying a new car and you would like to know the exact amount of the tax that you will be levied on it, in such situations you can use “What If” function.

Let’s say there are three cells which are A4, B4, and C4. You can fill the first amount, the second cell tells about the percentage of the tax, and the last cell will give you the exact amount of tax.

what is the difference between Pivot charts and regular charts?

While Pivot Charts can update when the pivot table updated, these are not that flexible like regular charts. In regular charts, you have the flexibility to make customizations, but this is not the same case with pivot charts.

There are chances that when you customize a Pivot chart and update it you may lose your customizations. Despite all these limitations, pivot charts are very useful in creating a quick view form the pivot table.

Name some of the errors that you see in Excel?

Below mentioned are some of the common errors in excel:

• #N/A Error: it occurs due to Value Not Available
• #DIV/0! Error: when a number is divided by 0.
• #VALUE! Error: Due to incorrect data type in the formula
• #REF! Error: When reference formula is no longer valid
• #NAME ERROR: it occurs due to misspelled function
• #NUM ERROR: you will encounter this error when you calculate a significant number.

What are the different types of data filter available in Excel?

Date filter, Text Filter and Number Filter are the different types of data filter available in Excel.

Describe volatile functions

When there is a modification performed in the worksheet, make use of volatile function to recalculate the formula repeatedly.

Provide the list of volatile formulas

TODAY(), NOW(), and RAND() are the highly volatile formulas. INDIRECT(), OFFSET(), INFO(), and CELL() are the other volatile formulas.

Why to use comments in Excel?

Comments in Excel are used to describe a formula given in a cell and leave notes for the users for any extra/special information.

When you link to a pivot table cell, a GETPIVOTDATA formula is created. How would you avoid this?

In Excel 2007 or later, by disabling the ‘Generate GetPivotData’ and in previous versions by manually giving the cell reference instead of using the mouse pointer to locate the cell.

Can you change the default summary function for data from COUNT to SUM?

Unfortunately, we can’t change default settings of the data fields. However, by default, if any cell in the selected range is blank or text, it will be default to COUNT else to SUM.