Clean Data
Clean messy data in the active sheet or a specified range.
Environment
- If running inside Excel (Office Add-in / Office JS): Use Office JS directly (
Excel.run(async (context) => {...})). Read via range.values, write helper-column formulas via range.formulas = [["=TRIM(A2)"]]. The in-place vs helper-column decision still applies.
- If operating on a standalone .xlsx file: Use Python/openpyxl.
Workflow
Step 1: Scope
- If a range is given (e.g.
A1:F200), use it
- Otherwise use the full used range of the active sheet
- Profile each column: detect its dominant type (text / number / date) and identify outliers
Step 2: Detect issues
| Issue |
What to look for |
| Whitespace |
leading/trailing spaces, double spaces |
| Casing |
inconsistent casing in categorical columns (usa / USA / Usa) |
| Number-as-text |
numeric values stored as text; stray $, ,, % in number cells |
| Dates |
mixed formats in the same column (3/8/26, 2026-03-08, March 8 2026) |
| Duplicates |
exact-duplicate rows and near-duplicates (case/whitespace differences) |
| Blanks |
empty cells in otherwise-populated columns |
| Mixed types |
a column that's 98% numbers but has 3 text entries |
| Encoding |
mojibake (é, ’), non-printing characters |
| Errors |
#REF!, #N/A, #VALUE!, #DIV/0! |
Step 3: Propose fixes
Show a summary table before changing anything:
| Column |
Issue |
Count |
Proposed Fix |
Step 4: Apply
Resolve date locale and number scale before conversion. 03/08/26 is ambiguous without a convention; retain it and flag it rather than guessing with DATEVALUE. Preserve identifiers with leading zeros; distinguish percent, decimal, thousands and millions. Use helper columns and source-to-output mapping for every transformation.
- Prefer formulas over hardcoded cleaned values — where the cleaned output can be expressed as a formula (e.g.
=TRIM(A2), =VALUE(SUBSTITUTE(B2,"$","")), =UPPER(C2), =DATEVALUE(D2)), write the formula in an adjacent helper column rather than computing the result in Python and overwriting the original. This keeps the transformation transparent and auditable.
- Only overwrite in place with computed values when the user explicitly asks for it, or when no sensible formula equivalent exists (e.g. encoding/mojibake repair)
- For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first
- After each category of fix (whitespace → casing → number conversion → dates → dedup), show the user a sample of what changed and get confirmation before moving to the next category
- Report a before/after summary of what changed
1---2name: clean-data-xls3description: >-4 Cleans a messy spreadsheet or table: trims whitespace, unifies spellings and casing, converts5 text numbers and mixed dates, removes duplicates and flags mixed-type or error cells, proposing6 fixes before applying them. Use when the user says "clean this data", "this sheet is a mess",7 "dedupe", "standardize the dates" or "normalize this column", for Excel, CSV or pasted tables.8 Not for auditing model formulas (use audit-xls).9metadata:10 source: https://skillmd.com/skills/the-utopia-studio/clean-data-xls11---1213# Clean Data1415Clean messy data in the active sheet or a specified range.1617## Environment1819- **If running inside Excel (Office Add-in / Office JS):** Use Office JS directly (`Excel.run(async (context) => {...})`). Read via `range.values`, write helper-column formulas via `range.formulas = [["=TRIM(A2)"]]`. The in-place vs helper-column decision still applies.20- **If operating on a standalone .xlsx file:** Use Python/openpyxl.2122## Workflow2324### Step 1: Scope2526- If a range is given (e.g. `A1:F200`), use it27- Otherwise use the full used range of the active sheet28- Profile each column: detect its dominant type (text / number / date) and identify outliers2930### Step 2: Detect issues3132| Issue | What to look for |33|---|---|34| Whitespace | leading/trailing spaces, double spaces |35| Casing | inconsistent casing in categorical columns (`usa` / `USA` / `Usa`) |36| Number-as-text | numeric values stored as text; stray `$`, `,`, `%` in number cells |37| Dates | mixed formats in the same column (`3/8/26`, `2026-03-08`, `March 8 2026`) |38| Duplicates | exact-duplicate rows and near-duplicates (case/whitespace differences) |39| Blanks | empty cells in otherwise-populated columns |40| Mixed types | a column that's 98% numbers but has 3 text entries |41| Encoding | mojibake (`é`, `’`), non-printing characters |42| Errors | `#REF!`, `#N/A`, `#VALUE!`, `#DIV/0!` |4344### Step 3: Propose fixes4546Show a summary table before changing anything:4748| Column | Issue | Count | Proposed Fix |49|---|---|---|---|5051### Step 4: Apply5253Resolve date locale and number scale before conversion. `03/08/26` is ambiguous without a convention; retain it and flag it rather than guessing with DATEVALUE. Preserve identifiers with leading zeros; distinguish percent, decimal, thousands and millions. Use helper columns and source-to-output mapping for every transformation.5455- **Prefer formulas over hardcoded cleaned values** — where the cleaned output can be expressed as a formula (e.g. `=TRIM(A2)`, `=VALUE(SUBSTITUTE(B2,"$",""))`, `=UPPER(C2)`, `=DATEVALUE(D2)`), write the formula in an adjacent helper column rather than computing the result in Python and overwriting the original. This keeps the transformation transparent and auditable.56- Only overwrite in place with computed values when the user explicitly asks for it, or when no sensible formula equivalent exists (e.g. encoding/mojibake repair)57- For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first58- After each category of fix (whitespace → casing → number conversion → dates → dedup), show the user a sample of what changed and get confirmation before moving to the next category59- Report a before/after summary of what changed