Cleaning messy data
9 minutes · 5 steps · free, no sign-up
Data from forms, exports and other people arrives untidy: extra spaces, odd capitals, numbers that are really text, repeated rows. Clean data is what makes every formula in this course work, so a few reliable habits are well worth learning.
- Fix spaces and capitals with TRIM and PROPER
- Turn text such as "1,250" into a real number with VALUE
- Add up the cleaned numbers
- Remove duplicate rows
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=PROPER(TRIM(A2)) tidy spaces and capitals
=VALUE(B2) "1,250" becomes 1250
=SUM(D2:D4) add the clean numbers
Data > Remove duplicatesThe key ideasSummary: Cleaning messy data
Clean in a new column and leave the original untouched. That way you can check your work, and the formulas update if the source changes. Nest functions to do several jobs at once: PROPER(TRIM(A2)) removes the stray spaces and then fixes the capitals.
A number stored as text is the classic hidden problem. It looks right but is left-aligned, and SUM skips it. VALUE converts it. Finally, removing duplicates is best done once everything else is tidy, so that "Ada" and " ada" are recognised as the same entry.
Watch out forCommon mistakes
- Cleaning the original in place. Once you overwrite it, you cannot check what changed. Work in a helper column, then copy and paste values over if you must.
- Removing duplicates before tidying. Entries that differ only by a space look different to the sheet and survive.
- Trusting a column of numbers. If SUM gives 0 or looks short, some numbers are text. Left-aligned numbers are the clue.
- Using PROPER on everything. It turns "McDonald" into "Mcdonald" and "NASA" into "Nasa". Check names it might get wrong.
Next stepTry it in the real Google Sheets
In Google Sheets, Data → Data cleanup offers Trim whitespace, Remove duplicates and Find problems in one place. The formulas here do the same jobs and keep working when your data changes.