---
id: gh-clean-data-xls
name: "clean-data-xls"
url: https://skills.yangsir.net/skill/gh-clean-data-xls
author: anthropics
domain: data-analysis
tags: ["data-cleaning", "spreadsheet", "normalization", "deduplication", "data-preparation"]
install_count: 1100
rating: 4.30 (120 reviews)
github: https://github.com/anthropics/financial-services-plugins/tree/main/plugins/vertical-plugins/financial-analysis/skills/clean-data-xls
---

# clean-data-xls

> 此技能用于自动清洗凌乱的电子表格数据，可去除空白字符、修正大小写不一致、转换文本型数字、标准化日期格式、删除重复行并标记混合类型列。适合在数据分析前快速准备干净、一致的数据，提升后续分析效率。

**Stats**: 1,100 installs · 4.3/5 (120 reviews)

## Before / After 对比

### 数据清洗耗时对比

**Before**:

用户手动处理凌乱数据时，需逐一检查空白、格式、重复等问题，耗时且易出错。

**After**:

此技能自动检测并修复常见数据问题，几分钟即可完成清洗，确保数据一致性。

| Metric | Before | After | Change |
|---|---|---|---|
| 清洗时间 | 60分钟 | 5分钟 | -91.7% |

## Readme

# 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

- **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


---
*Source: https://skills.yangsir.net/skill/gh-clean-data-xls*
*Markdown mirror: https://skills.yangsir.net/api/skill/gh-clean-data-xls/markdown*