Why the file is always dirty

Every real dataset arrives broken, and it is broken in predictable ways. Knowing the list turns cleaning from a search into a checklist.

Humans typed it. "Lahore", "lahore", "LHR", "Lahor", " Lahore" are five values and one city. Any column a person typed by hand will have this.

Dates are the worst column in every file. 03/04/2026 is two different days depending on who typed it. Excel silently converts anything that looks like a date, which is how product codes become dates and never come back.

Numbers stored as text. Because someone typed "1,200" or "Rs 1200" or "1200 " with a trailing space. They look right, they sum to zero.

Missing data pretending to be present. Empty cells, "N/A", "-", "0", "unknown", "NULL" as text, and a space. Each means something different and a zero in a price column will quietly move your average.

Duplicates that are not identical. The same customer twice with a different spelling, so no exact match finds them.

Merged cells and headers in row seven, because the file was made to be looked at rather than processed.

Trailing spaces, invisible and responsible for more wasted hours than anything else on this list.

Before you change a single cell, do two things. Copy the raw file and never touch the copy. And write down the row count. Every step afterwards, check that number. Cleaning that silently deletes forty rows is worse than not cleaning at all.