# Clean Spreadsheet Data

Trims, standardizes and deduplicates messy spreadsheet data before analysis.

- Repository: https://github.com/AI4Finance-Foundation/FinRobot (8353 stars)
- Category: Data and ML
- License: Apache-2.0
- Last updated: 2026-10-24
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add AI4Finance-Foundation/FinRobot --skill clean-data-xls -g -a claude-code -y`
Codex: `npx skills add AI4Finance-Foundation/FinRobot --skill clean-data-xls -g -a codex -y`
Cursor: `npx skills add AI4Finance-Foundation/FinRobot --skill clean-data-xls -g -a cursor -y`
Gemini CLI: `npx skills add AI4Finance-Foundation/FinRobot --skill clean-data-xls -g -a gemini-cli -y`

## 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",...

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# 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

The rest: https://github.com/AI4Finance-Foundation/FinRobot/blob/HEAD/finrobot_desktop/skills/financial-analysis/clean-data-xls/SKILL.md