Every export is a crime scene
You pull a report out of the booking system, the CRM or the payment platform, and what lands in your downloads folder is not data. It is a rumour about data. The same customer appears as ACME Ltd, Acme Ltd. and acme ltd with two spaces in the middle. Half the dates are proper dates and half are text pretending to be dates. Row 412 is a header that got exported twice.
None of this is your fault, and all of it is your problem. The good news is that cleaning is the single most improvable spreadsheet task, because the fixes are boring, repeatable and easy to describe out loud.
In plain English
- Helper column:
- A spare column where you build the cleaned version, leaving the original column untouched.
- Trailing space:
- An invisible space at the end of a value. Two entries that look identical will not match if one has one.
- Paste values:
- Pasting the result of a formula rather than the formula itself, so the number stays put.
- Text to Columns:
- A built-in menu tool that splits one column into several at a chosen character, such as a comma.
- Duplicate:
- A row that repeats an earlier one. Sometimes an error, sometimes two genuinely separate orders.
Rule one: never clean the original
Before you change a single cell, duplicate the sheet and rename it. Call the copy Working and leave the export exactly as it arrived. When you discover in an hour that the clean-up quietly ate 40 rows, the untouched original is the only thing that lets you prove it and start again.
Do the cleaning in helper columns next to the original, not on top of it. Once you are happy, copy the helper column and paste values over the original. Overwriting first and checking later is how good afternoons end badly.
Capitalisation and stray spaces
This is the easiest win in the whole course. Trailing spaces are the reason your lookup says Not found and your pivot table lists the same region twice.
=PROPER(TRIM(A2))
TRIM removes spaces at the ends and squashes doubles in the middle. PROPER puts a capital on each word. Both work in Excel and Google Sheets. Use UPPER instead of PROPER for things like region codes, and leave PROPER alone for anything containing McDonald or O'Brien, because it will confidently produce Mcdonald.
โ Weak prompt
Prompt
Clean up this data please.
Output
Sure, here is your data with some formatting applied.
Clean means nothing on its own. You get changes you did not ask for and cannot audit, and you will not know what moved.
โ Good prompt
Prompt
Column B is Company Name, rows 2 to 900, from a CRM export. It has inconsistent capitalisation, extra spaces and a few trailing full stops. Give me a formula for a helper column that standardises it, and tell me the risks of each step. Do not change my original column.
Output
One helper-column formula, an explanation of each part, and a note that automatic capitalisation will damage names like McKenzie.
Named column, named row range, named problems and a request for the risks. You keep control of what changes.
Checkpoint
Work on a copy, clean in helper columns, and only paste values over the original once you have checked the result. The untouched export is your undo button.
Dates that are not dates
The classic disaster. Some of your dates are real dates and some are text, which is why sorting by date produces something that looks like it was shuffled. The quick test: real dates line up to the right of the cell by default, text lines up to the left.
Column A contains dates from an export, rows 2 to 900.
Some are real dates and some are text. I can see these formats:
[03/04/2026], [4 Mar 2026], [2026-03-04].
I am in [Excel / Google Sheets] and my region uses [day first / month first].
Give me a helper column formula that turns all of them into a real date.
Flag anything ambiguous rather than guessing at it.
That last line matters more than the rest. The value 03/04/2026 is the third of April in London and the fourth of March in New York, and no formula can tell which one your export meant. If a whole column is ambiguous, go and find out how the source system writes dates before you convert anything.
Names split across columns, or not split enough
Sometimes you have one Full Name column and need two. Sometimes you have First and Last and need one. Both are simple, with one wrinkle: people have middle names, double-barrelled surnames and prefixes.
=TRIM(B2 & " " & C2)
Splitting is where the tools differ. Google Sheets has a SPLIT function, Microsoft 365 has TEXTSPLIT, and older Excel has neither. All three have Text to Columns in the Data menu, which is usually the fastest route anyway. Ask your AI assistant for the menu steps rather than a formula:
Column C is Full Name, rows 2 to 900, in [Excel / Google Sheets].
I need First Name and Last Name in separate columns.
Give me the menu steps, not a formula.
Then tell me which rows will break the rule, for example three-word names,
missing surnames, or a company name sitting in a person field,
and give me a way to list those rows so I can fix them by hand.
Duplicates
Both Excel and Google Sheets have a remove-duplicates command in the Data menu, and both will happily delete rows you wanted. Never run it as your first move. Flag first, look, then decide.
=COUNTIF(B:B, B2)
Anything above 1 appears more than once. Now sort by that column and actually read the repeats. Two identical orders from the same customer on the same day might be a double-entry error, or might be a customer who genuinely ordered twice. Only you know your business. The spreadsheet certainly does not, and neither does Gemini.
Before and after every clean-up step, note the row count. If you started with 900 rows and finished with 847, you need to know where the other 53 went before you tell anyone what the numbers say.
๐ Quiz
Question 1 of 4What is the first thing to do before cleaning an export?