Why cleaning comes first
Analysis can only be as reliable as the data it uses. A duplicated record can inflate a total, a mistyped date can scatter a trend, and a missing value silently dropped can shift an average. People who work with data often say that cleaning takes up a large share of the effort, and that is typical because real data comes from many sources and human typing.
Cleaning does not mean making the data look nicer. It means finding where the data does not match reality or its own rules, and deciding carefully what to do about it.
Common problems in raw data
Duplicates
The same customer, order or event may appear more than once, perhaps because a form was submitted twice or two systems were merged. Exact duplicates are easy to spot. Near-duplicates, such as “J. Smith” and “John Smith” at the same address, take judgment.
Missing values
Cells may be blank, or hold placeholders such as “N/A”, “unknown”, “-” or “0”. A zero that actually means “not recorded” can distort results. Understanding why values are missing matters as much as filling them: a field may be blank because it was optional, because a system failed, or because it did not apply.
Inconsistent formats and labels
The same thing is written in several ways: “UK”, “U.K.” and “United Kingdom”; “Yes”, “Y” and “true”; dates as 03/04/2026 or 2026-04-03. The first date style is ambiguous, since it may mean 3 April or March 4 depending on the convention of the person who wrote it. Agreeing on one standard format avoids confusion.
Stray whitespace and capitalization
A trailing space makes “Leeds” and “Leeds “ look different to a computer. Differences in capital letters have the same effect.
Wrong data types
Numbers stored as text, dates stored as plain strings, or postal codes stored as numbers (losing leading zeros) all cause trouble. Sorting numbers stored as text puts “10” before “2”.
Impossible or suspicious values
Ages of 250, order dates in the future, negative quantities and totals that do not equal the sum of their parts all indicate errors, or at least need a check.
Outliers
An unusually large or small value may be a typing error, a real but rare event, or a hint at something important. Do not delete outliers automatically. Investigate first.
Structural problems
Several values may be packed into one cell, headers may repeat in the middle of a file, merged cells may hide data, or the same measurement may be spread across many columns. Rearranging data into one row per observation and one column per variable usually makes it far easier to work with.
A careful process
- Keep the original. Never overwrite the raw data. Work on a copy, or write cleaning steps as a script or query that produces a new, cleaned version. This keeps the work repeatable and lets you recover from mistakes. A solid backup strategy applies to data files too.
- Look before you change. Count rows, check column types, list distinct values for category columns, and view the minimum, maximum and typical values for numbers. A quick chart often reveals odd values faster than a table; chart selection can help you pick the right one for the check.
- Write down rules. Decide in advance how to treat blanks, duplicates, and conflicts. Record the rules so others can follow them.
- Fix systematically. Apply each rule to the whole dataset rather than editing cells by hand, where possible.
- Check the result. Compare row counts and totals before and after. If 1,000 rows became 940, you should be able to explain the missing 60.
- Keep a log. Note what changed, how many records were affected and why. Version control is a handy way to keep scripts and a change history, which is where version control becomes useful beyond software.
Handling missing data
There is no single right answer, and the choice can affect conclusions.
- Leave it as missing and let the analysis handle it, if the tools support that.
- Remove the rows when only a few are affected and the missing pattern appears random. Removing many can bias the results.
- Fill with a reasonable value, such as a typical value or a value carried from a related record. This is a form of estimate, so it should be flagged as such.
- Go back to the source and obtain the real value, which is often best when feasible.
Whichever you pick, state it in your findings.
Tools
The method matters more than the tool. Spreadsheets handle small jobs well, using sorting, filtering, text functions and find-and-replace. SQL can remove duplicates, trim spaces, standardize text and filter impossible values directly in the database, using the statements described in SQL queries. For repeatable work on larger files, a scripting language is common, and Python basics is a reasonable way into that. Dedicated data preparation tools also exist. Choose whatever lets you repeat the same steps next month.
Preventing the mess
Cleaning is cheaper when problems are stopped at the source. Dropdown lists instead of free text, required fields, validation of dates and numbers, and database rules like constraints and unique keys all reduce errors on entry. A well-structured design, as described in database normalization, also avoids a major cause of inconsistency: the same fact stored in several places.
Privacy while cleaning
Raw datasets often include personal details. Copying them into spreadsheets and emailing them around multiplies the risk. Work with the minimum fields needed, in line with data minimization, and be aware of what counts as personal data before sharing a cleaned file. If a task can be done on de-identified data, it usually should be.
Knowing when it is clean enough
Perfect data rarely exists. The goal is data that is fit for the question being asked. A rough monthly trend might tolerate small gaps that a financial reconciliation would not. Once the data is tidy, techniques such as statistical modeling become far more trustworthy, since the results rest on a more solid base.