Home
cd ../playbooks
Finance & AccountingBeginner

Spreadsheet Data Cleaner

Detect and fix messy spreadsheet data including whitespace issues, inconsistent casing, numbers stored as text, date problems, duplicates, and encoding issues.

5 minutes
By anthropicSource
#data cleaning#spreadsheet#Excel#data quality#deduplication#formatting

Messy spreadsheet data — inconsistent formatting, numbers stored as text, duplicate rows, garbled characters — turns a simple analysis into hours of manual cleanup before you can even start the real work.

Who it's for: financial analysts, data analysts, operations teams, FP&A professionals, accountants, anyone working with imported or consolidated spreadsheet data

Example

"Clean this customer data export — fix the formatting issues and remove duplicates" → Cleaned spreadsheet with trimmed whitespace, standardized dates, converted numbers, deduplicated rows, and a summary report of all changes made

CLAUDE.md Template

New here? 3-minute setup guide → | Already set up? Copy the template below.

Or install from your terminal:
npx ccplaybooks add fa-clean-data-xls
# Spreadsheet Data Cleaner

**Important**: Always review proposed changes before applying them. Back up your original data before running any cleaning operations.

Systematic workflow for cleaning messy spreadsheet data. Detects and fixes whitespace issues, inconsistent casing, numbers stored as text, date format problems, duplicates, blank cells, mixed-type columns, and encoding issues.

## Cleaning Workflow

### Step 0: Back Up the Original

Before any cleaning, create a copy of the original file or sheet. Never clean data in place without a backup.

Get new playbooks like this one

One email a week with new Claude Code workflows. Free, like everything here.

No spam. Unsubscribe anytime.

README.md

What This Does

Provides a systematic workflow for cleaning messy spreadsheet data. Detects and fixes whitespace issues, inconsistent casing, numbers stored as text, date format problems, duplicates, blank cells, mixed-type columns, and encoding issues — all with user approval before applying changes.


Quick Start

Step 1: Create a Project Folder

Create a folder for your data cleaning work and place the downloaded template inside as CLAUDE.md.

Step 2: Download the Template

Click Download above, then move the file into your project folder as CLAUDE.md.

Step 3: Start Working

"Clean this sales data — standardize the date formats and fix the text issues"
"Remove duplicates and fix inconsistent company names in this spreadsheet"
"This financial data has numbers stored as text — convert them and clean up the formatting"

Cleaning Order

Fixes are applied in a specific order to avoid cascading issues:

  1. Encoding fixes — Fix character encoding first since it affects all text operations
  2. Whitespace — TRIM and CLEAN all text cells
  3. Casing standardization — Title Case for names, UPPER for codes, etc.
  4. Number conversion — Convert numbers-as-text to actual numbers
  5. Date standardization — Convert all dates to ISO 8601 (YYYY-MM-DD)
  6. Category consolidation — Merge inconsistent labels ("USA" / "US" / "United States")
  7. Duplicate removal — Remove exact duplicates; flag near-duplicates for review
  8. Blank handling — Fill, flag, or remove blanks based on context

What Gets Detected

Check Example
Leading/trailing whitespace " John Smith "
Numbers as text Numeric values with text formatting
Date inconsistencies "01/15/2024" vs "2024-01-15" vs "Jan 15, 2024"
Inconsistent categories "USA", "US", "United States", "U.S."
Duplicates Exact and near-duplicate rows
Special characters Non-printable characters, BOM markers
Outliers Values far outside expected range

Tips & Best Practices

  • Always back up your original data before running any cleaning operations
  • Provide context: Tell the assistant what each column represents so it can apply appropriate cleaning rules
  • Specify preferred formats: Date format, casing preferences, category names
  • Flag columns to skip: Some columns should not be cleaned (free-text notes, unique IDs with leading zeros)
  • Set a materiality threshold: For large datasets, focus on issues affecting more than 1% of rows first
  • Iterate: Run a first pass for critical issues, review, then run a second pass for refinements

Handling Specific Data Types

The template includes specialized handling for phone numbers (strip non-numeric, standardize format), email addresses (lowercase, validate format, catch common typos like "gmial.com"), addresses (standardize abbreviations), and currency values (remove symbols, convert to numeric).

$Related Playbooks

Finance & Accounting

Competitive Landscape Analysis Deck

Build institutional-quality competitive landscape presentations with market positioning, competitor deep-dives, moat assessment, and strategic synthesis.

5 minutes
Beginner
Finance & Accounting

Comparable Company Analysis Builder

Build institutional-grade comparable company analyses in Excel with operating metrics, valuation multiples, quartile statistics, implied valuation, and professional formatting.

5 minutes
Intermediate
Finance & Accounting

DCF Model Builder for Equity Valuation

Create institutional-quality discounted cash flow models with WACC calculations, sensitivity analysis, and professional Excel output for equity valuation.

5 minutes
Intermediate
Finance & Accounting

Presentation Deck Number Refresh

Update numbers across an existing presentation deck — quarterly refreshes, earnings updates, comp rolls, and rebased market data — while preserving all formatting.

5 minutes
Beginner
Finance & Accounting

Investment Banking Deck Quality Checker

Comprehensive QC review of pitch decks and client presentations covering number consistency, data-narrative alignment, language polish, and visual formatting.

5 minutes
Beginner
Finance & Accounting

LBO Model Builder for Private Equity

Build leveraged buyout models in Excel with Sources & Uses, operating projections, debt schedules, returns analysis (IRR/MOIC), and sensitivity tables following investment banking standards.

5 minutes
Intermediate
Finance & Accounting

PowerPoint Template Skill Creator

Turn a branded PowerPoint template into a reusable skill that can generate on-brand presentations programmatically, with precise placeholder mapping and layout documentation.

5 minutes
Beginner
Finance & Accounting

Expense Report Generator

Organize business expenses into categorized reports for reimbursement, accounting, and tax preparation.

10 minutes
Intermediate
Finance & Accounting

Expense Tracker & Categorizer

Automate expense tracking, receipt processing, approval workflows, and reimbursement management

10 minutes
Advanced
Finance & Accounting

Personal Finance Manager — AI Budget Tracker from Bank Statements

Import bank statements (CSV or PDF) and get instant spending breakdowns, budget analysis using the 50/30/20 rule, savings recommendations, and interactive charts. All data stays local on your machine.

10 minutes
Beginner
Finance & Accounting

Financial Statement Generator

Generate P&L, balance sheet, and cash flow statements from financial data

10 minutes
Advanced
Finance & Accounting

Grow Sustainably (Without VC)

Evaluate every business decision — spending, hiring, fundraising, scaling — through the lens of profitability and infinite runway. The anti-burnout, anti-blitzscaling operating manual.

5 minutes
Intermediate

Browse all Finance & Accounting playbooks →