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