Excel Data Cleaning

Recommended priority order:

1. Remove duplicate orders (HIGH): Duplicates directly affect revenue calculations and inventory counts. Fix first to avoid grossly incorrect totals.
2. Standardize product codes (HIGH): Inconsistent codes prevent proper grouping and analysis. Essential for accurate reporting.
3. Remove extra spaces (MEDIUM): Spaces cause matching issues but don't affect numerical calculations. Clean before doing text-based analysis.
4. Fill missing customer names (LOW): Missing names are problematic but don't prevent order analysis. Can use customer ID for most analyses.

General rule: Prioritize issues that affect calculations and aggregations first, then handle formatting issues, finally address missing data that has workarounds.

Field	Cleaning Checks
Names	✓ Remove extra spaces (TRIM) ✓ Standardize capitalization (PROPER) ✓ Check for special characters ✓ Verify no numbers in names
Email	✓ Standardize to lowercase ✓ Verify @ symbol present ✓ Check for domain (.com, .org, etc.) ✓ Remove duplicates
Phone	✓ Standardize format (xxx-xxx-xxxx) ✓ Remove non-numeric characters ✓ Verify 10 digits ✓ Check for missing values
Hire Date	✓ Verify date format consistency ✓ Check dates not in future ✓ Ensure dates after company founding ✓ Convert text dates to date format
Department	✓ Standardize department names ✓ Fix spelling variations ✓ Create dropdown validation list ✓ Check against approved dept list

Steps:
1. Find: GMAIL.COM    Replace: gmail.com
   (Fixes uppercase)

2. Find: @gmail       Replace: @gmail.com
   Options: Match entire cell = OFF
   (Completes incomplete domains)

3. Find: @gmail.co    Replace: @gmail.com
   (Fixes common typo)

Result: All Gmail addresses standardized to @gmail.com

Method 1: Single Wildcard Replace
Find: *-
Replace: (leave empty)

This removes everything before and including the hyphen.

Method 2: Formula Approach (More Flexible)
=RIGHT(A2,5)
or
=VALUE(RIGHT(A2,5))  -- Converts to number

Method 3: Text to Columns
1. Select column
2. Data → Text to Columns
3. Choose Delimited → Next
4. Select Other, enter: -
5. Finish
6. Delete first column (prefixes)

Each method works - choose based on whether you need to keep original data or if the pattern changes.

Solution:

Formula in B2:
=TRIM(PROPER(A2))

Copy down to B5

Results:
B2: "John Smith"
B3: "Jane Doe"
B4: "Bob Johnson"
B5: "Sarah Lee"

Next steps:
1. Copy B2:B5
2. Paste Values over A2:A5
3. Delete column B

Solution:

Step 1: Strip all formatting (Column B)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,
  "-",""),"(",""),")",""),".","")

Result: All become "5551234567"

Step 2: Reformat (Column C)
="("&LEFT(B2,3)&") "&MID(B2,4,3)&"-"&RIGHT(B2,4)

Or use TEXT function if numeric:
=TEXT(VALUE(B2),"(000) 000-0000")

Final Results (C2:C5):
"(555) 123-4567"
"(555) 123-4567"  
"(555) 123-4567"
"(555) 123-4567"

Alternative: Single mega-formula
=TEXT(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  SUBSTITUTE(A2,"-",""),"(",""),")",""),".","")),
  "(000) 000-0000")

Solution:

Comprehensive cleaning formula:

=TRIM(CLEAN(PROPER(SUBSTITUTE(A2,CHAR(160)," "))))

Breaking it down from inside out:

1. SUBSTITUTE(A2,CHAR(160)," ")
   - Replaces non-breaking spaces with normal spaces
   
2. PROPER(...)
   - Standardizes to Title Case
   
3. CLEAN(...)
   - Removes line breaks and non-printable chars
   
4. TRIM(...)
   - Removes extra spaces

Test cases:
Input: "  jOHN   SMITH\n  " 
Output: "John Smith"

Input: "SARAH\nLEE  "
Output: "Sarah Lee"

Input: "  bob   johnson  "
Output: "Bob Johnson"

Production tip: 
Create named range for formula:
Name: CleanText
Formula: =LAMBDA(text,TRIM(CLEAN(PROPER(
         SUBSTITUTE(text,CHAR(160)," ")))))
         
Usage: =CleanText(A2)

Solution:

Duplicate Analysis:

