Why cleaning comes first
Any total, chart or summary is only as reliable as the rows underneath it. If a customer appears twice, a date is stored as text, or a column has blanks where numbers should be, the results can be wrong while still looking perfectly plausible. That is the danger: nothing flashes a warning. Spending a little time on cleaning before analysis usually saves far more time than hunting for the source of a strange total afterward.
Cleaning is also not a one-off chore. Data from forms, exports and manual entry arrives messy in similar ways each time, so a repeatable routine pays off.
Step 0: keep the original
Before changing anything, save an untouched copy of the raw data, either as a separate tab or a separate file with a clear name. Then do your cleaning on the copy. If a fix turns out to be wrong, you can compare against the original or start again. This is a small habit with large protection, and it fits with the wider idea of keeping earlier states recoverable through version history.
Also write down what you did. A short list of cleaning steps in a notes tab lets you, or a colleague, repeat the process next month.
Step 1: look before you fix
Start by scanning the data. Sort each column in turn and look at the top and bottom, where oddities collect. Filter a column to see its unique values; a “Status” column with “Done,” “done,” “DONE “ and “Complete” is telling you something. Count the rows and note how many you expect.
Look for these common problems:
- Duplicate rows or near-duplicates.
- Inconsistent spelling and capitalization.
- Extra spaces at the start or end of text.
- Dates stored as text, or in mixed styles.
- Numbers stored as text, sometimes with currency symbols or commas typed in.
- Blank cells and placeholder values such as “N/A,” “unknown” or “0.”
- Several facts squeezed into one cell, such as a full name and a phone number together.
Step 2: standardize formats
Make each column hold one type of value in one style.
Text. Trim stray spaces and pick one capitalization approach. Many tools have functions for trimming and for changing case. Use them on a helper column and then paste the results back as values, so the formulas do not remain.
Dates. Convert text dates to real dates, using a single format. Be careful with ambiguous entries: “03/04/2026” can mean March 4 or April 3 depending on region. If you are not sure which way the source wrote them, check against rows where the day is above 12 or ask the person who supplied the file.
Numbers. Remove symbols and thousands separators that make a number into text, and confirm that the column now adds up. A quick test is to compare a sum against a count of rows times a rough average.
Categories. Decide on a short, agreed list of values and map the variants onto it. “NY,” “New York” and “new york” should become one value. Later, rules that stop bad entries at the source reduce how often you need to do this.
Step 3: handle duplicates carefully
Exact duplicates, where every cell matches, are the easy case, and most spreadsheet tools have a feature to remove them. Even so, check what it kept. Near-duplicates are harder: “Jon Smith” and “John Smith” at the same address might be one person or two. Decide what identifies a record, such as an order number or email address, and flag duplicates on that column rather than deleting them immediately.
Before deleting, ask whether a repeated row might be legitimate. Two purchases of the same item on the same day could be real. Removing true records changes totals just as keeping false ones does. If you are cleaning contact lists, the question of what a contact database should contain helps decide which fields really define a unique person.
Step 4: decide what to do about blanks
A blank cell can mean “unknown,” “not applicable,” “zero” or “someone forgot.” These are not the same, and different choices change results. Some options:
- Leave it blank when blank honestly means unknown, and make sure your formulas treat blanks as intended.
- Fill with a clear label such as “Not provided” for text fields, so a blank cannot be mistaken for an oversight.
- Fill from another source when you have a reliable one.
- Exclude the row from a specific calculation, noting how many rows were dropped.
Avoid filling with an average or a guess unless you are clear about why and willing to say so. As a hypothetical illustration, if 3 of 20 survey rows lack an age and you drop them, you are summarizing 17 rows, and the summary should say so.
Step 5: check the result
After cleaning, check that nothing important was lost or distorted.
- Compare row counts before and after, and account for each difference.
- Re-sort and re-filter each column to confirm the values look consistent.
- Recompute a total or two and compare it with a figure you trust, such as a known monthly total from another source.
- Spot-check a handful of rows against the raw copy.
Working with personal information
Cleaning often involves names, emails and other personal details. Work on the minimum copy you need, store files where only the right people can open them, and delete temporary copies when finished. Good handling of customer data does not stop once the analysis begins.
Make the next cleanup smaller
The best cleaning is the kind you do less often. If the data comes from a form, make fields consistent at the source. If it comes from people typing, offer drop-down lists. If it comes from an export, ask whether the settings can produce cleaner output. Time spent upstream usually makes this article’s steps shorter every time you apply them.