Skip to content

Module 01 · Lesson 04

Clean the data first

Tidy text before you make it pretty.

Excel basics6 min
ToolWhat it doesExample
=TRIM(B2)Removes extra spaces before, after and between words.“ mesh short” › “mesh short”
=PROPER(B2)Capitalises Each Word.“glass cutter” › “Glass Cutter”
Ctrl+E · Flash FillType the first answer by hand in the next column, press Ctrl+E: Excel copies your pattern.Splitting or joining names
Ctrl+H · ReplaceFind a mistake once, replace it everywhere.“srews” › “screws”
Conditional formattingHome › Conditional Formatting › Highlight Cells Rules › Duplicate Values.Shows Safety boots listed twice
Paste ValuesAfter TRIM/PROPER, copy the new column and paste as values (Ctrl+Alt+V › V), then delete the helper column.

Your turn

Try it on the practice file

Clean the Item Description column, then fix the row with “long” in Quantity.

  1. TRIM and PROPER into a helper column
  2. Paste values back, delete the helper
  3. Fix 8Mm / 3Mm with Ctrl+H
  4. Highlight duplicates and decide which to keep