Top 100 Excel Interview Questions And Answers 2021 UPDATED

Februari 15, 2023Januari 17, 2023 falma.u99 answers, excel, interview, questions, updated

Excel Interview Questions and Answers
In case you’re searching for Excel Interview Questions and Answers for Experienced or Freshers, you are at the correct place. Additonally Our Excel Online Training will impart the practical ways to use excel with components like editing, formatting, entering, formatting numbers, modification of rows, modification of columns, managing worksheets, understanding formulas, auto-fill, custom fills and changing views, conditional formatting, data tools, tables, formulas, lookups, graphics, format charts, pivot tables and macros. To complete the jobs assigned for accountants, auditors, administrative clerks, business management, information staff, market analysts, educators, cost estimators, teachers, financial analysts, investment bankers, loan managers, digital marketers, project managers and construction manager’s knowledge of Excel is very essential. Excel is used at beginner level jobs and professional level jobs. Learn Excel Online Course from the best institute to use excel effectively. Find the interview questions and answers for Excel course to get a visual reference for the doubts into the subject.

There is a parcel of chances from many presumed organizations on the planet. The Excel advertise is relied upon to develop to more than $5 billion by 2021, from just $180 million, as per Excel industry gauges. In this way, despite everything you have the chance to push forward in your vocation in Excel Development. Gangboard offers Advanced Excel Interview Questions and answers that assist you in splitting your Excel interview and procure dream vocation as Excel Developer.

Best Excel Interview Questions and Answers
Do you believe that you have the right stuff to be a section in the advancement of future Excel, the GangBoard is here to control you to sustain your vocation. Various fortune 1000 organizations around the world are utilizing the innovation of Excel to meet the necessities of their customers. Excel is being utilized as a part of numerous businesses. To have a great development in Excel work, our page furnishes you with nitty-gritty data as Excel prospective employee meeting questions and answers. Excel Interview Questions and answers are prepared by 10+ years of experienced industry experts. Excel Interview Questions and answers are very useful to the Fresher or Experienced person who is looking for the new challenging job from the reputed company. Our Excel Questions and answers are very simple and have more examples for your better understanding.

In today’s trend, though we have tons of software and tools to perform various technical activities, still we make use of a few basic applications. One of them is Microsoft apps. Yes, MS Word, MS Excel, and MS PowerPoint are the best commonly utilized apps in various organizations and in different small scale and large scale businesses. Having a decent knowledge on MS Excel would help most of the candidates so perform activities on various reports required for business results. There are many jobs that require excel to be a major part of daily. This document will provide you a brief idea of basic excel questions and answers.

If you are looking for Advanced Microsoft Excel interview questions and answers, then you are at the perfect place. In this blog, we are going to cover the advanced excel interview questions and answers. Mastering these Advanced Excel interview questions and answers will help experienced as well as freshers in clearing their interviews with ease. Excel is the most demanded skill need for performing all most all the jobs. This advanced excel interview questions and answers blog covers all the essential concepts of Excel which include start excel, designing a worksheet, scrolling menus, calculation concepts, charts, formulas, analyzing data, etc. Excel is the fundamental technical skill that can be found on any resume. But in real-time when the interviewer asks any excel question, many of the job aspirants are unable to answer the excel interview questions. So to help out, we have gathered here best Excel interview questions, based on the opinions of experts. Let’s get into the excel interview questions and answers.

Q1) Define Microsoft Excel
Answer: 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
Answer: Accounting, Date, Percentage, Number, and Text are the different data formats available in Excel.

Q3) Define Format Painter
Answer: 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
Answer: The place where we store the data is called a cell.

Q5) Why to use comments in Excel?
Answer: 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?
Answer: 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
Answer: 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
Answer: 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?
Answer: Ctrl+F1 is the shortcut key to hide the ribbon in Excel

Q10) How will you protect a sheet in Excel?
Answer: 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.

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

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

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

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

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

Q16) How many rows and columns are present in Microsoft Excel 2013?
Answer: There are rows and columns in Microsoft Excel 2013.

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

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

