Guide

The Data Cleaning Checklist: From Messy CSV to Analysis-Ready

Most analysis problems are really data problems. A chart that looks wrong, a total that does not match finance, or a model that behaves strangely usually traces back to duplicates, inconsistent labels or a column of numbers stored as text. Cleaning is not glamorous, but doing it in a fixed order saves hours and prevents embarrassing mistakes.

Here is a checklist you can run on any CSV or spreadsheet export before you analyze it.

Before you start: keep the raw file

Save an untouched copy of the original export and work on a duplicate. Write down every cleaning step you take — in a notes tab or a script — so the result can be reproduced and explained.

1. Check the structure

• One header row, with unique, descriptive column names.
• One record per row; no subtotals, blank spacer rows or notes at the bottom.
• No merged cells.
• Each column holds one kind of information (not “City, State” in one cell if you need them separately).

2. Remove exact duplicates

Duplicates appear when exports are merged, forms are submitted twice or systems sync badly. Remove rows that are identical across all columns, then look for near-duplicates — the same email address with different capitalization, for example. For a quick check on a single column, paste it into the remove duplicates tool and compare the unique count with the original.

3. Clean text

• Trim leading and trailing spaces — “Pro ” and “Pro” will not match.
• Remove invisible characters such as non-breaking or zero-width spaces copied from web pages. The text cleaner handles these.
• Standardize case where it should not matter (emails, country codes).
• Standardize categories: “US”, “U.S.”, “USA” and “United States” should become one value.

4. Fix data types

• Numbers stored as text — often caused by currency symbols, thousands separators or trailing spaces. Strip them and convert.
• Dates — pick one format (ISO 8601, YYYY-MM-DD, is the safest) and convert everything to it. Watch for day/month swaps between US and international formats.
• Identifiers — keep ZIP codes, phone numbers and product codes as text so leading zeros survive.

5. Handle missing values deliberately

Find blanks and placeholders such as 0, 999, “N/A” or “-“. Then decide, column by column:

• Leave blank and exclude from calculations (most common and most honest).
• Fill with a known value when the meaning is clear (an empty discount field means no discount).
• Drop the row if a critical field is missing and the row cannot be used.

Never let a placeholder like 0 or 999 sit in a numeric column — it silently distorts averages.

6. Check ranges and outliers

Summarize each numeric column — count, min, max, mean, median — with the dataset summary tool. Look for impossible values (negative ages, dates in the future, a 10,000% discount). Then look at extreme but possible values: a huge order may be real or a typo. Investigate before deleting; outliers are sometimes the most important rows.

7. Validate relationships

• Totals match the source system (revenue in your file equals revenue in the finance report for the same period).
• Logical rules hold: end date after start date, quantity × price equals line total.
• Foreign keys match: every order’s customer ID exists in the customer table.

8. Document and save the clean version

Save the cleaned file with a clear name and date, alongside a short note of what changed: rows removed, values standardized, assumptions about missing data. Anyone who reads your analysis later — including you — will need this.

Quick checklist

1. Raw copy saved
2. Structure: one header, one record per row, no merged cells
3. Duplicates removed
4. Text trimmed and standardized
5. Numbers, dates and IDs in correct types
6. Missing values handled consciously
7. Ranges and outliers checked
8. Totals and relationships validated
9. Steps documented

Once your data is clean, the next step is usually converting or visualizing it — see our guides to CSV to JSON conversion and choosing the right chart.