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

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?

Answer:

This will be the sure question all the excel interviewer asks. The answer must be “VLOOKUP 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.”

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?

Answer:

Yes, there is an alternative available to this limitation.

There are multiple alternatives but common and often used alternative is the combination of “Index + MATCH” function. 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.

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

Answer:

Excel tables 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.

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

Answer:

Logical functions are used to perform calculations that need to match multiple criteria. So, before doing the calculation we need to apply logical test and if the logical test is TRUE we can apply one set of calculation and if the logical test is FALSE we can perform another set of calculations.

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

Answer:

Paste Special is a method used in excel to paste the copied data with multiple options. Frequently used paste special methods are as follows.

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 cell 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?

Answer:

Pivot Table 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.

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

Question 7: What Does Text Function Do?

Answer:

TEXT function is used to convert the selected value to different formatting options.

Using 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?

Answer:

CSE stands for “Control Shift Enter”. These keys are used to apply the formula as “array formulas”. When the array formula 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 “array formula”.

Question 9: What is the Named Range in Excel?

Answer:

Named range 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 10: Differentiate between COUNT & COUNTA Functions in Excel?

Answer:

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

COUNTA:CountA function 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.

Recommended Articles

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