Q19) While evaluating formulas in Excel, what is the operations order used?
Answer: PEMDAS is the acronym given for the order of operations in Excel.

1. P – Parenthesis/ Brackets
2. E – Exponentiation (^)
3. M – Multiplication
4. D – Division
5. A – Addition
6. S – Subtraction

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

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

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

Q23) Provide the list of volatile formulas
Answer: TODAY(), NOW(), and RAND() are the highly volatile formulas. INDIRECT(), OFFSET(), INFO(), and CELL() are the other volatile formulas.

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

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

Q26) To perform auto-sum on the rows and columns, what is the shortcut?
Answer: ‘ALT=’ is the shortcut to perform auto-sum on the rows and columns.

Q27) How will you open a new Excel workbook using a shortcut key?

Answer: Ctrl+N is the shortcut to open a new Excel workbook.

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

Q29) Define Slicer in Excel
Answer: 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.

Q30) Who designed the Bullet Chart?
Answer: 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.

Q31) What are the different types of data filter available in Excel?
Answer: Date filter, Text Filter and Number Filter are the different types of data filter available in Excel.

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

Q33) Is it possible to remove duplicates in Excel from a data set?
Answer: 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’.

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

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

Q36) What is the syntax of SUBSTITUTE function in Excel?
Answer: Syntax of SUBSTITUTE function in Excel:
‘SUBSTITUTE(text, oldText, newText, [instanceNumber])’

Q37) What is the syntax of REPLACE function in Excel?
Answer: Syntax of REPLACE function in Excel:
REPLACE(oldText, startNumber, NumberCharacters, newText)

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

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

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

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

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

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

Q44) What are the key elements to give a best dashboard?
Answer: 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.

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

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

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

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

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

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

Q51) Define Microsoft Excel?
Answer: 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.

Q52) What is meant by Ribbon in Excel?
Answer: 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.

Q53) Explain the “Data formats” available in excel? List a few of them.
Answer: 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

Q54) List the order of the operations used for evaluating Excel formulas?
Answer: 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

Q55) what is Macro Excel?
Answer: 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.

Q56) Explain the procedure to wrap text within the cell?
Answer: 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.

Q57) Can we able to prevent people from copying the cell from your worksheet?
Answer: 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.

Q58) Explain what the two macro languages are available in MS Excel?
Answer: 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.

Q59) Explain the procedure, to sum up the columns and Rows number instantly in an Excel sheet?
Answer: 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.

Q60) Do we have charts in MS-Excel? And what are they used for?
Answer: 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.

Q61) What does it mean by a red triangle on the top right of a cell?
Answer: 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.

Q62) What is the primary use of comment? How can we add a comment to a cell?
Answer: 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.

Q63) Explain the usefulness of the name box in MS-Excel?
Answer: 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.

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

Q65) list some useful functions in MS-Excel?
Answer: 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

Q66) Can we merge cells in excel?
Answer: 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.

Q67) How can you resize the column?
Answer: 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.

Q68) Explain the three report formats available in Excel?
Answer: We have three types of reports which are:

Q69) Why is Format painter Used?
Answer: 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.

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

Q71) Explain the process to provide Dynamic range In “Data Source”?
Answer: 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.

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

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

Q74) What do you know about Freeze Panes in Excel?
Answer: 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.

Q75) List what are the types of workbook protection in Excel?
Answer: 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.

Q76) How can you stop format losing in a Pivot table?
Answer: 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”.

Q77) What is the difference between COUNTA, COUNTIF, COUNT, and COUNTBLANK present in excel?
Answer:

* 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.

Q78) Can we add shortcuts to excel functions?
Answer: 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.

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

Q80) Can we apply the same formatting to every sheet of a workbook in Excel?
Answer: 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.

Q81) What are the keys that you use to move the next worksheet or to the previous worksheet?
Answer: To move to the next sheet, we need to use Ctrl+PgDown, and to move to the previous sheet in excel, we use Next + PgUp.

Q82) Which filter do you consider if you want to analyze the list using database function?
Answer: We need to use an advanced filter to test two conditions or to analyze more than two conditions.

