🎯 📊 EXCEL INTERVIEW QUESTIONS WITH ANSWERS
🧠 1️⃣ Tell me about your Excel experience and key projects
✅ Sample Answer:
"I have 3+ years using Excel for data analysis, financial modeling, and dashboard creation across sales, finance, and operations teams. Advanced proficiency in PivotTables, Power Query ETL, XLOOKUP/INDEX-MATCH, VBA automation, and dynamic array formulas. Recently automated a 50-store P&L reporting system that reduced monthly close time from 3 days to 4 hours while improving accuracy from 87% to 98%."
📊 2️⃣ What are the differences between VLOOKUP, INDEX/MATCH, and XLOOKUP? When would you use each?
✅ Answer:
VLOOKUP searches the first column rightward only with a fragile column index. INDEX/MATCH is bidirectional with dynamic columns: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). XLOOKUP is the new standard—searches any direction, returns arrays, exact match by default: =XLOOKUP(lookup_value, lookup_array, return_array, "Not Found"). Production choice: XLOOKUP. Fallback: INDEX/MATCH. VLOOKUP is for legacy only.
🔗 3️⃣ What are the differences between COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS functions?
✅ Answer:
COUNT: Numbers only. COUNTA: Non-blank cells. COUNTBLANK: Empty cells. COUNTIF: Single condition like =COUNTIF(A1:A100,">50"). COUNTIFS: Multiple conditions like =COUNTIFS(Sales[Date],">1/1/2025", Sales[Region],"East"). Array alternative: =SUMPRODUCT((Sales[Amount]>1000)*(Sales[Region]="East")).
🧠 4️⃣ What is a PivotTable? How do you create one and what are its key features?
✅ Answer:
Create: Insert → PivotTable → Select range → New worksheet. Fields: Rows (grouping), Columns (pivot), Values (aggregate), Filters (slicers). Advanced: Calculated fields via Pivot Analyze → Fields/Items/Sets, date/number grouping, Show Values As % of total/running total, slicers/timelines, and data model relationships. Pro tip: Convert source to a table first for dynamic range.
📈 5️⃣ What are IFERROR, ISERROR, and IFNA functions? When would you use each for error handling?
✅ Answer:
IFERROR catches all errors (#DIV/0!, #N/A): =IFERROR(XLOOKUP(...),"Not Found"). ISERROR tests for logical use. IFNA catches only #N/A for lookups. Best practice: Wrap risky formulas. Nested: =IFERROR(VLOOKUP(...),IFERROR(INDEX/MATCH(...),"Manual Check")).
📊 6️⃣ What is Power Query? Walk through the ETL process and common transformations you perform
✅ Answer:
Power Query (Data → Get Data): ETL (Extract, Transform, Load) engine with refreshable transformations. Workflow: Source → Transform preview → Close & Load. Transformations: remove duplicates, split columns, unpivot columns→rows, merge/append queries, group by aggregation, and custom M language columns. Example: Monthly CSV folders → clean → append → PivotTable source.
📉 7️⃣ Compare SUMIF, SUMIFS, and SUMPRODUCT. Which is best for performance vs flexibility?
✅ Answer:
SUMIFS: Multiple criteria, readable =SUMIFS(Amount,Date,">1/1/2025",Region,"East"). SUMPRODUCT: Array formula for complex logic (A1:A100>1000)*(B1:B100="East"). SUMIF: Single criteria only. Performance: SUMIFS is fastest. Flexibility: SUMPRODUCT handles OR logic, wildcards, and dates elegantly.
📊 8️⃣ How does conditional formatting work? Give business examples with custom formulas
✅ Answer:
Rule types: Color scales, data bars, icon sets, top/bottom rules, and custom formulas. Formula examples: Above average =A1>AVERAGE($A$1:$A$100), weekends =WEEKDAY(A1,2)>5, duplicates =COUNTIF($B$1:$B$100,B1)>1. Business use: Aging receivables (red=90+ days), sales heatmaps, and KPI thresholds.
🧠 9️⃣ Explain dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE with examples
✅ Answer:
Excel 365 spill arrays expand automatically. FILTER: Dynamic subset =FILTER(Sales, (Sales[Region]="East")*(Sales[Amount]>1000)). SORT: Dynamic sort =SORT(Sales,3,-1). UNIQUE: Remove duplicates. SEQUENCE: Auto-numbers =SEQUENCE(10,1,1,1). Combo: =SORT(FILTER(Sales,Sales[Amount]>10000),3,-1) → Top sales descending.
