Excel Interview Questions and Answers

80+ TOP Excel Interview Questions and Answers

Excel Interview Questions
Excel Interview Questions

Q1) Define Microsoft Excel?
Microsoft Excel is said to be a spreadsheet application or an electronic worksheet that is helpful for storing, analyzing data, manipulating data, and organizing reports.

Q2) Provide the different types of data formats available in Excel?
Accounting, Date, Percentage, Number, and Text are the different data formats available in Excel.

Q3) Define Format Painter
If you want to copy the format of a cell, text, image etc and apply on another text, the Format painter is used.

Q4) Define cells in Excel
The place where we store the data is called a cell.

Q5) 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.

Q6) How will you add comments in Excel?
To add comments in Excel, perform the below actions:

Right-click on the cell
Select “Insert” from the toolbar
Click “Comment”. Comment box appears. You can enter the required information here.

Q7) List out the charts available in MS Excel
Pie, Bar, Scatter, Line are some of the available charts in MS Excel, which is useful to provide graphical representation of a report/analysis.

Q8) Define Ribbon in Excel
A specific area that runs at the top of the application, comprised of toolbar and menu items is called a Ribbon. There are various tabs available in ribbon containing a set of commands to use in the application.

Q9) What is the shortcut key to hide the ribbon in Excel?
Ctrl+F1 is the shortcut key to hide the ribbon in Excel

10) How will you protect a sheet in Excel?
To protect the worksheet in Excel, navigate to Menu bar -> Review -> Protect sheet -> Password. Provide a password to protect the worksheet and avoid copying the data.

11) What is the function used to get the total of columns and rows in Excel?
To get the total of columns and rows in Excel, use the function ‘SUM’.

12) How many report formats are available in Excel?
Report, Compact and Tabular are the formats available in Excel.

13) What is the use of ‘IF’ function in Excel?
To verify whether the conditions are true or false, the function ‘IF’ is used in Excel.

14) Give the advantage of Look Up function in Excel
To return a value for array, you can use the function Look Up

15) What is the shortcut key to delete the blank columns?
To delete the blank columns in Excel, press Ctrl+-.

16) How many rows and columns are present in Microsoft Excel 2013?
There are 1048576 rows and 16384 columns in Microsoft Excel 2013.

17) Provide the syntax for VLookUp
The syntax for VLookUp is given below:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

18) How the errors are highlighted in Excel?
The different errors displayed in Excel are #REF!, #DIV/0!, #NUM, #N/A, #NAME, and #VALUE!.

19) While evaluating formulas in Excel, what is the operations order used?
PEMDAS is the acronym given for the order of operations in Excel.
P – Parenthesis/ Brackets
E – Exponentiation (^)
M – Multiplication
D – Division
A – Addition
S – Subtraction

20) Provide the major functions performed in Excel
The major functions performed in Excel are SUMIF, INDEX/MATCH, VLOOKUP, IFERROR and COUNTIF.

21) In excel, what is the function used to get the length of a string?
Use the function ‘LEN’ to find the text string length.

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

23) 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.

24) Provide the shortcut for find and replace
Ctrl+F is the shortcut key to open the find tab and Ctrl+H is the shortcut to open find and replace tab.

25) How will you open the spellcheck dialog box using a shortcut key?
To open a spell-check dialog box, the shortcut key is F7.

26) To perform auto-sum on the rows and columns, what is the shortcut?
‘ALT=’ is the shortcut to perform auto-sum on the rows and columns.
Get Excel and Advanced Excel Online Training

27) How will you open a new Excel workbook using a shortcut key?
Ctrl+N is the shortcut to open a new Excel workbook.

28) Can you give us the different sections in a Pivot Table?
Filter Area, Columns Area, Values Area, and Rows Area are the sections available in Pivot Table.

29) Define Slicer in Excel
The 2010 version Excel has the feature called Slicer in Pivot Table. With the help of Slicer in Pivot table, users can filter the data while selecting one or more options in slicer box.

30) Who designed the Bullet Chart?
Stephen Few is a dashboard expert who designed Bullet Charts and this chart has been extensively acknowledged as one of the topmost graphical representation to show the performance report.

31) 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.

32) What are the popular methods to transpose a data set in Excel?
Using Transpose function and Paste Special Dialog Box are the two (2) methods to transpose a data set in Excel.

33) Is it possible to remove duplicates in Excel from a data set?
There is an in-built feature in Excel to remove duplicates from a data set. Steps to remove duplicates is given below:

Select Data -> Select ‘Data’ tab -> Click ‘Remove Duplicates’.

