Skills › Financial Services › clean-data-xls
clean-data-xls
The facts
Its description
Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", "this data is messy".
The description the agent reads to decide when to use it.
From its settingsline 3
When it starts
A person asks the agent to tidy up messy spreadsheet data — trimming whitespace, fixing inconsistent casing, converting numbers stored as text, standardizing dates, removing duplicates, or flagging mixed-type columns — either in the active sheet or in a range they name.
Things a person might say (the model's guesses)
- “This sheet is a mess — can you clean it up before I analyze it?”
- “Dedupe the rows in A1:F200 and standardize the date column.”
- “The country column has usa, USA and Usa — normalize this data.”
A model's summary of when it is used.
A model's reading
Stops for a person
1 line stops to ask a person
Lines that stop and wait for a person before going on. Found by matching words, so treat them as pointers.
Found by matching wordsline 48
Licence
Apache-2.0, from LICENSE
The terms its author shares it under, which let it be shown here.
From the repositorylicence text
The text
Most marks come from matching words in the file, so treat them as pointers.
1---
2name: clean-data-xls
3description: Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", "this data is messy".
4---
6# Clean Data
8Clean messy data in the active sheet or a specified range.
10## Environment
12- **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.
13- **If operating on a standalone .xlsx file:** Use Python/openpyxl.
Environment
- If running inside Excel (Office Add-in / Office JS): Use Office JS directly (
Excel.run(async (context) => {...})). Read viarange.values, write helper-column formulas viarange.formulas = [["=TRIM(A2)"]]. The in-place vs helper-column decision still applies. - If operating on a standalone .xlsx file: Use Python/openpyxl.
Workflow
Raw lines 15 to 1617### Step 1: Scope
19- If a range is given (e.g. `A1:F200`), use it
20- Otherwise use the full used range of the active sheet
21- Profile each column: detect its dominant type (text / number / date) and identify outliers
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
23### Step 2: Detect issues
25| Issue | What to look for |
26|---|---|
27| Whitespace | leading/trailing spaces, double spaces |
28| Casing | inconsistent casing in categorical columns (`usa` / `USA` / `Usa`) |
29| Number-as-text | numeric values stored as text; stray `$`, `,`, `%` in number cells |
30| Dates | mixed formats in the same column (`3/8/26`, `2026-03-08`, `March 8 2026`) |
31| Duplicates | exact-duplicate rows and near-duplicates (case/whitespace differences) |
32| Blanks | empty cells in otherwise-populated columns |
33| Mixed types | a column that's 98% numbers but has 3 text entries |
34| Encoding | mojibake (`é`, `’`), non-printing characters |
35| Errors | `#REF!`, `#N/A`, `#VALUE!`, `#DIV/0!` |
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! |
37### Step 3: Propose fixes
39Show a summary table before changing anything:
41| Column | Issue | Count | Proposed Fix |
42|---|---|---|---|
Step 3: Propose fixes
Show a summary table before changing anything:
| Column | Issue | Count | Proposed Fix |
|---|
44### Step 4: Apply
46- **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.
47- 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)
48- For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first
49- 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
50- Report a before/after summary of what changed
Step 4: Apply
- 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
Involves a person confirm with the user :48
From anthropics/financial-services at 6056506, 2026-10-28. Skills read by DeepSeek V4.1 Flash on 2026-11-04. Summary and techniques by Claude Opus 5.5 on 2026-11-06.