Post #2619

✅ Excel Interview Questions with Answers📊💼 1️⃣ How do you clean a messy dataset in Excel? Steps: - TRIM() → removes extra spaces =TRIM(A1) - CLEAN() → removes non-printable characters =CLEAN(A1) - Remove Duplicates → Data → Remove Duplicates - Text to Columns → split data - Find & Replace (Ctrl+H) → fix values - Filter → remove blanks or errors 2️⃣ Absolute vs Relative References Relative (A1) → changes when copied Absolute ($A$1) → stays fixed When to use: - Relative → normal calculations - Absolute → fixed values (tax rate, constants) 3️⃣ Create PivotTable for Sales Analysis Steps: 1. Select data 2. Insert → PivotTable 3. Drag: Region → Rows, Product → Columns, Sales → Values Used for fast data summarization. 4️⃣ VLOOKUP Formula + #N/A Fix Formula: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE) Fix #N/A: - Check lookup value exists - Match data types Use: =IFERROR(VLOOKUP(A2, A:B, 2, FALSE),"Not Found") 5️⃣ INDEX-MATCH vs VLOOKUP VLOOKUP:=VLOOKUP(A2,A:B,2,FALSE) INDEX-MATCH:=INDEX(B:B, MATCH(A2,A:A,0)) ✅Why INDEX-MATCH? - Faster for large data - Works left lookup - More flexible 6️⃣ COUNTIF vs SUMIF vs COUNTIFS COUNTIF → count condition =COUNTIF(A:A,"East") SUMIF → sum condition =SUMIF(A:A,"East",B:B) COUNTIFS → multiple conditions =COUNTIFS(A:A,"East",B:B,">500") 7️⃣ Goal Seek Used for what-if analysis. Steps: 1. Data → What-if Analysis → Goal Seek 2. Set cell → target value 3. Change variable cell Example: target revenue calculation. 8️⃣ Conditional Formatting Top 10% Steps: Select data Home → Conditional Formatting Top/Bottom Rules → Top 10% 9️⃣ Dynamic Dashboard + Slicers Create PivotTable Insert → Slicer Insert → Timeline (for dates) Connect slicers to multiple visuals Used for interactive dashboards. 🔟 SUMPRODUCT (Multi-condition sum) =SUMPRODUCT((A2:A10="East")(B2:B10>500)C2:C10) Used for weighted or multiple-condition calculations. 1️⃣1️⃣ What is Power Query? Excel’s ETL tool. Steps: - Get Data → Load data - Remove columns - Change types - Remove duplicates - Load cleaned data Used for automation and transformation. 1️⃣2️⃣ Freeze Panes vs Split Panes Freeze Panes → lock rows/columns while scrolling Split Panes → divide screen into sections 1️⃣3️⃣ XLOOKUP vs VLOOKUP XLOOKUP:=XLOOKUP(A2,A:A,B:B) ✅Advantages: - Left lookup - No column index - Default exact match - Handles errors 1️⃣4️⃣ Circular References Fix Occurs when formula refers to itself. Fix: Formulas → Error Checking → Circular References Correct formula logic 1️⃣5️⃣ Data Validation + Named Range Steps: 1. Formulas → Define Name 2. Data → Data Validation → List 3. Select named range Used for dropdown lists. Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i Double Tap ♥️ For More