Data Analyst Interview Questions with Answers: Part-4

31. What are Pivot Tables?
Pivot tables summarize large datasets quickly.
Example: Rows → Product, Values → Sum of Sales
Result: Total sales per product in seconds.

32. Difference between VLOOKUP and XLOOKUP?
VLOOKUP works left to right only. XLOOKUP works both ways and handles missing values better.
Example: =XLOOKUP(A2, Products!A:A, Products!B:B)
Fetches product name using product ID.

33. What is conditional formatting?
Highlights data based on rules.
Example: Highlight sales > 10000 in green.
Helps spot top performers instantly.

34. What are COUNTIFS and SUMIFS?
They apply conditions while counting or summing.
Example: =SUMIFS(C:C, A:A, "East", B:B, "Laptop")
Total sales of laptops in East region.

35. What is data validation?
Restricts incorrect data entry.
Example: Create dropdown for Region (East, West, North).
Data → Data Validation → List.

36. How do you remove duplicates in Excel?
Select data, Data → Remove Duplicates
Example: Remove duplicate customer IDs.

37. What is IF formula used for?
Applies logical conditions.
Example: =IF(C2>5000,"High Sales","Low Sales")

38. Difference between relative and absolute reference?
Relative → A2 changes when copied
Absolute → $A$2 stays fixed
Example: =A2*$E$1 Tax rate fixed while copying formula.

39. How do you clean data in Excel?
Remove duplicates, TRIM extra spaces, Fix date formats, Handle blanks
Example: =TRIM(A2)

40. What are common Excel mistakes analysts make?
• Merged cells
• Hard-coded values
• No pivot tables
• Poor formatting
• No documentation

Double Tap ♥️ For Part-5