Stop 02 · Excel & SpreadsheetsTopics · 5/10

Data Cleaning in Excel

In short

Real data is messy: extra spaces, 'Delhi' and 'delhi ', duplicate rows, dates stored as text. If you analyse it as-is, your totals will be wrong and nobody will tell you.

Using TRIM, TEXT-to-COLUMNS, Find & Replace, and Remove Duplicates to fix messy real-world data before any analysis is trustworthy.

The jaadu picks small stones out of a big plate of rice.

Think of it like this

Picking stones out of rice

Before cooking, you spread the rice on a plate and pick out small stones and husks. Skip it and someone bites a stone at dinner. Cleaning data is the same patient step before the real cooking, and the guests only notice when you skip it.

Watch it happen

Cleaning one messy city column

Delhi

delhi

DELHI

Mumbai

N/A

1/5Six rows that should be two cities. A PivotTable would show five different 'cities' here.

The big ideas

What you need to know

TRIM and CLEAN

TRIM removes extra spaces, CLEAN strips hidden characters that sneak in from copied web data.

Consistent text

PROPER, UPPER or LOWER make 'delhi' and 'DELHI' the same value.

Remove Duplicates

Data tab > Remove Duplicates. Choose which columns define a duplicate, like Order ID.

Fix text dates

Dates stored as text won't sort properly. Text to Columns or DATEVALUE turns them into real dates.

See it in code

Walk through the code

excel

1=TRIM(B2)2=PROPER(TRIM(B2))3=VALUE(SUBSTITUTE(C2, ",", ""))4=IF(B2="N/A", "", B2)5=COUNTIF(A:A, A2)>1

1/4Strip spaces, then fix capital letters in one go.

Put these in helper columns, check them, then paste as values over the original. Keep a copy of the raw data.

Save yourself the pain

Common mistakes

Oops

Cleaning directly on the only copy of the raw data.

Do this instead

Keep the raw sheet untouched. Clean in a copy or helper columns so you can always go back.

Oops

Removing duplicates on every column without thinking.

Do this instead

Decide what makes a row unique, usually an ID. Two different orders can share the same amount and date.

Your mission

Clean a messy list

~30 min

Quick check

A PivotTable shows 'Pune' and 'Pune ' as two separate rows. What fixes it?

Go deeper

1Take a deliberately messy dataset (extra spaces, inconsistent casing) and clean it fully.
2Learn TRIM, CLEAN, and PROPER/UPPER/LOWER for text normalization.
3Practice using Text-to-Columns to split a combined field into separate columns.