Organize information

Create a column-by-column Excel data cleaning plan

Inspect field meanings and define reversible cleaning rules before changing data

8 min setupTested with:ChatGPTReviewed: 2026-08-28

Add your context

Your text stays in this browser. AILesson Prompts does not send it to a model or server.

Dataset purposeColumns and intended meaningsRepresentative rows and known issues

Your prompt

Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt

Design a reversible Excel data-cleaning plan.

Dataset purpose:
[purpose]

Columns and intended meanings:
[schema]

Representative rows and known issues:
[sample]

Do not edit or discard values yet. Profile each column for type, blanks, uniqueness, allowed values, formatting, and cross-field consistency. Separate harmless presentation differences from changes that alter meaning. Return:
1. Blocking questions and assumptions
2. Column-by-column issue table with evidence
3. Cleaning rule, validation rule, and exception handling for each issue
4. Recommended helper/audit columns
5. Safe execution order with a preserved raw copy
6. Before/after tests and a rollback method

Never infer missing identities, categories, dates, or business facts from appearance alone.

Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.

From input to outcome

A worked example

See how concrete context turns this recipe into a usable result

Actual input

Dataset purpose: Prepare a customer export for CRM import and regional sales counts. The CRM requires a unique Customer ID, a valid email or blank, ISO country codes, and an ISO signup date.
Columns and intended meanings: CustomerID required unique text; Email optional; Country required ISO alpha-2; SignupDate required date; Status one of Active, Paused, Closed.
Representative rows and known issues: C-001 | ANA@EXAMPLE.COM | United Kingdom | 3/4/26 | active; C-002 | blank | UK | 2026-04-03 | Paused; C-002 | ana2@example.com | GB | 04/03/2026 | Active; C-004 | bad-address | U.K. | blank | closed.

Example output

Blocking questions

Confirm the source locale for 3/4/26 and 04/03/2026, the authoritative record for duplicate C-002, and whether a missing signup date blocks import. These cannot be inferred safely.

Column	Evidence	Cleaning rule	Validation / exception
CustomerID	C-002 repeats	Trim only; do not merge	COUNTIF must equal 1; route duplicates to review
Email	uppercase, blank, malformed	Trim and lowercase; preserve blank	Basic structural check; flag bad-address
Country	United Kingdom, UK, GB, U.K.	Map confirmed aliases to GB	Reject unmapped values
SignupDate	ISO plus ambiguous numeric text	Preserve ISO; convert only after locale confirmation	Flag ambiguous and blank values
Status	case variation	Map case-insensitively to approved labels	Reject other values

Create RawValue, CleanValue, RuleApplied, and ReviewReason columns or a separate audit sheet. Duplicate the source sheet as Raw_ReadOnly, record row count and a checksum of CustomerID values, then clean helper columns in the order: keys → required fields → dates → controlled labels → optional fields. Import only rows with no ReviewReason. Reconcile final row count, unique key count, rejected rows, and each mapping total to the raw copy. Roll back by discarding the clean sheet; never overwrite Raw_ReadOnly.

Why this works

1
Profiling before mutation prevents cosmetic cleanup from changing business meaning.
2
Raw copies and audit columns make every transformation reviewable and reversible.