Cleaning Ugly Spreadsheet Data With Gemini (The Prompts That Actually Work)
2,000 rows of inconsistent names, dates, and addresses. The Gemini prompt patterns that turn a cleanup week into a cleanup hour.
Every spreadsheet imported from the real world is a crime scene: dates in three formats, company names spelled four ways, addresses that make the mail column useless. Gemini inside Sheets handles this well, but only if you prompt it like a data person, not a wisher.
The first pattern is the standardize-and-show prompt: 'In column B, normalize every date to YYYY-MM-DD, and show me the five rows you changed most confidently plus any you could not parse.' The show-me clause is the safety net. You are not asking for blind transformation; you are asking for a proposal with evidence, which you spot-check before accepting.
The second pattern is the mapping table, which handles messy names better than any formula. 'Build a mapping of every variant of company name in column A to the most common canonical form, list it for my approval, then apply it.' Approving a 40-row mapping table takes two minutes and catches the merge errors that blind find-and-replace ships to production.
The third pattern is extraction from chaos. Free-text address cells become structured columns with: 'Extract street, city, postal code, and country from column C into new columns; leave blanks where unsure rather than guessing.' The leave-blank instruction is doing real work; models want to be helpful, and a guessed postal code is worse than an empty one.
The hour-long cleanup that used to be a week ends with a final audit prompt: 'Which columns still contain inconsistencies or blanks worth my attention, ranked by how much they would break a mail merge?' Gemini is good at cleanup and better at telling you what it could not clean, which is the part your tools never did.