34) Provide the two macro languages available in MS Excel
Visual Basic Applications (VBA) and XLM are the two (2) macro languages available in MS Excel.

35) Mention the event used to check the status of a Pivot Table modification
Use the event ‘PivotTableUpdate’ to check the status of a Pivot Table modification in a worksheet.

36) What is the syntax of SUBSTITUTE function in Excel?
Syntax of SUBSTITUTE function in Excel:

‘SUBSTITUTE(text, oldText, newText, [instanceNumber])’

37) What is the syntax of REPLACE function in Excel?
Syntax of REPLACE function in Excel:

REPLACE(oldText, startNumber, NumberCharacters, newText)

38) What are the keys used to move to the previous worksheet in Excel?
The keys Ctrl + PgUp is used to move to the previous worksheet in Excel

39) What are the keys used to move to the next worksheet in Excel?
The keys Ctrl + PgDown is used to move to the previous worksheet in Excel

40) Which filter is used to analyse the list that is employed with database function?
Advanced Criteria Filter is used to analyse the list employed with database function.

41) What is the shortcut key to minimize the workbook?
The keys ‘Ctrl+F9’ is the shortcut key to minimize the workbook.

42) How will you cancel an entry using the shortcut key?
‘Esc’ key is used to cancel the entry in Excel.

43) Will we be able to change the font and color of the multiple sheet tabs?
Yes, we can easily change the font and color of the sheet tabs in Excel.

44) What are the key elements to give a best dashboard?
The key elements such as Minimum distractions, visual presentation of information, easy to communicate, and provide useful data to the business stands out to be the best dashboard.

45) What are the new enhancements available in Excel latest version?
Slicers, Tables, IFERROR, Powerpivot, and Sparklines are the new enhancements available in Excel latest version.

46) Is it possible to close all the open excel files at a time?
Yes, it is possible to close all the open excel files at a time.

47) In Excel, what is Name Manager?
We give a name for a cell or a Range which is called Name Manager. Using the Name manager, Table gets managed.

48) Which symbol is used to lock or fix the reference?
The symbol ‘$’ is used to lock or fix the reference.

49) What is the advantage of Freeze panes in Excel?
If you want to lock a specific column or row, Freeze panes can be used.

50) Do you think we have unique address for each cell?
Yes, we have a unique address for each cell based on the value of the row and column.

51) Define Microsoft Excel?
MS Excel is a spreadsheet developed by Microsoft for macOS, Windows, IOS and Android. This spreadsheet allows users to organize, store, and modify the data by applying various formulas. It gives a clear view of reports by dividing into columns and rows. Excel is open to integrate with any external databases to conduct analysis and to generate reports and so on.

52) What is meant by Ribbon in Excel?
The ribbon is the top place of the excel application, in which you can see the menu icons and toolbars available in Excel application. You can use the CTRL+F1 to show or hide the ribbon. The ribbon contains various tabs, and every tab contains different commands.

53) Explain the “Data formats” available in excel? List a few of them.
We have Eleven data types available in Excel for storing data. Let us list a few of them.

Currency: records data in currency form
Name: data related to numbers are stored
percentage: records numbers as a percentage
Date: stores data as date
Text: It records data in the form of strings

54) List the order of the operations used for evaluating Excel formulas?
The order of operations in excel same as like standard mathematics. This is defined by “BEDMAS” or “PEMDAS”
Brackets or Parentheses
Addition
Division
Exponent
Multiplication
Subtraction

55) what is Macro Excel?
If you are required to perform tasks repeatedly in Microsoft Excel, you have a facility here to automate the tasks using Maco. A macro is defined as a set of actions that you can run based on the repetitions. When you create a macro, it automatically records your keystrokes and mouse clicks.

56) Explain the procedure to wrap text within the cell?
Initially, you need to do is a selection of the text cell that we want to wrap and next click on the wrap text tab in the Home tab. The text is wrapped into the cell.

57) Can we able to prevent people from copying the cell from your worksheet?
Yes, we can prevent others from copying the cells. To do so what you need to do is you need to click on the menu bar >Review > Protect Sheet > Password. Creating a password for your sheet can prevent others from creating a copy of your sheet.

58) Explain what the two macro languages are available in MS Excel?
We have two languages in MS-excel those are VBA (Visual Basic Applications) and XLM. In the beginning, XLM was in usage, but after the introduction of Excel 5 version, VBM has been in usage.

59) Explain the procedure, to sum up the columns and Rows number instantly in an Excel sheet?
It is very simple: you can use simply get the sum of the rows and columns in the excel sheet by using the sum function.

