Excel Data Cleaner

Problem: Excel cleanup still relies on manual steps

Real spreadsheets rarely arrive clean: duplicate order IDs, dates stored as 2026/7/5 instead of 2026-07-05, amounts with ¥ and thousands separators, phone numbers with spaces, and monthly reports split across multiple files. Analysts often spend significant time filtering, replacing, joining, and checking outliers before any modeling, pivot table, or statistical work. This skill turns those repeated preprocessing actions into natural-language instructions instead of requiring manual VBA, Power Query, or ad hoc scripts.

How it works: split cleanup into verifiable steps

The skill focuses on six common data problems:

• Deduplication: detect duplicates by one or multiple columns, keeping the first or last occurrence.
• Missing values: drop rows with blanks, fill fixed values, fill mean/median/mode, forward-fill, or mark imputed rows.
• Format normalization: unify dates to yyyy-mm-dd, remove currency symbols and thousands separators, and normalize phone numbers or ID fields.
• Outlier detection: use IQR, Z-score, fixed thresholds, or pattern checks to flag suspicious values.
• Splitting and merging: split addresses or tags by delimiters, or concatenate name parts into a single field.
• Multi-sheet aggregation: stack same-structure files vertically, or join files horizontally by keys such as employee ID or order ID.

Each run produces a structured report. Safety rules include creating a _raw_backup before cleaning, preserving the original file, writing a new cleaned file, and previewing samples for large files. It depends on the platform’s file access and a Python environment with pandas and openpyxl.

Boundary: useful preprocessing, not business validation

The skill supports .xlsx, .xls, and .csv, with a practical limit around 500,000 rows per file. Outlier detection is statistical, not business-correctness validation, and merged cells or complex formatting may be lost. It fits the stage before analysis when a messy spreadsheet needs to become a usable table, not full ETL, database cleanup, or rule-heavy domain logic.