Google Sheets Data Cleaning Checklist and Formulas
Create a reproducible sheet-cleaning workflow that preserves originals, proposes relevant formulas, and checks for unintended data loss before replacing source data.
GPT / Claude / Gemini7 variables
Prompt
Design a cautious Google Sheets data-cleaning workflow for my dataset.
SHEET HEADERS: {HEADERS}
REPRESENTATIVE SAMPLE ROWS: {SAMPLE_ROWS}
KNOWN DATA ISSUES: {ISSUES}
DEFINITION OF A DUPLICATE: {DUPLICATE_KEY}
REQUIRED OUTPUT FORMAT: {OUTPUT_SCHEMA}
SHEET LOCALE: {LOCALE}
RESTRICTIONS ON EDITING: {RESTRICTIONS}
Instructions:
1. Identify likely inconsistencies that are visible in the supplied sample; do not claim to have inspected unseen rows.
2. Recommend making a protected copy or separate raw-data tab first.
3. Prioritize fixes: whitespace, case, dates, numeric fields, missing values, duplicates, and schema validation.
4. For each fix, choose a clear Google Sheets formula, menu action, or optional Apps Script and explain its impact.
5. Never infer private data, silently fill unknown values, or delete records based on weak matches.
6. Use helper columns for risky transformations and show how to compare row counts and unique keys afterward.
7. Mention formulas that depend on locale-specific separators.
Return: issue inventory, ordered cleanup steps, relevant formulas, validation checks, and a rollback plan if cleaned results fail review.Quick brief
Purpose
Plan safer cleanup of duplicates, inconsistent formats, and missing values in Google Sheets.
Expected output
A step-by-step cleanup order, formulas or menu actions, validation rules, and before-and-after acceptance checks.
Customize before copying
Replace these placeholders with your own context before you run the prompt.
{HEADERS}{SAMPLE_ROWS}{ISSUES}{DUPLICATE_KEY}{OUTPUT_SCHEMA}{LOCALE}{RESTRICTIONS}
Works well with
GPT
Claude
Gemini
Variations
Focus on contact data with inconsistent phone and email formatting.
Create a duplicate detection plan that does not delete records.
Produce formulas only, using helper columns and no scripts.
What this prompt helps you do
Create a reproducible sheet-cleaning workflow that preserves originals, proposes relevant formulas, and checks for unintended data loss before replacing source data.
When to use it
Use when cleaning survey exports, contact sheets, product catalogs, CRM lists, or downloaded reports with mixed formats.
How it works
Audits sample columns for potential quality problems, ranks cleanup operations, and suggests testable transformations and validation checks.
Best practices
Provide headers, representative sample rows, rules for duplicates, and examples of valid values. Back up the source tab first.
Common mistakes
Do not delete duplicate-looking records without a defined key, infer missing personal information, or overwrite original values before checking results.
What you should expect back
A step-by-step cleanup order, formulas or menu actions, validation rules, and before-and-after acceptance checks.
Limitations
Suggestions based on samples may miss rare anomalies. Sheet locale and feature availability vary; manual validation remains essential.
Model notes
Works with GPT, Claude, or Gemini. Supply the named inputs as plain text; review outputs before publishing or executing.
Real-world applications
Lead lists, event registrations, shop inventory, and operations reporting.
How to tell if it worked
All intended unique records remain, transformed values conform to explicit rules, and the cleanup can be reproduced without losing the raw source.
Where to go next
Use after Excel Formula Generator when a formula is needed for a specific transformation.
Related prompts
Excel Formula Generator With Examples and Error Checks
Build an Excel formula from a plain-language task and a real worksheet layout.
Google Sheets KPI Dashboard Planner
Design a maintainable Sheets dashboard using real data columns and clearly defined metrics.
Compare Options + Pick a Winner (With Criteria)
Turn 'which is better?' into a clear decision with tradeoffs and a final pick.
Meeting Agenda (Clear Outcomes, Not Generic Lists)
Create agendas that make meetings actually productive.
Meeting Notes with Action Items (Clarity & Accountability)
Document meetings so everyone knows what was decided and what happens next.
Daily Schedule Fixer
Rescue a messy day by turning too many loose tasks into a workable plan.