Clean up a messy spreadsheet
How do you clean duplicates, inconsistent dates, and blanks without corrupting the data?
Shared spreadsheets often arrive with duplicate rows, mixed date formats, inconsistent units, and blank cells. AI is helpful for standardizing formats and spotting anomalies, but it must never guess missing values.
Merge duplicates with a log, standardize ISO dates, separate notes, and keep blanks explicit
Never silently alter data: log every merge/split and keep blank dietary cells as “To Confirm”.
R1 + R2 (Lin Xiao) → 1 Valid Registration
Merge duplicate submission and record R1/R2 consolidation in the cleaning log.
ISO Dates (YYYY-MM-DD) · Keep Dietary “To Confirm”
Standardize mixed dates to YYYY-MM-DD; do not invent “No restriction” for R3.
Row R4 “Budget Cap CNY 2,000” → Move to Notes
True signup count is 2 people (Lin, Zhou), not 4 raw spreadsheet rows.
The method: three small steps
Follow three rules when cleaning tables: log every deduplication, standardize date and unit formats, and keep blank or unconfirmed cells explicitly marked rather than filling them with guessed defaults.
Spot duplicate rows and mixed record types
R1 and R2 are duplicate signups by Lin Xiao on November 1, leaving 2 valid registrants (Lin Xiao and Zhou Ning), not 3; R4 is a budget cap note and must be separated from attendee rows.
Standardize dates and units
Convert “2026/11/01” and “Nov 2” to “2026-11-01” and “2026-11-02,” and keep headcount as clean numbers for calculation.
Keep blanks unconfirmed and log every change
Mark Zhou Ning’s blank dietary field as “to confirm” instead of assuming “none,” and attach a short change log tracing raw rows to cleaned rows.
WORKED EXAMPLE
See how to improve a result
Prepared example: deduplicate with a log and keep blanks honest
The requests and responses are prepared teaching examples, not live model output.
Read the example sources
Source A · Event notice
The team exchange event is scheduled for November 6, 14:00–16:00, in the third-floor meeting room. Sign up by November 3, 18:00. The target is 20 attendees; actual registrations have not yet been counted. No registration method, link, or event contact is provided.
Source B · Budget and equipment
The budget cap is CNY 2,000. No actual spending has been recorded. A projector is available in the meeting room.
Source C · Preparation notes
Catering and remote access are unconfirmed. Lin will organize the agenda before November 2; Zhou will confirm the attendee list before November 3. The photography owner and deadline remain unconfirmed.
The request
Clean these four signup and budget rows into a neat table and fill in any blanks.
Example response
Cleaned 3 registrants: Lin Xiao (2 people), Zhou Ning (1 person, no dietary restrictions), and CNY 2,000 spent.
What to noticeThis is a prepared problem example. It counts a duplicate row as a second person, invents “none” for a blank dietary field, and turns a budget cap into money spent.
Your turn: what would you ask next?
Try it yourself with different material
Try another sheet: clean an equipment loan log
Copy the material and task into your usual tool to practice. Then review your work against the checks.
Material
Fictional rows: L1 | Chen Mo | Projector A | 2026.12.01 | 1 unit | Status: returned L2 | Chen Mo | Projector A | 2026-12-01 | 1 | Note: duplicate click L3 | Xu Yan | Recorder B | Dec 2 | 1 unit | Status: (blank)
Task
Produce the 2 cleaned loan rows with standardized dates/units, plus a change log and follow-up question.
Review your work
- Merge duplicate rows L1 and L2 so valid loans equal 2, not 3.
- Standardize dates to 2026-12-01 and 2026-12-02.
- Keep Xu Yan’s return status as “to confirm” rather than marking it returned.
Inspect the four raw table rows in the workspace, choose rules for deduplication, formatting, and blanks, and write the cleaned table summary with a change log.
These materials describe a fictional event. No AI model is called. Selections are checked against explicit rules; you review your own writing against the sources.
Read the original exercise materials
The team exchange event is scheduled for November 6, 14:00–16:00, in the third-floor meeting room. Sign up by November 3, 18:00. The target is 20 attendees; actual registrations have not yet been counted. No registration method, link, or event contact is provided.
The budget cap is CNY 2,000. No actual spending has been recorded. A projector is available in the meeting room.
Catering and remote access are unconfirmed. Lin will organize the agenda before November 2; Zhou will confirm the attendee list before November 3. The photography owner and deadline remain unconfirmed.
Clean the messy table with a change log
R1 | Lin Xiao | Product | 2026-11-01 | 1 person | Dietary: none
R2 | Lin Xiao | Product | 2026/11/01 | 1 | Note: duplicate submission
R3 | Zhou Ning | Operations | Nov 2 | 1 person | Dietary: (blank)
R4 | Budget note | — | — | CNY 2,000 (budget cap) | Actual spend: (unrecorded)
Take your work with you
Drafts stay only on this page and are not restored after refreshing or leaving. Copy or download what you want to keep. Switching language does not delete your writing.
Standardize formats and merge duplicates with a clear log, and never fabricate values for blank cells.
Try it in your own AI tool
- Open an AI chat tool you already have access to, start a new conversation, and paste the complete task sheet into its input box before sending. Use only the fictional text here; no work files, passwords, or personal details are needed.
- Verify that total valid registrants equal 2 and that R4 is separated from the attendee list.
- Check that Zhou Ning’s dietary field remains “blank / to confirm” rather than auto-filled as “none.”
- Save this “Cleaned Table + Separated Exceptions + Change Log” prompt for routine spreadsheet cleanup.
Complete the checks above to record your progress. This records example practice; try a different task independently to test your skill.
Explanations, cases, and exercises are authored by Reallier. Links provide further learning. The workplace activity and people are fictional.