Clean Spreadsheet Data
Trims, standardizes and deduplicates messy spreadsheet data before analysis.
Install
npx skills add AI4Finance-Foundation/FinRobot --skill clean-data-xls -g -a claude-code -y- Repository
- AI4Finance-Foundation/FinRobot8.4k
- Category
- Data and ML
- Updated
- 11d ago
- License
- Apache-2.0
- Context cost
- Lightabout 600 tokens
- Runs scripts
- No, instructions only
What it does
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",...
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 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
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 |
|---|
