Why cleaning comes first
Spreadsheets happily calculate with whatever you give them. They do not know that “Smith, J.” and “J Smith” are the same person, or that a number typed with a trailing space is not really a number. The result is totals, counts and charts that look authoritative but are quietly wrong. Cleaning data means finding and fixing these problems before you draw conclusions. It is rarely exciting, but analysts often say it takes up a large share of their time, and skipping it is a common reason results cannot be trusted.
Protect the original first
Before changing anything, make a copy of the file or keep the raw data on its own sheet and do the cleaning on a duplicate. Mistakes happen, and a clean version is useless if you cannot check it against the source. This is a good time to apply routine file backups and sensible file naming, for example by adding a date to the copy. Also keep a short log of what you changed and why. If you ever have to explain a figure, that log will be valuable.
Look before you fix
Start by scanning the data. Sort each column, look at the smallest and largest values and at any odd entries. Try filtering a column to see its list of distinct values; misspellings and variants often show up right away. Counting the rows and checking that the count matches what you expect is another quick test. The aim is to understand the kinds of problems you have before deciding how to handle them.
Common problems and fixes
Duplicates
The same record can appear more than once, for example after combining two lists. Decide what counts as a duplicate: an identical row, or the same order number, or the same email address. Many spreadsheet programs offer a way to highlight or remove duplicates. Highlighting first is safer, because you can review them. Not every repeated value is an error. Two real customers can share a name, so check a unique identifier where one exists.
A hypothetical example: a list of eight sign-ups contains two entries repeated exactly. After removing the repeats, six unique records remain. If you had counted the original list, you would have overstated sign-ups by two.
Inconsistent spellings and capitalization
“North”, “north”, “NORTH” and “Nrth” will be treated as four separate groups in a summary. Pick a standard form for each category and apply it consistently. Functions that change capitalization can handle simple cases, while find-and-replace works for known variants. For fields where people typed freely, such as survey answers, you may have to review the distinct values by hand. This is also a reminder that using dropdown lists or fixed choices when collecting data prevents many problems later, an idea that carries over to survey design.
Stray spaces and invisible characters
Extra spaces at the beginning or end of a cell are invisible but make “North” and “North “ different. A trimming function removes leading, trailing and repeated spaces. Non-printing characters from copied web text or imported files can also cause trouble; if a trimming function does not fix a mismatch, look for characters that are not normal spaces.
Numbers stored as text
If a column of numbers will not add up, or it aligns to the left while true numbers align to the right, the values may be stored as text. Causes include currency symbols typed in as text, apostrophes, spaces or imported formats. Converting them to real numbers, after removing the extra characters, usually solves it. Check the totals again afterward, comparing against a sum of a few values worked out by hand.
Blank cells
Blanks can mean different things: the value is unknown, not applicable or simply forgotten. Treat them with care.
- Do not fill blanks with zero unless zero is really the right value. A blank price and a price of zero are very different.
- Some calculations skip blanks, and others do not. Averages ignore blank cells but treat a typed zero as a value, so filling with zeros changes the result.
- If a blank can be recovered from another source, fill it in and note where it came from. Otherwise leave it blank, or mark it clearly, and say how you handled it.
Dates and formats
Dates typed in mixed formats, such as day-month versus month-day, are a known source of confusion. If the order is ambiguous, check against what you know of the data, and convert everything to one clear format. Units can also vary, like some weights in pounds and some in ounces. Put the unit in the column heading and convert as needed.
Impossible or suspicious values
An age of 250, a date in the future for a past event or a negative quantity may be typing errors. Check them against the source if possible. Unusual but plausible values should not be removed just because they are inconvenient; they might be real, and they can matter most.
Work in small, repeatable steps
- Copy the raw data.
- Trim spaces and standardize capitalization.
- Standardize categories, dates and units.
- Convert text numbers to numbers.
- Review and remove duplicates.
- Decide how to handle blanks and suspicious values, and document the decision.
- Re-check row counts and a few totals against the original.
After cleaning, sorting and filtering to look at subsets, and summaries such as pivot tables, will be more trustworthy.
Be careful with personal information
Datasets sometimes include names, emails or other personal data. Cleaning does not change the need to handle it responsibly. Keep working copies in appropriate places, share only what others need and think about sharing permissions before sending a file. If the analysis does not need identifying details, consider removing those columns from the working copy.
Know when it is clean enough
Perfect data is rare. The goal is to be confident that remaining problems are too small to change your conclusions, and to be honest about any that are left. A short note beside your results stating what was cleaned, what was left and what assumptions you made makes the work much easier for others to trust and for you to revisit later.