Excel Cleaner

Excel Cleaner is a free agent skill maintained by Scopeful. It teaches an AI coding agent such as Claude Code, Cursor, Windsurf or Codex how to drive this tool correctly, so you do not have to re-explain it every session. Every published Scopeful skill is free and the install command is public, with no sign-in required. Scopeful also tracks hand-verified USD pricing for 39 AI creative tools at https://www.scopeful.org/tools.

Clean, fix, and restructure a messy spreadsheet or CSV.

Tags: spreadsheets, data-cleaning

Reference

name: excel-cleaner description: Use this skill whenever the user has a messy spreadsheet or CSV that needs cleaning, fixing, or restructuring. Triggers include "clean up this spreadsheet", "fix this CSV", "dates are wrong", "remove duplicates", "split this column", "merge these sheets", "the export is a mess", "numbers stored as text", or any xlsx/csv file described as messy, broken, or inconsistent. Produces a clean file plus a log of every change.

Excel Cleaner

Exports from CRMs, banks, and forms are always broken the same seven ways. This skill fixes them with pandas and hands back a clean file plus a change log — never a silently modified original.

Recipes are pandas 3.x-safe (3.0 shipped Jan 2026: copy-on-write is always on — no chained assignment, no inplace idioms; strings are a real str dtype, not object).

Ground rules

  1. Original untouched. Output to name-clean.xlsx + a cleaning-log.md listing every transformation and row counts before/after.
  2. Inspect before touching: df.shape, df.dtypes, df.head(10), df.isna().sum(), and df.nunique() — report findings and the proposed fixes, get a yes, then clean.
  3. Never drop data silently. Rows removed as duplicates/junk go to a removed sheet in the output file.
  4. Ask about ambiguity, don't guess: 03/04/2026 is March 4 or April 3 — ask which locale the export came from.

Loading

import pandas as pd
df = pd.read_excel("in.xlsx", sheet_name=0)          # or read_csv with sep=None, engine="python" to sniff delimiters
# Header not on row 1 (title rows above): find it, then
df = pd.read_excel("in.xlsx", skiprows=3)
# Multiple sheets, same shape → one table:
df = pd.concat(pd.read_excel("in.xlsx", sheet_name=None).values(), ignore_index=True)

Encoding pain on CSV: try encoding="utf-8-sig" first (Excel BOM), then "cp1252".

The seven standard fixes

# 1. Headers: strip, dedupe, snake_case
df.columns = (df.columns.str.strip().str.lower()
              .str.replace(r"[^\w]+", "_", regex=True).str.strip("_"))

# 2. Whitespace + invisible characters in every text column
for c in df.select_dtypes(include=["object", "string", "str"]).columns:   # pandas 2.x and 3.x
    df[c] = df[c].str.replace("\u00a0", " ", regex=False).str.strip()   # kill non-breaking spaces

# 3. Numbers stored as text ("1 234,56 €", "$1,234.56", "(500)" = negative)
df["amount"] = (df["amount"].astype("str")
    .str.replace(r"[€$\s ,]", "", regex=True)
    .str.replace(r"\((.+)\)", r"-\1", regex=True))
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")   # then REVIEW the rows that became NaN

# 4. Dates (mixed formats in one column are common)
df["date"] = pd.to_datetime(df["date"], errors="coerce", dayfirst=True)  # dayfirst per user's locale answer
# Excel serial numbers (45123.0) mixed in:
mask = pd.to_numeric(df["date_raw"], errors="coerce").notna()
df.loc[mask, "date"] = pd.to_datetime(pd.to_numeric(df.loc[mask, "date_raw"]), unit="D", origin="1899-12-30")

# 5. Duplicates — exact first, then key-based
dupes = df[df.duplicated(keep="first")]                      # goes to the `removed` sheet
df = df.drop_duplicates(keep="first")
df = df.drop_duplicates(subset=["email"], keep="last")       # key dupes: keep newest, confirm the key with the user

# 6. Inconsistent categories ("NY", "New York", "new york ")
df["state"] = df["state"].str.strip().str.title()
print(df["state"].value_counts())                             # show the user; map stragglers explicitly with .replace({...})

# 7. Split / combine columns
df[["first_name","last_name"]] = df["full_name"].str.split(" ", n=1, expand=True)
df["full_address"] = df[["street","city","zip"]].fillna("").agg(", ".join, axis=1)

Copy-on-write reminder: always assign back (df = df.something() or df.loc[...] = ...). Chained patterns like df[df.x > 0]["y"] = 1 do nothing in pandas 3.

Un-pivoting cross-tabs

Humans build month-columns; analysis needs rows:

df = df.melt(id_vars=["product"], var_name="month", value_name="revenue")

Detect it when column headers are dates/months/years — offer the un-pivot, don't force it.

Writing the result

with pd.ExcelWriter("out-clean.xlsx", engine="openpyxl") as xw:
    df.to_excel(xw, sheet_name="clean", index=False)
    dupes.to_excel(xw, sheet_name="removed", index=False)

Add basics that make it usable: freeze header row, auto-width columns, a date format on date columns (openpyxl). Deliver as a real file, then summarize: rows in → rows out, what was fixed, what needs the user's eye (the coerced-NaN list above all).