How to Clean Messy Data with Claude in Excel

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

1. Make a copy of the worksheet so you can compare before and after.
2. Identify the columns that are messy (dates, names, categories, IDs).
3. 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.

Related guides

• /how-to/generate-formulas
• /how-to/find-outliers-and-errors
• /how-to/summarize-text-data