• Row 103: Exact duplicate of row 101 (all columns match). Should be removed.
• Row 104: Case-sensitive duplicate of row 101. Same person with lowercase name. Should standardize with PROPER() then remove.
• Row 105: Partial duplicate of row 102. Same name, same city, but different email. Might be same person with two emails - requires investigation, not automatic removal.

Recommended Actions:

1. Standardize names with PROPER() function
2. Standardize emails with LOWER() function
3. Remove exact duplicates (row 103)
4. Flag row 105 for manual review

Solution:

Formula in B2 (assuming emails in A2:A100):

=IF(COUNTIF($A$2:$A$100,$A2)=1,
    "Unique",
    IF(COUNTIF($A$2:$A2,$A2)=1,
       "Original",
       "Duplicate"
    )
)

Logic Breakdown:
1. COUNTIF($A$2:$A$100,$A2)=1
   - If email appears only once in entire range → "Unique"
   
2. COUNTIF($A$2:$A2,$A2)=1
   - If email appears once in range from start to current row
   - This is the first occurrence → "Original"
   
3. Otherwise → "Duplicate"

Example Results:
A2: john@email.com     B2: Original
A3: jane@email.com     B3: Unique
A4: john@email.com     B4: Duplicate
A5: bob@email.com      B5: Unique
A6: john@email.com     B6: Duplicate

Now filter column B for "Duplicate" to review/remove

Solution:

Method 1: Helper Column with Concatenation

Column E (Unique Key):
E2: =B2&"-"&TEXT(C2,"yyyy-mm-dd")
Result: "C123-2024-01-15"

Column F (Duplicate Flag):
F2: =COUNTIF($E$2:$E$100,E2)>1

Column G (Occurrence Number):
G2: =COUNTIF($E$2:$E2,E2)

Results:
Row 2: E2="C123-2024-01-15"  F2=TRUE  G2=1 (Original)
Row 3: E3="C456-2024-01-15"  F3=FALSE G3=1 (Unique)
Row 4: E4="C123-2024-01-15"  F4=TRUE  G4=2 (Duplicate!)
Row 5: E5="C123-2024-01-16"  F5=FALSE G5=1 (Unique)

Method 2: Single Formula (No Helper Column)

Column E (Duplicate Status):
=IF(COUNTIFS($B$2:$B$100,$B2,$C$2:$C$100,$C2)=1,
    "Unique",
    IF(COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)=1,
       "First",
       "Duplicate-"&COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)
    )
)

Results:
Row 2: "First"
Row 3: "Unique"
Row 4: "Duplicate-2"
Row 5: "Unique"

To Remove Duplicates:
1. Sort by Customer_ID, then Order_Date
2. Use Remove Duplicates tool
3. Select only Customer_ID and Order_Date columns
4. First occurrence kept, others deleted

Alternative: Keep highest amount when duplicates exist
1. Sort by Customer_ID, Order_Date, Amount (descending)
2. Remove duplicates based on Customer_ID + Order_Date
3. Highest amount entry preserved

Analysis:

• Row 102 - Missing Department: FILL - Can look up from HR database using Employee ID, or ask employee
• Row 103 - Missing Salary: FILL with caution - Could use department average, but sensitive data requires verification
• Row 104 - Missing Name: REMOVE or INVESTIGATE - Critical identifier missing, likely test record or data corruption
• Row 104 - Missing Email: FILL - Can construct from naming convention (firstname@company.com) once name is found

Recommended Actions:

1. Row 104: Investigate first (check ID 104 in source system). If truly missing name, delete record
2. Row 102: Fill department from HR system or mark as "Unassigned" temporarily
3. Row 103: Flag for manager review before filling salary
4. Row 104: If name found, construct email using pattern

Formula in D2:
=IF(OR(ISBLANK(B2),ISBLANK(C2)),
   "INCOMPLETE",
   IF(ISBLANK(D2),B2*C2,D2)
)

Logic Breakdown:
1. First check if Quantity OR Price is blank
   → If either is missing, cannot calculate → "INCOMPLETE"
   
2. If both Quantity and Price exist:
   → Check if Total is blank
   → If blank, calculate: Quantity × Price
   → If not blank, keep existing Total

Results:
D2: 50.00 (already filled, keep it)
D3: 50.00 (calculated: 5 × 10.00)
D4: "INCOMPLETE" (missing Quantity)
D5: 60.00 (already filled, keep it)

Alternative - Strict version that only fills if missing:
=IF(ISBLANK(D2),
   IF(OR(ISBLANK(B2),ISBLANK(C2)),"INCOMPLETE",B2*C2),
   D2)

Multi-Step Strategy:

Step 1: Assess Impact

