AI Model Wiki

    Skip to main content
    10 min read
    Lesson 2 of 5

    Data Cleaning & Transformation

    From the course AI for Excel & Google Sheets

    Dirty data is the #1 spreadsheet productivity killer. AI automates the tedious cleaning tasks that used to take hours.

    Common Data Cleaning Tasks

    ProblemAI Solution
    Inconsistent dates"Standardize all dates in column A to YYYY-MM-DD format"
    Duplicate rows"Identify and highlight duplicate rows based on email and name"
    Mixed formats"Split full names into first name and last name columns"
    Typos & variations"Standardize company names (e.g., 'Microsoft Corp', 'MSFT', 'Microsoft')"
    Missing values"Fill missing ZIP codes based on city and state columns"

    Text Manipulation

    AI excels at text operations:

    • "Extract email domains from the email column"
    • "Convert product codes like 'PRD-001-BLU-XL' into separate columns for category, number, color, and size"
    • "Clean phone numbers to format (XXX) XXX-XXXX"
    • "Remove all special characters from the company name column"

    Data Validation

    • "Check if all email addresses in column B are valid format"
    • "Flag rows where the total doesn't match the sum of line items"
    • "Identify outliers that are more than 3 standard deviations from the mean"
    • "Verify that all dates fall within the fiscal year 2026"

    Using Copilot for Data Cleaning

    In Excel with Copilot:

    1. Select your data range
    2. Open Copilot and describe the cleaning task
    3. Copilot creates a new column with cleaned data
    4. Review and apply changes

    Using ChatGPT for Sheets

    For complex cleaning without built-in AI:

    1. Describe your data structure to ChatGPT
    2. Paste a sample of messy data
    3. Get formulas or Apps Script code to clean it
    4. Apply across your dataset

    Batch Operations

    AI handles bulk transformations:

    • Convert currencies across entire columns
    • Categorize free-text entries into standardized categories
    • Geocode addresses to lat/long coordinates
    • Translate columns to different languages

    Named Example: Alphabet's Finance Team & Stripe

    Stripe's finance team documented using ChatGPT to generate a Python + pandas script that cleaned 400,000 rows of transaction data, standardizing 47 inconsistent currency format variations in under 10 minutes. Alphabet (Google) has deployed Gemini in Sheets to auto-clean BigQuery exports before analysis — flagging rows where calculated revenue doesn't match the sum of line items, a check that previously required a manual formula across every row.

    Common Pitfall: AI-generated cleaning formulas that work on a sample often fail on the full dataset because edge cases appear only at scale. Always test cleaning transformations on at least 1,000 representative rows and check the results before running on millions of records.

    Best Practice: When asking AI to clean data, provide it with 10-20 representative rows including examples of each problematic variation. The more specific examples you give (e.g., "Microsoft Corp, MSFT, microsoft inc, Microsoft."), the more accurately the AI can write a normalization rule that handles all the real-world variants.

    Key Takeaways

    • •AI automates data cleaning tasks that previously took hours of manual work
    • •Text manipulation, standardization, and deduplication are AI strengths
    • •Built-in Copilot and Gemini offer in-spreadsheet AI cleaning
    • •ChatGPT generates formulas and scripts for complex cleaning tasks
    0 of 5 lessons marked complete

    Progress is kept in this browser only.