Every employee needs to know to manage a large set of data and perform easy calculations in most of all companies. If you are a data analyst, reporting analyst, or aspiring candidate, the first and foremost tool you need to know is MS Excel. Excel is a software that every employee needs to know to manage a large number of data and perform easy calculations. You must already use Excel at your workplace, so when you want to change the job from one company to another, the interviewer will test your skills and knowledge of MS Excel.
In this article, we have listed out some of the most popular excel interview questions and answers that will help you with your next job interview.
Ready to take your data science skills to the next level? Sign up for a free demo today!
Top 100 Excel Interview Questions And Answers
Here are given top frequently asked MS Excel interview questions and answers that has been asked in many companies. Some of them are listed here.
1. 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. Take a look at the image below:
2. 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:
| Name | Description |
| Number | Allows formatting cells to be of any type such as currency, accounting, date, percentage, etc. |
| Alignment | Allows text control, alignment and setting its direction |
| Font | Enables various fonts, styles, sizes, colors, etc. |
| Border | Allows cell borders to be changed, removed, colored, etc. |
| Fill | Enables you to choose different colors and styles to fill up the cell |
| Protection | Allows you to lock or hide cells |
Looking for a Data science and Machine learning Career? Explore Here!!
3. Can you add new rows and columns to an Excel sheet?
Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.
4. Can you protect workbooks in Excel?
Yes, workbooks can be protected. Excel provides three options for this:
- Passwords can be set to open Workbooks
- You can protect sheets from being added, deleted, hidden or unhidden
- Protecting window sizes or positions from being changed
5. 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.
6. How do you create Pivot Tables?
In order to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the following points in mind while preparing the data:
- Arrange the data into rows and columns
- The first row should contain unique heading for each of the columns
- The columns should have only one type of data
- Rows must have data for a single recording only
- No blank rows
- Columns should not be completely blank
- The data for creating Pivot table should be separate from other data present in the sheet
For example, let’s create a Pivot chart for the table shown in the image below:
To create a Pivot table, select the table and click on the Insert tab. then select Pivot Table command and you will see the following window:
Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty pivot table has been created. Also, PivotTables Fields pane will open that will help you configure the Pivot table. Take a look at the image below where I have created a Pivot Table:
7. Can you create a pivot table using tables from different worksheets?
If both the sheets are from the same workbook, you can create a pivot table for tables from different sheets as well. To create a pivot table from two different sheets, follow the same steps as shown in Q24 and when you specify the tables, go to the respective sheet and select the tables you intend to merge.
Experience the power of our Data Science and ML course with a free demo – enroll now!!
8. What do you understand by Excel functions?
Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate results of various formulas thereby helping in time conservation. Also, these functions make it very easy to execute formulas which would have been difficult to manually write down.
9. Explain SUM and SUMIF functions?
SUM: The SUM function is used to calculate the sum of all the values that are specified as a parameter to it. The syntax of this function is as follows:
SUM(number1, number2, …)
SUMIF: This function is used to calculate the sum of values that comply with a given condition.
SYNTAX:
SUMIF(range, criteria, [sum_range])
where,
- range specifies the range of cells to be evaluated
- criteria provides the condition to be met
- sum_range is optional and provides the actual cells to be summed up
10. How does the VLOOKUP function work?
The VLOOKUP function, in Excel, a lookup value and begins to look for the same in the leftmost column. When it finds the first occurrence of the given lookup value, VLOOKUP starts to move right i.e in the row where the value was found. It goes on until the column number specified by the user and returns the desired value. This function is used to match exact and approximate lookup values. However, the default match is an approximate match.
SYNTAX:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
here,
- lookup_value gives the value to be looked out for
- table_index is the range from where the data is to be taken
- col_index_num specifies the column from which you want to fetch the value
- range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)
11. Name the different data formats available in excel.
- Number
- Currency
- Date
- Percentage
- Text formats
12. What are the various logical functions used in excel?
- IF function
- AND
- FALSE
- IFERROR
- IFNA
- NOT
- OR
- TRUE
13. How would you differentiate a formula from a function?
A formula is an equation we can design to calculate the excel sheet. A function can be a part of a formula. At the same time, a function is the prebuilt code of excel that we can use to make calculations.
14. What is the use of freeze panes in excel?
We can use freeze zones to lock any row or column. Not only that, we can lock a group of rows and columns. Once it is locked, we can view the locked rows and columns even if we scroll the page down and horizontally.
15. How can you protect excel files and workbooks in different ways?
- We can use passwords to open or modify an excel file or workbook
- We can mark a file as final. After that, no one can change the file.
- We can use a digital signature to access a file.
16. Can you name the wildcards in Excel?
There are 3 wildcards in Excel that can be used in formulas.
Asterisk (*) – 0 or more characters. For example, Ex* could mean Excel, Extra, Expertise, etc.
Question mark (?) – Represents any 1 character. For example, R?ain may mean Rain or Ruin.
Tilde (~) – Used to identify a wildcard character (~, *, ?). For example, If you need to find the exact phrase India* in a list. If you use India* as the search string, you may get any word with India at the beginning, followed by different characters (such as Indian, Indiana). If you have to look for India” exclusively, use ~.
Hence, the search string will be india~*. ~ is used to ensure that the spreadsheet reads the following character as is and not as a wildcard.
17. How would you rearrange columns in an excel sheet?
- Locate your cursor on the top of the column that you want to move
- Highlight the column
- Hold the shift button and move the column right or left
- Release the click in the mouse once you move to the location where you want to move the column.
18. How would you convert an excel file into a pdf file?
- Choose the part of the excel sheet that you want to convert into excel
- Click the file ‘menu’ and go to the ‘Export’ option
- Then click create pdf and then click options to make settings of the pdf file
- Click ‘OK’ and name the file.
19. How would you filter data in pivot tables?
- Choose the cell in the pivot table
- Go to PivotTable analyze and choose insert slicer
- Choose the fields and click OK
- Then, arrange the size and position of the slicers on the table
- Choose the items that you wish to show in the pivot table
20. What filter would you use to analyze a list using a database function?
We will use the advanced criteria filter to analyze the list. We can use this filter to test more than two conditions.
21. What shortcut can you use to apply a formula for an entire column?
We can double-click on the cell’s bottom right corner containing the formula. Suppose the formula is in the first cell of the column, we can use the following method.
- Select the complete column by clicking the column header
- Press Ctrl+Space
- Use Ctrl+D to fill the consecutive cells.
22. How can you insert a hyperlink in excel?
First, select the text in a cell for which we want to create a hyperlink. Then, press Ctrl+K. Choose the existing file from the folders or enter the web page link. Press OK to create the hyperlink.
23. How would you highlight cells with errors in it?
- Select the data in which you want to highlight the cells with errors.
- Go to the Home tab and click on Conditional Formatting option.
- Click on ‘New Rule’.
- In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’ option.
- In the formula field, enter =ISERROR(A1), where A1 is the active cell in the selection.
- Click on the Format button and specify the color in which you want to highlight the cells.
- Click OK.
24. What according to you are the top 5 functions in Excel?
- VLOOKUP
- COUNTIF
- SUMIF
- IFERROR
- INDEX / MATCH
25. How would you get rid of leading / trailing / double spaces in a text in Excel?
To get rid of leading, trailing, and double spaces, you need to use the TRIM function.
Looking for a Data science and Machine learning Career? Explore Here!!
26. What is the shortcut for spell check?
F7 – This opens the spell-check dialog box.
27. How can you select all the cells in the worksheet?
You can use CONTROL A A – hold the control key and hit the A key twice.
28. What is a Pivot Cache?
Pivot Cache is something that automatically gets generated when you create a Pivot Table. It is an object that holds a replica of the data source.
29. How can we change the data value function to MAX or MIN in a pivot table?
By right clicking in the field in “Values” section and clicking on “Value Field Settings”, we can change the function MAX/MIN/AVG etc.
30. How can we get the weekday from a day in excel?
WEEKDAY function returns the number ranging from 1 to 7.
31. What is the default value of last parameter of VLOOKUP?
Default value of fourth parameter in VLOOKUP function is 1 or TRUE.
32. What is the restriction on VLOOKUP function?
Lookup value should be the first column in the table array, it always lookup value from right side.
33. How will you remove duplicate values from a column?
Using Remove duplicates option by pressing ALT A, M, A.
34. How to make Pivot table automatically include new record when it is added in the data?
Create a Pivot table by using a dynamic name range, we can define a name range with the help of OFFSET function in excel.
=OFFSET(reference, rows, cols, [height], [width]).
35. How do you change the value field to show some other result other than the Sum?
to change the value field to show results other than the Sum, right-click on the Sum of Amount values and then click on Value Field Settings.
36. How does the AND function work?
The AND function in Excel is used to whether a given condition or a set of conditions is TRUE or not. In case all the conditions are satisfied, this function will return a boolean TRUE.
37. What is the What If Analysis?
What If Analysis is the technique of performing changes to one or more formulas present in the cells in order to see how it affects the result of those formulas in the worksheet. Excel provides three types of What If Analysis tools:
- Scenarios
- Goal Seek
- Data Tables
38. Can you create shortcuts for most frequently used formulas?
Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick Access Toolbar and select the Customize Quick Access Toolbar option.
39. How will you fetch the current date in Excel?
You can make use of the TODAY function. This function will return the current date in the MS Excel date format.
SYNTAX: TODAY()
40. How do you find averages in MS Excel?
Average can be calculated using the AVERAGE function.
SYNTAX: AVERAGE(number1, number2, …)
41. Explain how to calculate compound interest in Excel?
To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an investment based on the periodic, constant interest rate and payments.
SYNTAX: FV(rate, nper, pmt, pv, type)
42. What are the different types of COUNT functions available in Excel?
Excel provides five types of COUNT functions i.e COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
The COUNT returns the total number of cells that have numbers in the range that is specified to it as a parameter.
SYNTAX: COUNT(value1, value2, …)
43. What is the operator precedence of formulas in Excel?
Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are executed first (if they are present) followed by multiplication, division, etc.
44. What do you understand by Excel functions?
Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate results of various formulas thereby helping in time conservation. Also, these functions make it very easy to execute formulas which would have been difficult to manually write down.
45. What happens when you check the Defer Layout Update option present in the PivotTable Fields window?
In case you check this option, you will not see dynamic changes while interchanging the table fields. By default, this option is off or unchecked. All the changes will appear only after you click on the Update button when you check this box.
46. How are PivotTables used to filter data?
Excel PivotTables allow you to filter data according to your requirements. To do this, place the field based on which you wish to filter out the data. Then from the pivot table, open the dropdown list present for the field you have placed in the Filter area and select the section of your choice.
47. How can you wrap the text within a cell?
You must 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.
48. 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.
49. What are charts in MS-Excel?
To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab’s Chart group.
50. To move to the previous worksheet and 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.
51. What is the shortcut for copying the formula from the above row?
Ctrl + .
52. What is the shortcut for selecting the entire column?
Ctrl + Spacebar
53. What does a red triangle at the top right of a cell mean?
The red triangle is a sign that someone has placed a comment placed on the cell. You can hover over the triangle to read the comment.
54. What is conditional formatting?
Conditional Formatting allows you to format a cell based on the value in it.
55. How would you insert a new line in the same cell?
To insert a new line in the same cell, use the shortcut ALT Enter – hold the ALT key and press enter.
56. What are slicers?
Slicers enables you to filter the data when you select one or more than one options in the Slicer box.
57. What is a Pivot Cache?
Pivot Cache is something that automatically gets generated when you create a Pivot Table. It is an object that holds a replica of the data source.
Looking for a Data Science Career? Explore Here!!
58. What is Ribbon?
The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users to access many of the most important commands directly.
59. How do you add a Note to a cell?
To add a Note, select the cell and right-click on the same. then select the New Note option and type in any note that you wish to.
60. What is the default value of last parameter of VLOOKUP?
Default value of fourth parameter in VLOOKUP function is 1 or TRUE.
61. How can we change the data value function to MAX or MIN in a pivot table?
By right clicking in the field in “Values” section and clicking on “Value Field Settings”, we can change the function MAX/MIN/AVG etc.
62. How can you say if there is a comment associated with a cell?
Red triangle is visible in the cell which tells that a comment is available, and the comment would be visible if you hover over the cell.
63. 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.
Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])
The REPLACE function swaps part of the text string with another set of text.
Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)
64. What is the restriction on VLOOKUP function?
Lookup value should be the first column in the table array, it always lookup value from right side.
65. How will you remove duplicate values from a column?
Using Remove duplicates option by pressing ALT A, M, A.
66. How could we restrict pivot table from not losing its formatting after data refresh?
Go to “Pivot Table Options” and turn on the “Enable Preserve Formatting” and disable the “Auto Format” option.
67. How is the formatting of data achieved in MS Excel cells?
Some of the data formatting ways for cells are in terms of Number, Alignment, Font, Border, Fill and Protection.
68. What is Relative Cell Address?
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.
69. Will it be feasible to have a Pivot Table from more than one table?
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.
70. Is it possible to hide or show the ribbon?
You can hide or show (minimize or maximize) the ribbon by pressing CNTRL F1.
71. How can you split a column into 2 or more columns?
- Select the cell that you want to split. Then, navigate to the Data tab, after that, select Text to Columns.
- Select the delimiter.
- Choose the column data format and select the destination you want to display the split.
- The final output will look like below where the text is split into multiple columns.
72. How does the IF() function in Excel work?
In Excel, the IF() function performs a logical test. It returns a value if the test evaluates to true and another value if the test result is false. It returns the value depending on whether the condition is valid for the entire selected range.
73. How do we apply advanced filters in Excel?
To apply advanced filters, use the Advanced Filter option present in the Data tab. Select where you want to filter the table. Choose the ‘list range’ and the ‘criteria range’ that has the conditions based on which you would like to filter the table.
74. How do you find duplicate values in a column?
Conditional Formatting
First, go to the Home tab, then under Conditional Formatting, select ‘Highlight Cells Rules’. Then choose ‘Duplicate Values’.
COUNTIF()
You can write a COUNTIF() function to check if the values in a particular column are repeated.
75. 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.
76. What is Data Validation?
Data Validation restricts the type of values that a user can enter into a particular cell or a range of cells.
77. How are nested IF statements used in Excel?
The function IF() can be nested when we have multiple conditions to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.
78. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?
Dynamic Range in the data source of pivot tables is used to make your pivot table dynamic to adjust to new data when refreshed automatically.
79. How do you create a pivot chart in Excel?
- To create a pivot chart, first, we need to create a pivot table.
- Go to the Insert tab next and select the ‘Pivot Chart’ option. Choose a suitable chart to represent your pivot table data.
80. What is the What-If Analysis in Excel?
The What-If Analysis in Excel is a powerful tool to perform complex mathematical calculations, experiment with data, and try out different scenarios.
81. How will you pass arguments to VBA Function?
Arguments can be passed to a VBA function as a reference or as a value.
82. What is the difference between a function and a subroutine in VBA?
| Functions | Subroutines |
| A function is responsible for returning the value of the task it is performing. | Meanwhile, subroutines don’t return the value of the task it is performing. |
| They are called by a variable. | They can be recalled from anywhere in the program, in multiple types. |
| Functions are used as it is in spreadsheets as formulas. | Subroutines are not used directly in spreadsheets as formulas. |
| Functions are used to carry out repetitive tasks, and it, in turn, returns a value. | Users are required to insert a value in the desired cell before fetching the result of the subroutine. |
83. What is the process of making a chart and why is it important to make it right?
Chart is a medium to present the data in graphical visualization, and it is the most
important insight of the data. To present the data with perfect visualization and appropriate
information, we should always pre-decide on the information to be presented. We prepare
the chart and then format it in presentable format.
84. How we can split a column into 2 or more columns?
To split the column into 2 or more columns, we use Text to column option.
85. What is a Dashboard and what are the important things we should keep in
mind while creating a dashboard?
Dashboard is a technique used to present important information through graphical
representation. It is helpful in presenting huge data in a single computer screen so it can be
monitored with a glance.
Important things we should keep in mind while creating a dashboard are:
1) Minimum distraction
2) Simple, easy to communicate
3) Important data
4) Few Colors
5) Relevant graphs
6) Dashboard should be on single computer screen
86. What is the easiest solution to reduce the file size?
- Find the last cell that contains data in the sheet. Delete all rows and columns after
this cell. - To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your
keyboard. - Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the
blank rows.
87. How to select all the objects in the sheet?
To select the object, we use Go to Special option.
- Press the shortcut key F5 to open the Go to Special dialog box
- Click on Special > Click on object > Click on OK
- All objects will get selected
88. What is the use of Name box?
Name Box is located in the left most corner of the Excel sheet. Usually, we use Name box to check the cell reference to the active cell but it has several other uses too.
89. How can we view the values in the right most column in Excel?
We can view the value from the right most column through Index and Match function.
90. How we can change the cell formatting?
To change the cell formatting “Format cell” option is used.
91. How to make dynamic drop down list?
To add item in the list, always create the dynamic list. This list picks the added
value automatically and no editing is required within the list. To create dynamic drop down
list, we use offset function along with Countif function.
Steps to create the dynamic list:
- Select the cell C1
- Go to the Data tab>Data Validation > Data Validation dialog box will appear.
92. How do you lock a worksheet to prevent people from editing it?
It’s possible to password protect worksheets in Excel. This is useful if you have a table of data that, if changed, would impact other dependencies, and for other reasons like user hierarchy authorizations.
93. What could you do to stop the pivot table from loosing the column width upon refreshing?
Format loss in a 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.
Experience the power of our data science course with a free demo – Enroll Now!
94. Explain workbook protection types in Excel.
Excel provides three ways to protect a workbook:
- Password protection for opening a workbook
- Protection for adding, deleting, hiding and unhiding sheets
- Protection from changing size or position of windows.
95. What are left, right, fill and distributed alignments?
Left /Right alignment align the text to left and right most of the cell.
Fill as the name suggests, fill the cell with same text repetitively.
96. How can you 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.”
97. 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.
98. How can you apply the same formatting to every sheet in a workbook in MS-Excel?
Right Click ‘Worksheet tab’ > Choose ‘Select All Sheets’. Now any formatting done will be applied to the whole workbook. To apply to a particular group of sheets, select only those sheets that need formatting.
99. How do you apply a single format to all the sheets present in a workbook?
To apply the same format to all the sheets of a workbook, follow the given steps:
- Right-click on any sheet present in that workbook
- Then, click on the Select All Sheets option
- Format any of the sheets and you will see that the format has been applied to all the other sheets as well.
100. What do you mean by array formula in excel?
An excel array formula can process many values instead of a single value. The array formula evaluates all the values in an array and performs multiple operations based on the conditions expressed in the formula.
Get hands-on with our data science and machine learning course – sign up for a free demo!
| Related Links | |
| Pivot Table in Excel | Data Analysis – Process, Methods, Types |
| Basic Excel Formulas and Functions | Keyboard Shortcuts in Excel |
| Advanced Excel Formulas | Six Sigma |
