Excel Interview Questions

If you are a number reader, data analyst, reporting analyst, or aspiring candidate, the first and foremost tool you need to know is MS Excel. 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. So, keeping this in mind in this article, we provide the top ten MS 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:

That will be the sure question all the Excel interviewer asks. The answer must be “VLOOKUP is a LOOKUP function in Excel used to fetch the data from one table to another based on the lookup value available. Tables can be in a different worksheet or a separate workbook. So, it does not matter which Excel worksheet it is in. The VLOOKUP function can fetch the data if the lookup value is available in both the tables."

The limitation of the VLOOKUP function is that "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 a common and often used choice is combining the “Index MATCH” function in excel. So, it does not matter where the result column is for this combination function, 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 cells containing data, but it is a structured reference object that exposes the user to various features.

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

To create a table, we can press the shortcut excel key “Ctrl + T” so it will make the table for us.

Question 4: What are Logical Functions 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 math, we need to apply logical tests. And if the logical test is "TRUE," we can use one set of calculations. If the logical test is "FALSE," we can perform another set of measures.

Three important logical functions are IF, AND, and OR.

The IF logical function is to perform criteria-based calculations.

The AND function in excel is used to match all the logical tests.

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

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. The frequently used Paste Special methods are as follows:

• Paste as Values: When we copy the formula cell, if we 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 enter "1 Lakh" in any cell and copy the cell. Then, we must select the lakh conversion range of cells and paste it as "Divide." It will convert all the values to lakhs.
• Paste as Transpose: When we want to swap rows and columns of the data, we can use the "Transpose" option in Paste Special.

Question 6: What is the Pivot Table?

Answer:

The Pivot Table is the tool used in Excel to summarize the data quickly. Using PivotTable, we can tell the story behind the data with simple drag and drop options. All the multiple entry items grouped into one will add all the totals of individual line items to show the summary report.

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

Question 7: What Does Text Function Do?

Answer:

The TEXT function in excel converts the selected value to different formatting options.

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

Question 8: What is the 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 or an array formula by looking at the formula. For example, if the formula ends contain curly brackets ({}), then we can consider it as an "array formula."

Question 9: What is the Named Range in Excel?

Answer:

The Named range in excel is nothing but giving unique names to the range of cells so that we can easily use the range of cells by using the name itself without worrying about going to the respective range of cells and making use of the range.

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

Answer:

The COUNT and COUNTA are two identical functions that may confuse users. The basic difference between them is as follows:

COUNT: The COUNT function in Excel counts only numerical values such as numbers and dates except for empty cells.

COUNTA: The COUNTA function counts all the non-empty cells. Whether numbers or text values, it can calculate any non-blank cells.

This article is a guide to Excel Interview Questions. Here we discuss the list of top 10 MS Excel questions and their answers to crack the interview. You can learn more about Excel from the following articles: -

• Count Number of Excel Rows & Columns
• ISBLANK Function in Excel
• Comparison Excel Charts