📊 Excel Interview Questions — Part 1

1️⃣ What is the difference between VLOOKUP and XLOOKUP?

Sample Answer:

VLOOKUP searches for a value in the first column of a table and returns a value from a column to its right.

XLOOKUP is more flexible because it can search both left and right, supports exact matching by default, and allows a custom result when the value isn't found.

Example:

"=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")"

💡 In modern Excel, I would generally prefer XLOOKUP over VLOOKUP.

2️⃣ What is the difference between COUNT, COUNTA and COUNTBLANK?

Sample Answer:

"COUNT" → Counts cells containing numbers.

"COUNTA" → Counts non-empty cells.

"COUNTBLANK" → Counts empty cells.

Example:

"=COUNT(A2:A100)"

"=COUNTA(A2:A100)"

"=COUNTBLANK(A2:A100)"

💡 This is commonly asked to test your understanding of different data types.

3️⃣ What is the difference between SUMIF and SUMIFS?

Sample Answer:

"SUMIF" is used when we have one condition.

"SUMIFS" is used when we have multiple conditions.

Example:

"=SUMIF(A2:A100,"East",C2:C100)"

This calculates sales for the East region.

For multiple conditions:

"=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")"

This calculates Laptop sales in the East region.

4️⃣ What is the difference between relative and absolute cell references?

Sample Answer:

A relative reference changes when a formula is copied.

Example:

"A2"

An absolute reference remains fixed.

Example:

"A2"

A mixed reference fixes either the row or column:

"$A2"
"A$2"

💡 The "F4" key can be used while editing a formula to cycle through reference types.

5️⃣ What is a PivotTable?

Sample Answer:

A PivotTable is an Excel feature used to quickly summarize and analyze large datasets.

For example, from a sales dataset, I can create a PivotTable to calculate:

→ Sales by region
→ Sales by product
→ Monthly revenue
→ Number of transactions

It allows me to analyze data without manually writing multiple formulas.

6️⃣ How would you remove duplicate records in Excel?

Sample Answer:

I can use Excel's Remove Duplicates feature.

First, select the dataset.

Then:

"Data → Remove Duplicates"

I select the columns that should be considered when identifying duplicates and click OK.

💡 I would usually keep a copy of the original dataset before removing duplicates.

7️⃣ What is IFERROR and why is it useful?

Sample Answer:

"IFERROR" allows me to replace an Excel error with a more meaningful result.

Example:

"=IFERROR(A2/B2,0)"

If the calculation produces an error, Excel returns "0" instead.

Another example:

"=IFERROR(XLOOKUP(A2,E:E,F:F),"Not Found")"

This prevents errors such as "#N/A" from appearing in a report.

8️⃣ How would you find duplicate values in Excel?

Sample Answer:

One common approach is Conditional Formatting.

I select the required range and use:

"Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values"

Another approach is using a formula such as:

"=COUNTIF(A2:A100,A2)>1"

If the result is TRUE, the value appears more than once.

9️⃣ How would you extract the month from a date?

Sample Answer:

I can use the "MONTH" function to return the month number.

"=MONTH(A2)"

For example:

"15-Sep-2026" → "9"

If I need the month name:

"=TEXT(A2,"mmmm")"

Result:

"September"

💡 The approach depends on whether I need the numeric month or the actual month name.

🔟 What is the difference between VLOOKUP and INDEX + MATCH?

Sample Answer:

VLOOKUP searches for a value in the first column and returns a value from a column to the right.

INDEX + MATCH is more flexible because the lookup column doesn't have to be the first column, and it can look in either direction.

Example:

"=INDEX(C2:C100,MATCH(A2,A2:A100,0))"

Here:

"MATCH" → Finds the position of the lookup value.

"INDEX" → Returns the value from that position.

Double Tap ❤️ For More