Excel Interview Questions

MS Excel Interview Questions and Answers

If you are a number reader or data analyst or reporting analyst or an aspiring candidate, then the first and foremost tool you need to have is “MS Excel.” You must be already using excel at your workplace, so when you want to change over the job from one company to another, the interviewer is definitely going to test your skills and knowledge about “MS Excel.” So, keeping this in mind in this article, we are providing the top ten MS Excel Interview Questions and Answers for you.

Excel Interview Questions

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Interview Questions (wallstreetmojo.com)

Top 10 Excel Interview Questions and Answers

The following are the top 10 interview questions and answers related to ms excel.

Question 1: What is the VLOOKUP Function and its Limitation?


This will be the sure question all the excel interviewer asks. The answer must be “VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more is a lookup function in excel which is used to fetch the data from one table to another table based on the lookup value available. Tables could be in a different worksheet or could be in a different workbook as well, so it doesn’t matter in which excel worksheet it is in, VLOOKUP can fetch the data if the lookup value is available in both the tables.”

Excel Interview - Question 1

The limitation of the VLOOKUP function is “it can fetch the data from left to right only.” If the required column is to the left of the lookup value column, then VLOOKUP cannot fetch the data from right to left.

Question 2: Are there any alternatives available for the limitation of VLOOKUP?


Yes, there is an alternative available to this limitation.

Excel Interview - Question 2

There are multiple alternatives, but a common and often used alternative is the combination of the “Index MATCH” function in excel. So, for this combination function, it doesn’t matter where the result column is, be it to the right of the lookup value column or to the left of the lookup value column; this function can fetch the result for us.

Also, have a look at this article – Alternatives to VLOOKUPAlternatives To VLOOKUPTo reference data in columns from right to left, we can combine the index and match functions, which is one of the best Excel alternatives to Vlookup.read more.

Question 3: What are Excel Tables, and how are they different from normal data tables?


Excel tablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.read more are not just a range of cell which contains data, but it is a structured reference object which exposes the user to the wide variety of features.

Tables are unlike normal random data sets because with Excel Tables, any addition or deletion of data will automatically impact the data range provided to the formulas and pivot tables, so the user need not to worry about the reference change for their formulas and pivot tables.

To create a table, we can simply press the shortcut excel keyPress The Shortcut Excel KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more “Ctrl + T” so it will create the table for us.

Excel Interview - Question 3

Question 4: What are Logical Function and name any three and their functionality?


Logical functions are used to perform calculations that need to match multiple criteriaMatch Multiple CriteriaCriteria based calculations in excel are performed by logical functions. To match single criteria, we can use IF logical condition, having to perform multiple tests, we can use nested IF conditions. But for matching multiple criteria to arrive at a single result is a complex criterion-based calculation.read more. So, before doing the calculation, we need to apply logical tests, and if the logical test is TRUE, we can apply one set of calculations, and if the logical test is FALSE, we can perform another set of calculations.

Three important logical functions are “IF, AND, OR.”

IF logical function is to perform criteria based calculation.

Excel Interview - Question 4 (IF Function)

AND function in excelAND Function In ExcelThe AND function in Excel is classified as a logical function; it returns TRUE if the specified conditions are met, otherwise it returns FALSE.read more used to match all the logical tests.

Excel Interview - Question 4 (AND Function)

OR function is used to match at least one logical test.

Excel Interview - Question 4 (OR Function)

Question 5: What is Paste Special and name some of the frequently used methods?


Paste SpecialPaste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more is a method used in excel to paste the copied data with multiple options. Frequently used paste special methods are as follows.

Excel Interview - Question 5
  • Paste as Values: When we copy the formula cell, if you need only the result of the formula, then we can use the “paste as values” option.
  • Paste as Divide: When we want to convert all the values to lakhs, we can simply enter 1 Lakh in any of the cells and copy the cell, select the lakh conversion range of cells and paste as “divide,” and all the values will be converted to lakhs.
  • Paste as Transpose: When we want to swap rows and columns of the data, then we can use the “Transpose” option in paste special.

Question 6: What is the Pivot Table?


Pivot TablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more is the tool used in excel to summarize the data quickly. Using Pivot Table, we can tell the story behind the data with simple drag and drop options, all the multiple entry items grouped into one all the totals of respective line items will be added together to show the summary report.

Excel Interview - Question 6

We can also get the summary in terms of “SUM, AVERAGE, and COUNT,” etc.

Question 7: What Does Text Function Do?


TEXT function in excelTEXT Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more is used to convert the selected value to different formatting options.

Question 7

Using the TEXT function, we can change the date format, we can change the time format, we can change the number format, and like this, we can apply the formatting that we needed for the respective cells.

Question 8: What is CSE Formula in Excel?


CSE stands for “Control Shift-Enter” in Excel. These keys are used to apply the formula as “array formulas.” When the array formulaArray FormulaArray formulas are extremely helpful and powerful formulas that are used in Excel to execute some of the most complex calculations. There are two types of array formulas: one that returns a single result and the other that returns multiple results.read more has been used, we can perform complex calculations in a single cell itself.

We can identify whether a formula is a regular formula or array formula by looking at the formula; if the formula ends contains curly brackets ({}), then we can consider it as an “array formula.”

Question 8

Question 9: What is the Named Range in Excel?


Named range in excelNamed Range In ExcelName range in Excel is a name given to a range for the future reference. To name a range, first select the range of data and then insert a table to the range, then put a name to the range from the name box on the left-hand side of the window.read more is nothing but giving unique names to the range of cells so that we can easily make use of the range of cells by using the name itself without worrying about going to the respective range of cells and make use of the range.

Question 9

Question 10: Differentiate between COUNT & COUNTA Functions in Excel?


COUNT & COUNTA two identical functions which may confuse users. The basic difference between them is as follows.

COUNT: Count function in excel counts only numerical values such as numbers, dates except for empty cells.

Question 10 (COUNT)

COUNTA: CountA functionCountA FunctionThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more counts all the cells which are non-empty. So it doesn’t matter whether it is numbers or text values; any cell that is non-blank will be counted.

Question 10 (COUNTA)

This has been a guide to Excel Interview Questions. Here we discuss the list of top 10 ms excels questions along with their answers to crack the interview. You can learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion