Skip to main content
Back to module
Spreadsheets for Data Work · Working with Data

Cleaning messy data

Reading54mLesson 3 of 3 in this module

Fix stray spaces, inconsistent text, numbers stored as text, mixed dates and duplicate rows before you analyse anything.

Why it matters

Data exported from forms, instruments, other people's spreadsheets or old systems is almost never clean. A single trailing space makes SUMIF miss a row; a quantity stored as text drops out of every total; a duplicated row doubles a cost. Analysts often say most of the work is cleaning. The goal is a table that follows the tidy rules from the first lesson, without losing or inventing data.

Golden rules

  1. Keep the raw data. Copy it to a sheet named Raw and never edit it. Do all cleaning on a copy or in helper columns, so you can always check or redo your work.
  2. Fix with formulas, then freeze. Build a cleaned column with formulas, check it, then Paste special > Values only (Ctrl+Shift+V in Sheets and Calc; Excel: Paste > Values) to replace the formulas with fixed values.
  3. Write down what you changed in a short Notes sheet: "Removed 1 duplicate row (ID 7)", "Converted dates in column A to ISO".

Text: spaces and case

ProblemFix
" Safety " (extra spaces)=TRIM(C2) removes leading/trailing spaces and collapses double spaces
safety, SAFETY=PROPER(C2) gives Safety; also UPPER and LOWER
Hidden line breaks or odd characters=CLEAN(C2) removes non-printing characters
Wrong word=SUBSTITUTE(C2, "Consumable", "Consumables")

Combine them: =PROPER(TRIM(C2)). Google Sheets also has Data > Data cleanup > Trim whitespace, and Edit > Find and replace (all three tools) is fine for one-off fixes.

Numbers stored as text

Signs: left-aligned numbers, a small green or warning triangle, COUNT lower than COUNTA, or a SUM that looks too small. Causes: a leading apostrophe, a space, a currency symbol typed as text, or a comma used as a decimal separator.

Fixes:

=VALUE(TRIM(D2))                 "10 " -> 10
=VALUE(SUBSTITUTE(E2, "$", ""))  "$8.50" -> 8.5

Then paste the results back as values and set a number format.

Dates

Mixed formats such as 2026-09-02, 09/03/2026 and 3 Sep 2026 in one column are risky: 03/09/2026 is 3 September in most of the world and 9 March in the US. You must know the source's convention; a formula cannot guess it.

  • Check each cell's real type: a true date is right-aligned and changes when you apply a date format.
  • =DATEVALUE(A2) converts date text into a real date using your spreadsheet's locale (File > Settings in Sheets, Tools > Options > Language Settings in Calc).
  • For unambiguous text like 2026-09-02, =DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)) builds a date from its parts.
  • Finally format the whole column as yyyy-mm-dd.

Duplicates

First decide what a duplicate is. Two orders of 10 gloves on different dates are both real. The same date, item, quantity and cost entered twice is probably a duplicate.

  • Find them: in a helper column, =COUNTIFS(A:A, A2, B:B, B2, D:D, D2) shows how many rows share those values. Anything above 1 needs a look.
  • Remove them: Sheets: Data > Data cleanup > Remove duplicates. Excel: Data > Remove Duplicates. Calc: select the table, Data > More Filters > Standard Filter, set a condition every row meets (for example Date = Not Empty), open Options, tick No duplications and Copy results to a new location.
  • =UNIQUE(B2:B11) lists distinct values (Sheets, Excel 365, Calc 24.8+), handy for checking a category column for spelling variants.

Worked example

Raw rows:

Date,Item,Category,Qty,UnitCost
2026-09-02,Nitrile gloves (box), safety ,10,$8.50
09/03/2026,Pipette Tips (rack),Consumables,25 ,4.20
2026-09-02,Nitrile gloves (box), safety ,10,$8.50

After cleaning: Category =PROPER(TRIM(C2)) gives Safety; Qty =VALUE(TRIM(D2)) gives 25; UnitCost =VALUE(SUBSTITUTE(E2,"$","")) gives 8.5; the second date becomes 2026-09-03 (US source, documented in Notes); item names are standardised to the Items table spelling; and row 3 is removed as an exact duplicate of row 1.

Common mistakes

  • Cleaning the only copy of the data.
  • Removing "duplicates" on one column (Item) and deleting real repeat orders.
  • Leaving formulas that reference the raw sheet, then deleting the raw sheet.
  • Silently guessing ambiguous dates or blank values instead of checking or flagging them.

Quick recap

  • Keep raw data untouched; clean in helper columns; paste as values; log changes.
  • TRIM, PROPER, CLEAN, SUBSTITUTE fix text; VALUE fixes numbers stored as text; DATEVALUE or DATE fixes dates.
  • Define a duplicate before removing it; COUNTIFS finds them, Remove duplicates deletes them.

Try it

Open the Do tab to clean a deliberately messy export.

Also in this lesson: