Contact, if you are interested in this website / development / Sponsorship / Advertisement / Partnership →

The data cleaning checklist

Twelve checks to run on any dataset before you build a chart, a model or a decision on it.

Data.Studio Editorial · Updated November 2026 · 9 min read

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.

Do it in minutes: run your file through the Data Profiler to surface duplicates, gaps, stray spaces and outliers, then fix the common issues in the Data Cleaner, which produces a change log automatically.