Lesson ProgressPhase 3 of 6
Phase 3Guided Practice
Guided Practice: Data Cleaning and Analysis
Practice cleaning logic before touching the real workbook
Phase 3: Safe Rehearsal
Practice the Cleaning Logic
Before cleaning real data, practice each step in this safe simulator. Get the patterns right first.
Step-by-Step Cleaning Practice
- TRIM: Removes leading/trailing spaces. Use on product names, category names.
- Find & Replace: Remove $ symbols from prices. Replace with nothing.
- Text to Columns: Convert text dates to real dates. Choose appropriate delimiter.
- PROPER: Capitalize first letter of each word. "latte" → "Latte"
- Remove Duplicates: Select all columns, use Data → Remove Duplicates.
- Filters: Show rows meeting criteria, hide rest temporarily.
Pro Tip
Always make a copy of raw data before cleaning. Keep original as backup!
Practice: Clean These Values
Type the cleaned result in the middle column. Try each cleaning operation.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Original | Cleaned (Your Turn) | Tool to Use | |||||||
| 2 | Latte | TRIM | ||||||||
| 3 | $4.50 | Find/Replace $ | ||||||||
| 4 | 2024-05-19 | Text to Columns | ||||||||
| 5 | muffin | PROPER | ||||||||
| 6 | ESPRESSO | PROPER | ||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
Outlier Detection Preview
After cleaning, you'll flag outliers—values far from the typical range. Here's the logic:
Z-Score = (Value - Mean) / Standard Deviation
- |Z| > 3: Likely an error—investigate
- |Z| 2-3: Possible outlier—check context
- |Z| < 2: Normal variation
You'll practice this in phase 4 after cleaning real data.
Common Mistakes to Avoid
Cleaning the wrong column
Check which column you're in before running any tool!
Not selecting all columns for Remove Duplicates
Select every column or you'll miss partial duplicates
Making a backup first
Always copy raw data before any cleaning operation