// Count missing patterns
Missing State only: 12% (1,200 records)
Missing Zip only: 5% (500 records)
Missing both: 3% (300 records)
Complete: 80% (8,000 records)

Step 2: Fill Zip from State (if State exists)

// For records with State but no Zip, use most common Zip for that State
=IF(AND(NOT(ISBLANK(B2)),ISBLANK(C2)),
   XLOOKUP(B2,StateLookup[State],StateLookup[MostCommonZip],"00000"),
   C2)

This fills ~5% (500 records)

Step 3: Fill State from Zip (if Zip exists)

// For records with Zip but no State
=IF(AND(ISBLANK(B2),NOT(ISBLANK(C2))),
   XLOOKUP(C2,ZipLookup[Zip],ZipLookup[State],"Unknown"),
   B2)

This fills most of the 12% (State-only missing)

Step 4: Handle Both Missing

// Option A: Use shipping address from previous orders (if repeat customer)
=IF(AND(ISBLANK(B2),ISBLANK(C2)),
   XLOOKUP(A2,HistoricalOrders[CustomerID],HistoricalOrders[State],"CHECK"),
   B2)

// Option B: If no history and both missing, flag for manual review
=IF(AND(ISBLANK(B2),ISBLANK(C2)),"MANUAL REVIEW","OK")

Step 5: Document Changes

// Add audit column showing what was done
=IF(AND(OriginalState="",FinalState<>""),"State Filled from Zip",
  IF(AND(OriginalZip="",FinalZip<>""),"Zip Filled from State",
  IF(AND(OriginalState="",OriginalZip=""),"Manual Review Needed",
  "No Changes")))

Final Result:

• ~93% records complete after automated filling
• ~3-5% flagged for manual review (missing both)
• ~2% may remain unresolvable (delete or mark as "Unknown")
• Full audit trail of all changes

Key Principle: Never silently fill data. Always mark filled values for review and maintain original data in separate columns until validated.

Steps:

1. Select A1:A4
2. Data tab → Text to Columns
3. Choose "Delimited" → Next
4. Check "Comma" → Next
5. Leave data format as "General" → Finish

Result:

A           B      C              D
Name        Age    City           State
John Smith  28     New York       NY
Jane Doe    35     Los Angeles    CA
Bob Johnson 42     Chicago        IL

If your data has headers in first row, consider skipping A1 and starting from A2, then manually type headers.

Steps:

1. In B2, manually type: john.smith@company.com
2. In B3, manually type: jane.doe@company.com
3. Select B4
4. Press Ctrl+E (Flash Fill)
5. Excel fills remaining cells with pattern

Result:

A: Full Name         B: Email Address
John Smith          john.smith@company.com
Jane DOE            jane.doe@company.com
Bob JOHNSON         bob.johnson@company.com
Sarah Lee           sarah.lee@company.com

Why It Works:

• Flash Fill detects: split name at space
• Convert to lowercase
• Join with period
• Add domain @company.com

If Flash Fill doesn't work, add a third example or use formula: =LOWER(SUBSTITUTE(A2," ","."))&"@company.com"

Multi-Step Approach:

Step 1: Standardize First (Add Missing Commas)

// Find state abbreviations without comma before
Find: ([A-Z]{2}) (\d{5})
Replace: , $1 $2
Use Find & Replace with wildcards

Result: All addresses have consistent comma placement

Step 2: Text to Columns (Comma Delimited)

1. Select address column
2. Text to Columns → Delimited → Comma
3. Click Next → Finish

Result:
A: 123 Main St       B: New York     C: NY 10001
A: 456 Oak Avenue    B: Los Angeles  C: CA          D: 90001

Step 3: Clean Up State and Zip

// Extract State (column D)
=LEFT(TRIM(C2),2)

// Extract Zip (column E)
=RIGHT(TRIM(C2),5)

Or use Flash Fill:
- Manually type "NY" in D2, "CA" in D3
- Ctrl+E to fill remaining states
- Repeat for Zip codes

Final Clean Result:

Street          City           State    Zip
123 Main St     New York       NY       10001
456 Oak Avenue  Los Angeles    CA       90001
789 Pine Road   Chicago        IL       60601

Alternative: Power Query (Best for Large Datasets)

1. Data → Get Data → From Table/Range
2. Split Column by Delimiter (comma)
3. Split last column by space to separate State and Zip
4. Trim all columns
5. Close & Load

Best Practice: For complex, repetitive parsing tasks, Power Query is more reliable than Text to Columns because it's repeatable and handles variations better.