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?

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

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?

Answer:

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

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.

To create a table, we can simply press the shortcut excel key “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?

Answer:

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

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.

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?

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.

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?

Answer:

TEXT function in excel 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?

Answer:

CSE stands for “Control Shift-Enter” in Excel. 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 an “array formula.”

Question 8

Question 9: What is the Named Range in Excel?

Answer:

Named range in excel 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?

Answer:

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

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
LEARN MORE >>