Analysts often joke that 80% of the job is cleaning data. The exact number is debatable, but the cost of skipping it isn't. A single duplicated import or a currency mix-up can make a dashboard confidently wrong. Work through these checks in order. Each one builds on the last.
Structure
1. One table, one grain
Decide what one row represents: an order, an order line, a customer-day. Mixed grains (some rows are orders, others are line items) silently double-count. If you find subtotal or "Total" rows exported from a spreadsheet, remove them.
2. Headers are clean and unique
Every column needs a single-row, unique, descriptive name. Watch for merged header cells, trailing spaces ("Email ") and duplicates like two columns called Date. Consistent snake_case names make later work easier.
3. The data is "tidy"
Each variable should be a column, and each observation a row. A spreadsheet with months as columns (Jan, Feb, Mar…) is wide. Reshape it to long format (month, value) for most analysis and charting tools.
Completeness & uniqueness
4. Duplicates are found and explained
Check for exact duplicate rows first, then for duplicates on the business key (the same order ID appearing twice with different values). Exact duplicates are usually import errors. Key duplicates need investigation: is one an update, a refund or a genuine second record?
5. Missing values are standardised and understood
Missing data hides in many disguises: blanks, NA, N/A, null, -, 0, 9999, 1900-01-01. Convert genuine "unknowns" to one consistent empty value, but never turn a real zero into a blank. Then ask why values are missing. Missing at random is harmless. Missing for a reason (for example, only cancelled orders lack a delivery date) changes your analysis.
6. Primary keys are actually unique
If a column should identify each row (customer ID, SKU), confirm its distinct count equals the row count. If it doesn't, joins will multiply rows unexpectedly.
Validity & consistency
7. Data types match meaning
Numbers stored as text won't sum. Text stored as numbers loses leading zeros (ZIP codes, phone numbers, product codes). Dates stored as text sort alphabetically, so "10/1/2026" lands before "9/1/2026". Fix types deliberately, column by column.
8. Categories are consistent
"USA", "U.S.", "United States" and "united states " are four groups to a computer. Trim spaces, standardise casing, and map variants to a canonical list. A frequency table of each category column is the fastest way to spot them.
9. Dates and time zones are unambiguous
Is 03/04/2026 March 4 or April 3? Convert everything to ISO format (2026-03-04). For timestamps, record the time zone or store in UTC. Daily totals shift when events near midnight land in a different zone.
10. Units and currencies are aligned
Mixed units are the most expensive error on this list. Look for columns combining dollars and euros, grams and kilograms, or monthly and annual figures. Add a unit column if needed, convert, and note the exchange rates and dates you used.
Plausibility
11. Outliers are investigated, not auto-deleted
Use the interquartile-range rule or simple sorting to find extreme values. Then classify each one: error (a $1,000,000 coffee is a typo, so fix or remove it), legitimate extreme (a real enterprise order, so keep it and consider reporting medians), or different population (internal test accounts, so filter them out). Write down which was which.
12. Totals reconcile with a trusted source
Compare row counts and key totals with the source system or finance figures. If your cleaned revenue is 3% off the accounting system, find out why before anyone sees a chart.
Document as you go
Keep a short cleaning log: what you changed, why, and how many rows were affected. It makes your work reproducible, defensible in meetings, and much faster to redo when next month's export arrives. Better still, automate the steps so they run the same way every time.