Data Cleaning Checklist: 15 Steps Before You Analyse Anything

A data cleaning checklist is a fixed list of checks you run on every dataset before analysing it: understand the columns, keep a raw copy, fix types, remove duplicates, handle blanks, standardise text and dates, check ranges and outliers, validate joins, then reconcile totals and write down what you changed. Running the same 15 steps every time catches the errors that otherwise end up in a report your manager trusts.

This checklist works whether you clean in Excel, SQL or Python. Each step says what to look for and how to fix it, and it follows the order we teach in our Data Analysis course.

Quick answer: Before any analysis: (1) understand the source, (2) keep the raw file untouched, (3) check rows, columns and types, (4) remove duplicates, (5) handle missing values, (6) trim and standardise text, (7) fix dates, (8) align units and currencies, (9) check ranges, (10) look at outliers, (11) validate IDs, (12) test joins, (13) reconcile totals, (14) document every change, (15) automate the steps you will repeat.

Why a data cleaning checklist matters

Dirty data rarely looks dirty. A sales total can be wrong because of one duplicated export, “Kolkata” and “kolkata “ counted as two cities, or dates read as text and silently dropped from a monthly chart. None of these throw an error. The only defence is to check deliberately, in the same order, every time.

A checklist also makes your work defensible. When someone asks “how do you know this number is right?”, you can answer with what you checked and what you changed.

Before you touch anything

Step 1: Understand where the data came from

Ask who produced it, from which system, for what period, and what one row represents (one order? one order line? one customer per day?). Getting the grain wrong is the root of many double-counting mistakes.

Step 2: Keep a raw copy

Never clean the only copy. Save the original export untouched, and do your work on a copy, in Power Query, in a SQL view or in a script. If a step goes wrong, you can always start again.

Structure and types

Step 3: Check shape and data types

Count rows and columns and compare with what you expected. Then check each column’s type:

• Numbers stored as text (often left-aligned in Excel, or object dtype in pandas)
• Dates stored as text, or a mix of formats in one column
• IDs that lost leading zeros because they were read as numbers (PIN codes, product codes)

Fix types first. Many later checks silently fail on the wrong type.

Step 4: Remove exact and near duplicates

Exact duplicates often come from exporting overlapping date ranges. Near duplicates are subtler: the same customer with different spacing or capitalisation. Decide what makes a row unique (for example, order ID plus line number) and check that combination.

Tool	How to find duplicates
Excel	Data > Remove Duplicates, or COUNTIFS to flag first
SQL	GROUP BY the key columns HAVING COUNT(*) > 1
pandas	df.duplicated(subset=[...]) then drop_duplicates()

Flag before deleting, so you can see what you are removing.

Step 5: Handle missing values

For each column, count blanks and nulls, then ask why they are missing. Then choose:

• Leave and report when blanks are genuine (no discount applied).
• Fill with a clear label like “Unknown” for categories.
• Fill from another source when you can look up the true value.
• Drop rows only when they are unusable and removing them will not skew the result.

Watch for disguised blanks: “N/A”, “-”, “0” used to mean unknown, or a single space. The pandas guide to missing data explains how pandas treats each case.

Consistency

Step 6: Trim and standardise text

Remove leading and trailing spaces, fix inconsistent case and map variants to one value: “WB”, “West Bengal” and “W. Bengal” become “West Bengal”. Build a small mapping table rather than fixing values one by one, so the same rules apply next month.

Step 7: Fix dates and times

Check for mixed formats (03/04/2026 can be 3 April or 4 March), text dates, impossible dates (31 June) and time zone differences between systems. Store dates as real date types and agree on one format for reporting.

Step 8: Align units and currencies

Kilograms and grams in one column, prices with and without tax, rupees and dirhams side by side. Convert to one unit, note the rate and date of any currency conversion, and rename the column to say what it holds (amount_inr_excl_gst).

Validity

Step 9: Check ranges and allowed values

Every column has rules. Quantities should not be negative unless they are returns. Percentages sit between 0 and 100. Ages over 120 are wrong. Status fields should only contain known values. A quick summary (min, max, distinct values) per column catches most of these.

Step 10: Investigate outliers, do not just delete them

An order 50 times larger than usual could be a typo, a bulk B2B order or a test transaction. Look at the row, ask the data owner if needed, and then decide. Deleting real outliers hides the most interesting part of the data.

Step 11: Validate IDs and keys

Check that IDs are unique where they should be (one row per customer in a customer table), have the expected length and format, and are not blank. Broken keys break every join that follows.

Combining data

Step 12: Test joins before trusting them

After joining two tables, compare row counts before and after. Rows increasing means duplicate keys on one side; rows disappearing in an inner join means unmatched keys. Count unmatched rows on purpose with a left join and look at a sample.

Step 13: Reconcile totals with the source

Your cleaned data should agree with a number someone already trusts: the finance system’s monthly revenue, the CRM’s customer count, the total on the original report. Small differences are fine if you can explain them; unexplained gaps mean something went wrong.

Finish properly

Step 14: Document what you changed

Keep a short cleaning log: what you found, what you changed, how many rows were affected and why. In Power Query the applied steps are a log in themselves; in SQL and Python, comments and a short README do the job. This is also what makes a portfolio project convincing.

Step 15: Automate the steps you will repeat

If the data arrives every week or month, turn your cleaning into something you can rerun: a Power Query pipeline, a SQL view or a pandas script. The second run should take minutes. Our pivot table tutorial shows what clean, table-shaped data makes possible once this is done.

The checklist in one place

• 1. Source, period and grain understood
• 2. Raw copy saved, untouched
• 3. Row count, columns and data types checked
• 4. Duplicates flagged and removed
• 5. Missing and disguised blanks handled
• 6. Text trimmed and standardised
• 7. Dates converted and consistent
• 8. Units and currencies aligned
• 9. Ranges and allowed values checked
• 10. Outliers investigated
• 11. IDs unique and well formed
• 12. Joins tested with row counts
• 13. Totals reconciled with a trusted source
• 14. Cleaning log written
• 15. Repeatable steps automated

Where data cleaning fits in an analyst’s work

Cleaning is not a chore before the “real” work; it is a core part of the job, and interviewers often ask how you handled messy data in a project. If you are deciding which data role suits you, our comparison of data analyst vs data scientist vs business analyst explains how much cleaning each role involves, and our guide on how to become a data analyst sets out the full skill path.

Learn it with Omebly

Omebly Academy’s Data Analysis course teaches cleaning messy data in month 1 with Excel and basic SQL, and cleaning and exploration with Python and pandas in month 2, before moving on to Power BI dashboards. Classes are live and mentor-led, with recordings and weekly one-to-one reviews.

• Core (3 months, $699): Excel for analysis, SQL, Python with pandas and Power BI dashboards, with two capstone projects.
• Professional (6 months, $1,199): adds statistics, A/B testing, cohort and funnel analysis and report automation, four capstones, mock interviews and portfolio review.
• Career (12 months, $1,999): adds cloud warehousing basics, data modelling and a live client-style project with the Omebly team, unlimited mentor reviews and placement assistance.

You receive an Omebly certificate of completion with your projects listed. Placement assistance covers CV and LinkedIn help, mock interviews, portfolio review and referrals where possible; there is no job guarantee.

Want help choosing a track? Contact us or message us on WhatsApp to book a free first call.