ChatGPT Prompts for Data Cleaning Workflows
20 copy-paste ChatGPT prompts for data cleaning: deduplication, missing values, outliers, standardization, type conversion. The unsexy work that determines whether analysis is right or wrong.
In short: This page contains 16 copy-paste ready prompts, organized into 4 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Hand-curated and tested by the AI Academy team.
Standardization
4 promptsStandardization Plan
1/16✨ What it does
ChatGPT writes a standardization plan for [Paste sample data]: case, trimmed whitespace, uniform dates, and abbreviation rules, with rules per column and transformation steps. Paste a sample, then apply the rules to a copy before you touch production.
[Paste sample data]. Standardization plan: text case (all lower / proper / upper), trim whitespace, format dates uniformly, standardize abbreviations. Output: rules per column, transformation steps.
Pro tip: Standardization first; analysis after. "USA" / "U.S.A." / "United States" = 3 different values to your computer. Standardize to one canonical form.
Phone + Email Format
2/16✨ What it does
ChatGPT standardizes phone and email columns: strip spaces and dashes, consistent country codes, lowercase trimmed emails, with formulas or Power Query steps. Run the steps on a copy of your file, then spot-check a few numbers and addresses.
Standardize phone + email columns. Phone: strip spaces/dashes, country code consistency. Email: lowercase, trim. Output: formulas / Power Query steps. Common cleaning task.
Pro tip: Phone numbers in 10 formats = same person counted as 10. Standardize to E.164 (+15551234567). Emails lowercase. Then dedupe = one record per actual person.
Date Standardization
3/16✨ What it does
ChatGPT standardizes [Paste mixed dates] to one format, with detection per row, DATEVALUE, handling ambiguous dates such as 1/2/2024, and error handling. Paste the mixed dates, then decide the day-month order before you convert the column.
[Paste mixed dates]. Standardize to single format. Output: detection logic (which format each row), DATEVALUE, parsing for ambiguous (1/2/2024 = Jan 2 or Feb 1?), error handling.
Pro tip: Mixed date formats = #1 data quality issue. Especially MM/DD/YYYY vs DD/MM/YYYY. Sometimes ambiguous (1/2/24 could be either). Source-tracking + careful parsing.
Address Cleaning
4/16✨ What it does
ChatGPT cleans an address column by parsing street, city, state, and zip, standardizing abbreviations, optional Google or USPS validation, and international cases. Parse a sample of 20 rows, then validate the odd ones before you batch the rest.
Clean address column. Output: parse into components (street, city, state, zip), standardize abbreviations (St → Street or vice versa), validate via API (Google, USPS), handle international.
Pro tip: Addresses notoriously messy. APIs (Google Maps, USPS) standardize + validate. Manual cleaning = errors. For volume, API integration worth it.
Prompts get you started. Tutorials level you up.
A growing library of 300+ hands-on AI tutorials. New tutorials added every week.
Deduplication
4 promptsDuplicate Detection Strategy
5/16✨ What it does
ChatGPT plans duplicate detection in [data] for exact single-column matches, fuzzy matches across columns, normalize-then-dedupe, and when to merge versus keep separate. Describe the data, then normalize first and review your fuzzy pairs by hand.
Detect duplicates in [data]. Output: exact match (single column) easy, fuzzy match (across columns), normalize first then dedupe, when to merge vs keep separate. Strategy depends on data.
Pro tip: Detect duplicates AFTER standardization, not before. "Smith" / "smith " / "SMITH" = duplicates after trim+lowercase. Order matters.
Fuzzy Matching
6/16✨ What it does
ChatGPT builds fuzzy matching with Levenshtein, soundex, Power Query Fuzzy Match, or OpenRefine, plus scale, precision versus recall, for customer names and addresses. Pick the tool for your volume, then set a conservative threshold and review the near-matches.
Fuzzy duplicate detection. Output: methods (Levenshtein distance, soundex, Power Query Fuzzy Match, OpenRefine), tools per scale, tradeoff (precision vs recall). For: customer names, addresses.
Pro tip: Customer names: "Smith Inc" / "Smith Inc." / "Smith Incorporated" = same. Fuzzy matching catches. Power Query has fuzzy match; for serious work, OpenRefine free + powerful.
Merge Decision Logic
7/16✨ What it does
ChatGPT writes merge logic for duplicates: most-recent by date, most-complete (fewest blanks), source priority such as CRM over a spreadsheet, and a manual review queue so you do not auto-merge blindly. Choose a winner rule, then send close calls to the review queue.
Merging duplicates: which value wins. Output: most-recent (by date), most-complete (fewest blanks), source priority (CRM > spreadsheet), manual review queue. Don't auto-merge blindly.
Pro tip: Auto-merging without rules = lost data. "Most-recent" wins for: contact info. "Most-complete" for: profiles. Source-priority for: cross-system. Decide rules; document.
Match Confidence Scoring
8/16✨ What it does
ChatGPT scores match confidence from 0-100 (exact email = 100, fuzzy name plus same company = 80, and similar), with an auto-merge threshold and a manual queue. Set the auto-merge bar, then review everything under your threshold.
Score match confidence (0-100). Output: criteria (exact email = 100, fuzzy name + same company = 80, etc.), threshold for auto-merge, manual review queue. Confidence-driven merging.
Pro tip: Auto-merge >90% confident; queue 60-90% for review; ignore <60%. Tiered processing = efficient + accurate. Eliminates "merge everything" or "merge nothing" extremes.
Missing Values
4 promptsMissing Value Strategy
9/16✨ What it does
ChatGPT sets a missing-value strategy per column: delete the row, fill a default, calculate, leave blank, or flag for follow-up. Decide per field, then document the choice before you fill anything.
Handle missing values. Output: by column, decision (delete row, fill with default, calculate, leave blank, flag for follow-up). Different fields = different strategies.
Pro tip: Missing values not all equal. Missing income = leave blank (sensitive). Missing zip = lookup from address. Missing required = ask source. Per-column strategy.
Imputation Methods
10/16✨ What it does
ChatGPT imputes missing values for [column] with mean, median, mode, regression, k-nearest neighbors, or a model, plus when each is appropriate and the bias it adds. Name the column, then pick the simplest method and note the bias in your write-up.
Impute missing values for [column]. Methods: mean/median/mode, regression-based, k-nearest-neighbors, predictive model. Output: when each appropriate, biases introduced. Imputation = trade-off.
Pro tip: Imputation introduces bias. Mean = pulls toward center. Median = ignores extremes. Predictive = best but complex. Document what you imputed; flag if material to analysis.
Forward/Backward Fill
11/16✨ What it does
ChatGPT applies forward or backward fill on a time series when values change slowly or stay sticky, with Excel or Pandas ffill/bfill and alternatives. Use fill only on sticky fields, then show the original gaps in a note on your file.
Forward / backward fill for time series. Output: when appropriate (slow-changing values, sticky states), tools (Excel formula, Pandas ffill/bfill), alternatives. Time-series specific.
Pro tip: Forward fill: take last known value forward. Useful for: stock prices, status codes. Inappropriate for: continuous data (interpolate instead). Match method to data type.
Missing Data Documentation
12/16✨ What it does
ChatGPT documents missing-data handling: which fields were missing, treatment, percent imputed versus original, impact on the analysis, and an audit trail. Attach that note to your file, then share it with anyone who will reuse the numbers.
Document missing data handling. Output: which fields had missing, treatment per field, % imputed vs original, impact on analysis, transparency for audit. Documentation = trust.
Pro tip: Undocumented imputation = analysis can't be reproduced + audited. Document: what was missing, how filled, why. Future-you debugging = thanks past-you.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. 300+ easy-to-follow tutorials inside AI Academy — and growing every week.
Outliers + Validation
4 promptsOutlier Detection
13/16✨ What it does
ChatGPT detects outliers in [Data] with more than 3 standard deviations, IQR, top/bottom 1%, and boxplots, then helps you investigate whether each is real or an error. Paste the data, then investigate the flagged rows before you drop them.
[Data]. Detect outliers. Methods: standard deviation (>3σ), IQR (>1.5x IQR), percentile (top/bottom 1%), visual (boxplot). Output: identification + investigation. Real or error?
Pro tip: Outliers can be: data error, real but extreme, or signal (interesting). Don't reflexively remove. Investigate; sometimes the outlier IS the story.
Range Validation
14/16✨ What it does
ChatGPT sets range checks (ages 0-120, percents 0-100, reasonable dates) and flags out-of-range values with whether to correct, exclude, or query the source. Run the flags, then query the source on anything you cannot explain.
Range validation: ages should be 0-120, percentages 0-100, dates within reasonable. Output: flag out-of-range, action (correct, exclude, query source).
Pro tip: Range errors common. "Age = 200" = data entry error. Validation catches at cleaning; wrong values pollute analysis.
Cross-Field Validation
15/16✨ What it does
ChatGPT writes cross-field rules such as order_date before ship_date, child_age under parent_age, and total equals the parts, plus a list of violations. Run the rules, then fix or escalate every break before you publish.
Cross-field validation. Examples: order_date < ship_date, child_age < parent_age, total = sum of components. Output: rules + violations. Logical consistency.
Pro tip: Single-field validation = basic. Cross-field = sophisticated. "Birth date 2020 + role manager" = inconsistency. Logical rules across columns = quality.
Source-of-Truth Reconciliation
16/16✨ What it does
ChatGPT reconciles [source A] versus [source B]: discrepancies, a source of truth per field, documented differences, and an automated ongoing check. Name both sources, then pick the truth per field and schedule the check on your side.
Reconcile data across [source A] vs [source B]. Output: identify discrepancies, decide source of truth per field, document differences, automate ongoing check. Multi-source = where data quality dies.
Pro tip: Two sources = two truths. Common in orgs: CRM vs ERP vs Marketing tool. Decide source-of-truth per field. Automate check; manual reconciliation = breaking ground.
Free tool
Prompt Optimizer
Turn a rough idea into a structured, professional AI prompt.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.
7-day free trial. Cancel anytime.
Related guides