60) Do we have charts in MS-Excel? And what are they used for?
Yes, we do have various charts in Ms-Excel which include a bar, columns, pie scatter etc. We can select the different charts from the insert tabs from the charts group. Charts are useful for creating a graphical representation of excel data.

61) What does it mean by a red triangle on the top right of a cell?
The triangle indicates that there is some comments associated with that particular cell. You can view or read the comment by placing the mouse on it.

62) What is the primary use of comment? How can we add a comment to a cell?
Below is the reasons why comments are used.
Comments are mainly used to specify the need of the cells.
Comments are mainly used to specify a formula used in a cell.
Comments help to write notes about a cell.

To give a comment in an excel sheet, you need to right-click on the cell menu and select cell menu. And then write your comments.

63) Explain the usefulness of the name box in MS-Excel?
The Name box plays a vital role in finding the required cell or range name. To find any cell address or name, enter the elements in the name box.

64) What can you do to add a new worksheet to the excel?
To add a new excel sheet, you need to insert a worksheet tab at the end of the excel sheet.

65) list some useful functions in MS-Excel?
Below is the functions available to modify the data in excel:

Logical Functions – IF, AND, TRUE, FALSE
Math and Financial Functions – DEGREE, SQRT, GCD, RAND()
Index Match – INDEX MATCH and VLOOKUP
Date and Time Functions – DATEVALUE (), NOW(), WEEKDAY(NOW())
Pivot tables

66) 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.

67) How can you resize the column?
To resize a column, the first and foremost thing that you need to do is change the width of one column, and the next step is to drag the boundary to the right side until the size you want have. And you can have another manner to resize the column, i.e., select Format option from the home tab in that you need to click on AUTOFIT COLUMN WIDTH. Once you click on it, the selected cell will be formatted.

68) Explain the three report formats available in Excel?
We have three types of reports which are:
Report
Tabular
Compact

69) 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.

70) What is conditional formatting?
Conditional formatting is an essential feature in Microsoft excel using which you can format to a cell or by selecting the various range of cells based on predefined conditions. Let’s take an example here: You wish to highlight the cells whose value is less than 20 with red colour, then you can do that using conditional formatting.

71) Explain the process to provide Dynamic range In “Data Source”?
To define a dynamic range to the “Data Source” of Pivot tables the first thing you need to do is to create a named range with the help of offset function.

72) Will it be possible to create a Pivot table with the help of various sources?
Yes, we can create a pivot table using multiple data sources, but the sources should be from the single workbook.

73) Name the event that we use to confirm whether a table is modified one or unmodified?
To check the pivot table modification status, we use “PivotTableUpdate” in a worksheet.

74) What do you know about Freeze Panes in Excel?
Freeze pan is a feature in excel to lock a particular column or row. Whatever the column or row you have locked will remain displayed in the same position even if you scroll it horizontally or vertically.

75) List what are the types of workbook protection in Excel?
We have 3 ways to excel in protecting a workbook which are:
Using a password to prevent access for the other users.
Protecting cells for adding, hiding, deleting and unhiding excel sheet.
Prevention from modifying the position or size of windows.

76) How can you stop format losing in a Pivot table?
To stop the format loss in the pivot table can be quickly done by modifying the options present in the Pivot table. You can go to the options that are present in the Pivot Table and then disable the option “AutoFormat” and “Enable Preserve Formatting”.

77) What is the difference between COUNTA, COUNTIF, COUNT, and COUNTBLANK present in excel?
COUNT option in excel is mainly used to count the number of cells that contain dates, numbers, etc. it counts all the cells, which contains numbers except blanks.
COUNTA or COUNT All option is used to cells that contain any value it may be text, numbers, logical values or symbols etc. It counts all types of cells, excluding blanks.
COUNTBLANK is used to know all empty cells or an empty String.
COUNTIF option is used to find the matching cells based on specific criteria.

78) Can we add shortcuts to excel functions?
Yes. Using ‘Quick Access Toolbar’ above the home button can be used to customize the function in excel. You will be displayed with the most frequently used shortcuts.

79) Explain the use of LOOKUP function in Excel?
LOOKUP function in excel is being used to return the values from an array or a range

80) Can we apply the same formatting to every sheet of a workbook in Excel?
Yes we can do the same formatting to every sheet by Right-Clicking on the ‘Worksheet tab’ > and Choose ‘Select All Sheets’ option. This process of formatting can be applied to the entire workbook. If you wish to apply to format to the only specified groups, then only select the sheets that need Formatting.

Frequently Asked Excel Interview Questions