Clean and Standardize Messy Spreadsheet Data with Exact Formulas

Get a column-by-column cleaning plan with ready-to-fill formulas for spaces, case, dates and duplicates, plus a list of rows that need a manual check.

You getCleaning plan table, duplicate check and rows to review by hand

Prompt

Prompt
You are a data analyst who cleans spreadsheets for reporting teams.

I will paste a sample of my messy spreadsheet data, with headers, right after this prompt. Before answering, ask me in one message:
1. Do I use Excel or Google Sheets?
2. About how many rows are in the full dataset?
3. What date format and text case do I want in the final data?
Wait for my answers, then build a cleaning plan.

Rules:
- Cover every column. Give an exact formula that works in my tool, written for row 2 so I can fill down. Write "no change needed" where that is true.
- Check for extra spaces, inconsistent case, mixed date and number formats, blanks and duplicates.
- Never guess a value. If a cell is ambiguous, such as 03/04/2024 or a phone number with missing digits, flag the row instead of fixing it.
- Define duplicates explicitly by naming the columns that must match.
- Leave the original data untouched. Put all formulas in new helper columns.

Output format:
1. Cleaning plan table: Column | Problem found | Formula or step | Before -> After example
2. Duplicate check: rule and formula
3. Rows to review by hand: Row | Value | Why it cannot be fixed safely
4. Final steps, in order

End with one line on the assumptions you made and what the sample might not show.

Example output

At a glanceFive steps to finish cleaning the signup listHelper columns F to K on a 1,200-row customer list
  1. Add helper columns F to KFill each formula down to row 1,201
  2. Resolve CHECK rowsFilter H and I for CHECK, plus rows listed for review
  3. Remove duplicatesFilter K for DUPLICATE, review, then delete
  4. Paste valuesCopy F:J and Paste Special > Values over originals
  5. Tidy and saveDelete helper columns and save a new version

Example for: a 1,200-row customer signup list in Excel. Columns: Name, Email, Signup Date, Phone, State. Target format: yyyy-mm-dd dates, Proper Case names.

1. Cleaning plan
ColumnProblem foundFormula or step (helper column)Before -> After
A: NameLeading spaces, all caps or lowercaseF2: =PROPER(TRIM(A2))" MARIA lopez" -> "Maria Lopez"
B: EmailMixed case, trailing spacesG2: =LOWER(TRIM(B2))"J.Kim@Mail.com " -> "j.kim@mail.com"
C: Signup DateStored as text, mixed formatsH2: =IFERROR(DATEVALUE(TRIM(C2)),"CHECK"), then format H as yyyy-mm-dd"2024-1-9" -> 2024-01-09
D: PhoneBrackets, dashes, spacesI2: =IFERROR(TEXT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")",""),"-","")," ",""),"000-000-0000"),"CHECK")"(555) 214 8890" -> "555-214-8890"
E: StateLowercase, spacesJ2: =UPPER(TRIM(E2))"tx " -> "TX"
2. Duplicate check

Rule: a duplicate is any row whose cleaned email (column G) matches an earlier row.

K2: =IF(COUNTIF($G$2:G2,G2)>1,"DUPLICATE","")

This keeps the first occurrence and marks later ones. Filter K for "DUPLICATE" before deleting anything.

3. Rows to review by hand
RowValueWhy it cannot be fixed safely
7Signup Date: 03/04/2024Could be March 4 or April 3. Confirm with the source system's export format.
12Phone: 555-81Only 5 digits. The missing digits cannot be inferred.
15Email: blankDuplicate check cannot match this row. Find the email or remove the row.
19Name: "McDONALD, JAMES"PROPER gives "Mcdonald, James", and the order is reversed. Fix by hand.
4. Final steps
  1. Add the helper columns F to K and fill each formula down to row 1,201.
  2. Filter H and I for "CHECK" and resolve those rows, plus the ones listed above.
  3. Filter K for "DUPLICATE", review the rows, then delete them.
  4. Copy F:J and use Paste Special > Values over the originals, or into a new sheet.
  5. Delete the helper columns and save a new version of the file.

Assumptions: all phones are 10-digit US numbers and dates without a day-first marker are US format. A 20-row sample may hide other problems, such as international numbers or names with accents.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking for Excel vs Google Sheets first means formulas actually run in your tool instead of failing on syntax differences.
  • The never-guess rule turns ambiguous dates and broken phone numbers into a review list instead of silent errors.
  • Helper columns and a paste-as-values final step keep your original data safe until you have checked the results.

Use this when you export or paste a messy list into Excel or Google Sheets. Have 10-30 representative rows with headers ready to paste into ChatGPT, Claude or Gemini.

Common edits

  • Add your own duplicate rule, such as matching on name plus phone instead of email.
  • Ask for a Power Query or Apps Script version if you clean the same export every week.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets