Microsoft Excel Interview Questions and Answers

Ques 1. What is Microsoft Excel?

Microsoft excel is an electronic worksheet developed by Microsoft, to be used for organizing, storing and manipulating.

Ques 2. What is ribbon?

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.

Ques 3. How can I hide or show the ribbon?

By pressing the CTRL key and pressing the F1 key to toggle you can see and hide the ribbon.

Ques 4. How you can sum up the Rows and Column number quickly in the excel sheet?

By using SUM function you can get the total sum of the rows and columns, in an excel worksheet.

Ques 5. How you can add a new excel worksheet?

To add a new Excel worksheet you have to insert worksheet tab at the bottom of the screen.

Ques 6. How you can resize the column in MS Excel?

To resize the column you have to 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.

Ques 7. What is the sequence of operating mathematical operation in Excel?

The order of sequence of operating is BEDMAS

• Brackets
• Exponents
• Division
• Multiplication
• Addition
• Subtraction

Ques 8. What is the benefit of using formula in excel sheet?

Calculating the numbers in excel sheet, not only help you to give the final ‘sum up’ of the number but, it also calculate automatically the number replaced by another number or digit.  Through excel sheet, the complex calculations becomes easy like payroll deduction or averaging the student’s result.

Ques 9. How do I Format data in MS Excel?

1. Must Always highlight the data before formatting 
2. Click Format 
3. Then go to Cells 

Ques 10. How do I resize Columns and Rows to better fit the data in MS Excel?

1. Move the mouse in between any two labels (Rows/numbers or Columns/letters) 
2. The pointer will turn into a vertical (letters) or horizontal (numbers) line with arrows on both ends 
3. Simply drag the column or row to the desired size 

Ques 11. Using Excel is there a way to close all open Excel files at once instead of closing them one at a time?

Yes, you can close down all your Excel files at once by using the following instructions: 
1. Hold down the Shift key. 
2. Choose File + Close All from the menu. Holding down the Shift key changes Excel's File + Close command to a File + Close All command. 

Ques 12. How can you wrap the text within a cell?

You have to select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell.

Ques 13. Is it possible to prevent someone from copying the cell from your worksheet?

Yes, it is possible. In order to protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect sheet > Password.  By entering password, you can secure your worksheet from getting copied by others.

Ques 14. What does a LOOK UP function searches in the MS Excel?

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

Ques 15. What is the AND function does in excel?

Like IF function, AND function also does the logical function. To check whether the output will be true or false the AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet. If you want to see the final result or output of more than one cells in single cell it is possible by using AND function.

Example: If you have two cells, A1 and A2, and the value you put in those two cells are >5 and you want result should display as ‘TRUE’ in cell B1 if value>5,   and ‘False’ if any of those values<5. You can use AND function to do that.

Ques 16. What is the quick way to return to a specific area of a worksheet?

The quick way to return to a specific area of 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.

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

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

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

COUNTBLANK: It counts blank cells or cells with an empty string.

COUNTIF and COUNTIFS: These count cells matching a certain criteria.

Ques 18. What are three report formats that are available in Excel?

• Compact
• Report
• Tabular

Ques 19. How would you provide a Dynamic range in "Data Source" of Pivot Tables in MS Excel?

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.

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

If the multiple sources are different worksheets, from same workbook, then it is possible to make Pivot table using multiple sources of data.

Ques 21. How cell reference is useful in the calculation?

In order to avoid writing the data again and again forcalculating 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.

Ques 22. How you can disable the automating sorting in pivot tables?

To disable the automating sorting in pivot tables,

Go to >  “More Sort Options”> Right Click  “Pivot table” > Select “Sort” menu > Select  “ More Options” >  Deselect the “ Sort automatically when the report is created”.

Ques 23. What is the "What If" condition in excel formulas?

The “What If” condition is used to change the data in Microsoft excel formulas to give different answers.

Example: You are buying a new car and want to calculate the exact amount of tax that will be levied on it then you can use the “What If” function.  For instance there is three cells A4,B4 and C4. First cell says about the amount, second cell will tell about the percentage (7.5%)  of tax and final cell will calculate the exact amount of tax.

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

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

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

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.

Ques 26. How do I put password to protect my entire Spreadsheet so data cannot be changed?

1. Click Tools 
2. Scroll down to Protection, then Protect Sheet 
3. Enter a password, Click OK 
4. Re-enter password, 
Click OK 

Ques 27. What is Freeze Panes and how do I do it?

1. Row - Select the row below where you want the split to appear 
2. Column - Select the column to the right of where you want the split to appear 
3. Go to the Menu Bar 
4. Click Windows
and then click Freeze Panes 

Ques 28. What does a red triangle at the top right of a cell indicates?

A red triangle in the cell indicates the comment associated with the cell.  If you place mouse on it, it will show the comment.

Ques 29. How do I combine different chart types into my Excel spreadsheet?

To combine chart types, follow these steps: 
1. If the Chart toolbar isn't already displayed, right-click any Toolbar and select Chart. 
2. On the chart, click the series you want to change. 
3. On the Chart toolbar, click the arrow next to the Chart Type button and then select the new chart type for the series (in our example, a line chart). 

Ques 30. What is IF function in excel?

To perform the logic test IF function is performed. It checks whether certain conditions is true or false. If the condition is true then it will give result accordingly if the condition is false then the result or out-put will be different.

Example: For example, you select the cell and you want to display that cell as “Greater than five”, when value is true (=5 or 5) and “less than five” when value is false( <5 ). For that by using IF condition you can display result.

=IF (Logical test, value if true, value if false)

=IF (A1>5, “Greater than five, “Less than five”)

Ques 31. Is there a way to apply the same formatting to every sheet in a workbook in Excel?

Yes. To do this, you will need to right click on one of the worksheet tabs and then choose Select All Sheets. After you do this any formatting that you apply or text you enter will show up on all the sheets in your workbook. In order to eliminate certain sheets from the changes, hold down the Ctrl key and click on the tab of the worksheet you want excluded from the others. You can also group sheets by holding the shift key and selecting the worksheet tab. 

Ques 32. How can I identify which cells in my spreadsheet have a formula and which do not in MS Excel?

Option A: 
1. Choose Edit + Go To (or press Ctrl + F5). 
2. Select Special. 
3. Select Formulas. 
4. Click OK. 

Option B: 
1. Choose Tools + Options. 
2. Select the View Tab 
3. In Window Options choose the check box 'Formulas'. 
4. Click OK 

Ques 33. How can I printout the formulas in an Excel spreadsheet - rather than the results?

The trick is to change the way Excel displays the worksheet before you choose to print. Check the box Tools, Options, View, Formulas and you'll see the formulas appear in each cell (with the columns changed to fit). When you print the sheet the formulas will be printed instead of the values.

Ques 34. Is it possible to change the color and font of the sheet tabs?

Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you will get option change color but i didn't find any option to change the font of sheet tabs. 

Ques 35. What you could do to stop the pivot table from loosing the column width upon refreshing?

Format loss in pivot table can be stopped simply by changing the pivot table options.  Under the “Pivot Table Options” turn on the “Enable Preserve Formatting” and disable “ Auto Format” option.

Ques 36. How do I find the first empty cell in column A?

If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If 

Ques 37. Which event do you use to check whether the Pivot Table is modified or not?

To check whether the pivot table is modified or not we use “PivotTableUpdate” in worksheet containing the pivot table.