๐ง 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.