AI Prompt to Clean and Standardize a Messy Spreadsheet Dataset
This is a data cleaning prompt for turning a messy spreadsheet or CSV export into a consistent, analysis-ready dataset — built for analysts, marketers, and small business owners who receive data from multiple sources (form exports, CRM downloads, manual entry) with inconsistent formatting. It's aimed at problems like mismatched date formats, inconsistent capitalization, duplicate rows, and stray whitespace that break downstream analysis or reporting.
The prompt asks the model to work through the dataset systematically: identify formatting inconsistencies column by column, propose a standard format for each, flag likely duplicates or outliers for human review rather than silently deleting them, and return both the cleaned data and a plain-language summary of every change made. This keeps a human in the loop for judgment calls (like whether two similarly-named rows are actually duplicates) while automating the repetitive formatting work.
ChatGPT, Claude, and Gemini can all read and reformat tabular data pasted as text, but none of them execute your original spreadsheet formulas or verify cleaned output against your source system, so any cleaned dataset should be spot-checked against the original before it's used for reporting or decisions.
Prompt template
ROLE: You are a data cleaning assistant helping standardize a spreadsheet dataset for analysis. CONTEXT: Below is a sample of the dataset. Columns are: [LIST OF COLUMN NAMES] [PASTE SAMPLE DATA HERE, INCLUDING KNOWN PROBLEM ROWS] TASK: 1. Review each column and identify formatting inconsistencies (e.g. date formats, capitalization, whitespace, abbreviations, units) 2. Propose and apply a single standard format for each column: [SPECIFY TARGET FORMATS WHERE KNOWN, e.g. dates as YYYY-MM-DD] 3. Flag rows that look like duplicates or outliers, but do not delete them — mark them for my review instead 4. Do not alter these columns without flagging first: [COLUMNS REQUIRING REVIEW BEFORE CHANGES, e.g. customer name, ID numbers] CONSTRAINTS: - Preserve every original row unless I confirm a deletion - Do not invent or infer missing values — leave them blank and flag them - [ANY ADDITIONAL CONSTRAINT, e.g. keep a specific column's original casing] OUTPUT FORMAT: 1. The cleaned dataset as a table 2. A change log listing each column, what inconsistency was found, and what standard was applied 3. A separate list of flagged rows (possible duplicates/outliers) with a one-line reason for each flag
Example input
ROLE: You are a data cleaning assistant helping standardize a spreadsheet dataset for analysis. CONTEXT: Below is a sample of the dataset. Columns are: Customer Name, State, Signup Date, Plan John Smith, NY, 03/14/2024, pro john smith , new york, 2024-03-14, Pro Maria Garcia, CA , 14-03-2024, PRO Maria Garcia, California, 03/14/24, pro David Chen, TX, 3/14/2024, Basic TASK: 1. Review each column and identify formatting inconsistencies 2. Propose and apply a single standard format for each column: dates as YYYY-MM-DD, state as two-letter abbreviation, plan name capitalized 3. Flag rows that look like duplicates or outliers, but do not delete them 4. Do not alter the Customer Name column's spelling without flagging first CONSTRAINTS: - Preserve every original row unless I confirm a deletion - Do not invent or infer missing values OUTPUT FORMAT: 1. The cleaned dataset as a table 2. A change log 3. A list of flagged rows with reasons
Example output
**Cleaned Dataset**
| Customer Name | State | Signup Date | Plan |
|---|---|---|---|
| John Smith | NY | 2024-03-14 | Pro |
| john smith | NY | 2024-03-14 | Pro |
| Maria Garcia | CA | 2024-03-14 | Pro |
| Maria Garcia | CA | 2024-03-14 | Pro |
| David Chen | TX | 2024-03-14 | Basic |
**Change Log**
- Signup Date: converted all entries (03/14/2024, 2024-03-14, 14-03-2024, 03/14/24, 3/14/2024) to YYYY-MM-DD format
- State: standardized "new york" and "California" to two-letter abbreviations (NY, CA); trimmed trailing whitespace from "CA "
- Plan: standardized casing to "Pro" and "Basic"
- Customer Name: trimmed extra whitespace in "john smith " and "Maria Garcia"; casing left unchanged pending your review (see flags)
**Flagged Rows for Review**
1. Row 1 and Row 2 ("John Smith" / "john smith") — likely the same customer with inconsistent capitalization; same signup date and plan
2. Row 3 and Row 4 ("Maria Garcia" / "Maria Garcia") — likely the same customer; identical date and plan, only spacing differsWhen to use it
- Cleaning a CSV or spreadsheet export from a form tool, CRM, or survey platform before importing it into another system
- Standardizing inconsistent entries (like "NY", "N.Y.", and "New York" all referring to the same value) across a column
- Preparing a dataset for analysis or a dashboard where formatting inconsistencies would otherwise skew grouping or filtering
- Getting a second pass on a dataset you've already partially cleaned, to catch anything you missed
Best practices
- Paste a representative sample of the data (including any known problem rows) rather than just the first few clean-looking rows, so the model sees the actual range of inconsistencies
- Tell the model which columns are safe to standardize automatically versus which ones need your review before changing (like customer names, where merging duplicates has business consequences)
- Ask for a change log alongside the cleaned data so you can audit exactly what was altered
- Spot-check the cleaned output against a few rows of your original data before using it, since the model can misjudge an edge case (like two genuinely different customers with similar names)
Common mistakes
- Asking the model to "just clean this up" without specifying target formats, which leads to inconsistent judgment calls
- Letting the model silently delete rows it thinks are duplicates instead of flagging them for review
- Pasting a huge dataset in one block and expecting perfect accuracy — very large datasets are better cleaned in batches with spot checks between them
- Not specifying date, currency, or unit formats, resulting in a mix of formats that still don't match your target system
FAQs
How do I use AI to clean messy spreadsheet data?
Paste a representative sample of your data into a prompt that specifies target formats for each column, asks the model to flag (not delete) likely duplicates, and requests a change log so you can review exactly what was altered.
Can ChatGPT or Claude fix inconsistent dates and formatting in a CSV?
Yes, both can read pasted tabular data and reformat columns like dates, states, and capitalization to a standard you specify, though you should always spot-check the output against your original file.
Will AI automatically remove duplicate rows from my dataset?
It shouldn't without your approval. This prompt explicitly tells the model to flag possible duplicates and outliers for review rather than deleting them, since only you know the business context behind a borderline case.
What's the best way to clean a large spreadsheet with AI without losing data?
Break the dataset into smaller batches, specify exact target formats for each column upfront, and request a change log with each batch so you can verify nothing was altered incorrectly before combining the results.
Does this data cleaning prompt work in Gemini and Claude, not just ChatGPT?
Yes, the prompt structure is model-agnostic. Any model that can read pasted tabular text and follow formatting instructions can perform this kind of column-by-column standardization.