How to Clean Messy Data with Claude in Excel
Step-by-step guide to standardize formats, remove duplicates, and fix inconsistencies using Claude in Excel.
Feb 6, 2026
When this guide helps
Use this workflow when your spreadsheet has inconsistent dates, mixed text formats, duplicated rows, or inconsistent naming (for example: "NY", "New York", and "NYC").
Prepare your sheet first
- Make a copy of the worksheet so you can compare before and after.
- Identify the columns that are messy (dates, names, categories, IDs).
- Add a new column for Claude results when possible, so you can review changes before overwriting.
Step-by-step workflow
1. Ask Claude to profile the data
Prompt:
Review columns A through F and list the data-quality issues you see. Group them by column.2. Standardize formats
Prompt:
Standardize date formats in column B to YYYY-MM-DD. Put the cleaned values in column G and explain any rows you could not convert.3. Normalize text categories
Prompt:
Normalize the values in column C so "NY", "New York", and "NYC" all become "New York". Put results in column H.4. Deduplicate rows
Prompt:
Find duplicate rows based on columns A, C, and D. Flag duplicates in column I and keep the earliest entry.5. Validate the results
Prompt:
Compare columns B vs. G and C vs. H. Summarize any changes that look risky.Practical tips
- Ask Claude to work on a specific range for speed and accuracy.
- Keep original values so you can quickly audit changes.
- If your dataset is large, process in batches and name the ranges.
Common pitfalls
- Cleaning without a backup makes it hard to review errors.
- Standardizing categories without a mapping list can create incorrect merges.