Guys, let's continue our Data Analyst Interview Series.
This time, let's move to one of the most important skills for a Data Analyst: Excel.
Here are 10 Excel interview questions you should know. 👇
1️⃣ What is a PivotTable and why is it used?
Sample Answer:
“A PivotTable is an Excel feature used to quickly summarize and analyze large datasets. It allows me to group, filter, and aggregate data without writing complex formulas.
For example, I can use a PivotTable to calculate total sales by region, product, or month and quickly identify business trends.”
2️⃣ What is the difference between VLOOKUP and XLOOKUP?
Sample Answer:
“VLOOKUP searches for a value in the first column of a selected range and returns a value from another column. It has limitations such as primarily working from left to right.
XLOOKUP is more flexible. It can search in any direction, provides better handling of missing values, and allows separate lookup and return ranges.
For new Excel work, I would generally prefer XLOOKUP when it is available.”
3️⃣ What is the difference between COUNT, COUNTA, COUNTIF, and COUNTIFS?
Sample Answer:
“COUNT counts cells containing numbers.
COUNTA counts non-empty cells.
COUNTIF counts cells that meet one condition.
COUNTIFS counts cells that meet multiple conditions.”
Example:
=COUNT(A2:A100)=COUNTA(A2:A100)=COUNTIF(B2:B100,"Completed")=COUNTIFS(B2:B100,"Completed",C2:C100,">1000")4️⃣ What is the difference between SUMIF and SUMIFS?
Sample Answer:
“SUMIF is used when I have one condition, while SUMIFS is used when I need to apply multiple conditions.
For example, to calculate sales for the India region:
=SUMIF(A:A,"India",B:B)To calculate sales for India where the product is Laptop:
=SUMIFS(C:C,A:A,"India",B:B,"Laptop")5️⃣ How do you remove duplicate records in Excel?
Sample Answer:
“I first determine which columns should uniquely identify a record. Then I can use Excel's Remove Duplicates feature to identify and remove duplicate rows.
However, I would not immediately delete duplicates. I would first verify whether they are genuine duplicates or legitimate repeated transactions.”
6️⃣ How do you handle missing values in Excel?
Sample Answer:
“First, I identify how many values are missing and understand why they are missing.
Depending on the situation, I may replace them with an appropriate value, use a formula such as IF or IFERROR, flag them as ‘Unknown’, or exclude them if the business requirement allows it.
I would avoid blindly replacing missing values because that can affect the accuracy of the analysis.”
7️⃣ What is conditional formatting?
Sample Answer:
“Conditional formatting automatically changes the appearance of cells based on specified conditions.
For example, I can use it to highlight sales below target, overdue transactions, duplicate values, negative profit, or unusually high values.
It is useful for quickly identifying patterns and exceptions in a dataset.”
8️⃣ How would you identify the top 10 customers by sales in Excel?
Sample Answer:
“I could use a PivotTable to summarize total sales by customer, sort the values in descending order, and filter the result to the top 10 customers.