MS Excel Interview Questions and Answers

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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?

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

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 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 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 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 make the table for us.

Question 4: What are Logical Functions 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 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 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 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?

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

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

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

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

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

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 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 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 & ColumnsISBLANK Function in ExcelComparison Excel Charts