Q83) What are the benefits of formulas in an Excel sheet?
Answer: Formulas play an essential role in ms excel. Excel acts as a calculator to ‘Sum up’ the number calculates the numbers that are replaced by another number or digit. You can also perform complex calculations in excel such as Payroll deduction or averaging the student’s results etc. by using formulas.

Q84) What is the use of the “ What If” condition?
Answer: 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.

Q85) what is the difference between Pivot charts and regular charts?
Answer: 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.

Q86) How can you format a cell? Explain the available options?
Answer: We can format a cell with the help of the format option. Below are the various format options available in Excel:

* Number
* Font
* Fill
* Alignment
* Border
* Protection

Q87) What is the process to deselect the automatic sorting operation in pivot tables?
Answer: We need to follow the below process to stop automatic sorting operation in pivot tables:
Click on>More Sort Options element > Right Click on ‘Pivot tables’ > click on ‘sort menu’ >then again click on the same ‘More Options’ > to deselect ‘Sort automatically’.

Q88) How can you prevent pivot tables from losing column width when you refresh?
Answer: We stop format losing in a pivot table by merely modifying the options in the pivot table. Under the pivot table, the options that are including are: to disable the option “AutoFormat” and “Enable Preserve Formatting”.

Q89) What are the New Enhancements to Excel?
Answer: The following are some of the New Enhancements to Excel:

* Slicers
* Tables
* Sparklines
* Powerpivot
* IFERROR

Q90) what is the use of the “AND” function in Excel?
Answer: The AND function in excel is used for logical functioning purposes the same as like “IF” function. AND function evaluates whether the output is true or false the AND function will analyze the least mathematical expression which is in the spreadsheet. When you want to know the output of multiple cells, you can use AND function.

Q91) Is cell reference is useful in the Calculation? Then how?
Answer: Yes, the cell reference is useful in the calculation process. It helps in writing the data again and again for calculating purposes. Wherever you write any formula in excel you need to guide excel to find the location of data. This location is termed as a cell reference. So whenever the new value-added to the cell, the cell automatically calculates based on the reference cell formula:

Q92) What is the difference between the function and formula in Microsoft Excel?
Answer: A function is defined as an inbuilt operation that can consider several arguments. When it comes to the formula, it is a user-defined expression helps in calculating a value. In Excel, a user can create complex formulas and have various functions in it. For instance, =B1+B2 is a formula and =SUM(B1:B10) is a function

Q93) What are the top 5 excel functions according to you?
Answer: Below is the top 5 excel functions:

* COUNTIF
* IFERROR
* VLOOKUP
* SUMIF
* INDEX/MATCH

Q94) Name some of the errors that you see in Excel?
Answer: 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.

Q95) Which formula do you use to find the length of a text string in a cell?
Answer: Using the LEN function, you can find the length of a text in a string cell, Assume you would like to know the length of a string cell in B1, you can apply the formula =LEN(A1)

Q96) In which situations do you use the SUBTOTAL function?
Answer: Whenever we are working with tabular data, we need to get the SUBTOTALS of various functions which include; MAX, MIN, AVERAGE, COUNT, STDEV.

Q97) Explain some of the volatile functions in excel?
Answer: A volatile function recalculates the formula again and again (whenever any modification occurs in a worksheet).
Here is the list of volatile formulas

* Highly volatile: NOW(), RAND(), TODAY()
* Almost volatile; CELL(), INDIRECT(), OFFSET(), INFO().

Q98) What Shortcut do you use for the FIND and REPLACE dialogue box?
Answer:

* CONTROL F- This shortcut opens the find and replaces the dialogue box with the selected find tab.
* CONTROL H – this shortcut helps to find and replace the dialogue box with the Replace tab selected.

Q99) What is the shortcut to check the Spell check?
Answer: F7 – This key will open the dialogue box for you to check the spelling.

Q100) Explain some of the interactive controls that you know?
Answer: Excel has many numbers of tools that can be used in a dashboard.

* Checkboxes
* Drop Down Lists
* Radio Buttons
* Scroll bars

Apart from the tools mentioned above, you can also add more functionality to Dashboards using VBA