Data Cleaning Checklist
Data Cleaning Checklist
Data cleaning is an essential step in preparing reliable data for analysis, reporting, dashboards, and machine learning. Incomplete records, duplicate entries, inconsistent formats, invalid values, and incorrect data types can distort calculations and lead to misleading conclusions.
This Data Cleaning Checklist helps data analysts, students, business analysts, database professionals, and data science teams improve data quality before using datasets for decision-making.
Use this checklist when working with spreadsheets, CSV files, databases, CRM exports, marketing reports, survey responses, or other structured datasets.
1. Understand the Dataset
Identify the dataset's source and purpose.
Confirm the date range covered by the data.
Review the number of rows and columns.
Identify what each row represents.
Review column names and descriptions.
Identify unique identifiers and key fields.
Confirm expected data types.
Identify important business rules.
Check whether the dataset contains personal or confidential information.
Document known limitations before making changes.
Verification: Confirm that you understand the dataset's structure, meaning, and intended use before cleaning it.
2. Preserve the Original Data
Create a copy of the original dataset.
Store the original in an appropriate location.
Give the working copy a clear name.
Record the extraction date or source version.
Document the planned cleaning operations.
Identify which changes can be reversed.
Avoid overwriting source data unintentionally.
Restrict access to sensitive datasets.
Establish a naming convention for cleaned versions.
Confirm that the original data can be recovered if necessary.
Verification: Ensure that the original dataset remains available for comparison, auditing, and recovery.
3. Inspect Missing Values
Identify blank cells and null values.
Check for placeholders such as
N/A,Unknown, or-.Distinguish genuinely missing data from valid empty values.
Calculate missing-value counts by column.
Review missing-value percentages.
Identify records missing critical fields.
Investigate why important values are missing where possible.
Decide whether to retain, impute, flag, or remove affected records.
Apply the chosen method consistently.
Document the treatment of missing values.
Verification: Confirm that missing values have been handled using a method appropriate to the dataset and analytical objective.
4. Identify and Handle Duplicate Records
Check for completely duplicated rows.
Identify potential duplicates using business keys.
Review duplicate customer, transaction, or product identifiers.
Distinguish legitimate repeated events from accidental duplicates.
Compare timestamps or source identifiers where relevant.
Define which record should be retained if duplicates are invalid.
Remove confirmed duplicates when appropriate.
Record the number of records removed.
Recheck uniqueness after cleaning.
Verify that valid repeated transactions or events remain intact.
Verification: Confirm that duplicate removal does not delete legitimate records or distort totals.
5. Standardize Data Formats
Standardize date formats.
Confirm consistent date and time interpretation.
Review time zones where relevant.
Standardize currency formats.
Confirm consistent units of measurement.
Normalize phone number formats where appropriate.
Standardize country, state, and location names.
Review capitalization and whitespace.
Normalize category labels.
Check decimal separators and thousands separators.
Document any conversions performed.
Verification: Ensure that equivalent values use consistent representations and that conversions preserve the original meaning.
6. Correct Data Types
Identify columns stored as text that should be numeric.
Convert date strings to appropriate date types.
Check integer and decimal fields.
Validate Boolean fields.
Confirm that identifiers are stored appropriately.
Preserve leading zeros in codes such as postal codes or account identifiers.
Check that numeric conversions do not introduce unintended rounding.
Identify values that fail type conversion.
Review the results after conversion.
Document unresolved type issues.
Verification: Confirm that each column has a suitable data type and that identifiers are not accidentally altered by numeric conversion.
7. Validate Values and Business Rules
Check for negative values where they are not permitted.
Identify impossible or out-of-range values.
Validate percentages and ratios.
Check minimum and maximum allowed values.
Verify that dates follow expected business rules.
Confirm that required fields contain valid values.
Review inconsistent status values.
Check that category values belong to approved lists.
Validate calculated fields against their formulas.
Investigate records that violate business rules.
Verification: Confirm that invalid values are corrected, excluded, flagged, or retained with a documented justification.
8. Review Outliers and Unusual Values
Identify unusually high or low numeric values.
Compare outliers with the expected data distribution.
Review unusual transaction amounts or quantities.
Investigate sudden spikes or drops.
Check whether extreme values are caused by data-entry errors.
Compare suspicious records with trusted source information where available.
Distinguish legitimate rare events from errors.
Avoid automatically removing every statistical outlier.
Document the treatment of unusual values.
Reassess important metrics after any changes.
Verification: Ensure that outliers are investigated rather than removed solely because they appear unusual.
9. Clean Text and Category Fields
Remove unnecessary leading and trailing spaces.
Standardize capitalization where appropriate.
Correct confirmed spelling variations.
Normalize equivalent category labels.
Review inconsistent abbreviations.
Check for hidden or non-printing characters.
Standardize naming conventions.
Preserve meaningful distinctions between categories.
Review free-text fields for unexpected formatting.
Maintain a mapping of values that were standardized.
Verification: Confirm that equivalent categories are represented consistently without combining values that have different meanings.
10. Validate Identifiers and Relationships
Check whether primary identifiers are present.
Confirm uniqueness where required.
Identify missing or malformed identifiers.
Check foreign-key relationships where applicable.
Identify records that reference nonexistent entities.
Verify relationships between customers, orders, products, or other related records.
Review unmatched records after joins.
Check whether joins have multiplied rows unexpectedly.
Confirm that record counts are reasonable after transformations.
Document unresolved relationship issues.
Verification: Ensure that identifiers and relationships support accurate analysis and that joins do not create unintended duplication.
11. Check Data Consistency Across Sources
Compare overlapping fields across systems.
Verify totals against trusted reports.
Check whether reporting periods align.
Confirm that units and currencies are consistent.
Review differences in naming conventions.
Investigate mismatched identifiers.
Check whether systems use different definitions of the same metric.
Identify stale or outdated source records.
Document differences that cannot be reconciled.
Confirm which source should be treated as authoritative for each field.
Verification: Ensure that data from different sources can be compared meaningfully and that unresolved discrepancies are documented.
12. Validate Formulas and Calculated Fields
Review formulas used to calculate derived values.
Confirm the correct inputs are used.
Check division-by-zero scenarios.
Verify percentage and ratio calculations.
Confirm rounding rules.
Check date-difference calculations.
Validate totals against component values.
Test calculations using sample records.
Recalculate important metrics independently where practical.
Document assumptions behind derived fields.
Verification: Confirm that calculated fields use the intended formulas and produce results consistent with the business definitions.
13. Protect Privacy During Data Cleaning
Identify personal and confidential information.
Confirm authorization to process the dataset.
Remove fields that are not required for the task.
Mask or pseudonymize sensitive identifiers where appropriate.
Restrict access to authorized users.
Avoid uploading confidential datasets to unapproved tools.
Use approved storage locations.
Review temporary files and exported copies.
Follow applicable retention and deletion policies.
Document relevant privacy controls.
Verification: Ensure that cleaning activities do not unnecessarily expose or retain sensitive information.
14. Automate Repeatable Cleaning Steps
Identify cleaning tasks that recur regularly.
Document the transformation rules.
Automate consistent operations where practical.
Add validation checks to the workflow.
Handle unexpected values safely.
Log processing errors.
Test the workflow on representative datasets.
Compare automated output with a verified sample.
Track changes to scripts and transformation rules.
Document how the process can be rerun.
Verification: Confirm that the cleaning process produces consistent results and that failures or unexpected changes can be detected.
15. Perform Final Data Quality Checks
Recheck row and column counts.
Recalculate missing-value counts.
Recheck duplicate records.
Validate data types and formats.
Confirm that business rules are satisfied or exceptions documented.
Review outlier treatment.
Validate key relationships and joins.
Compare important totals with the original dataset.
Confirm that transformations did not introduce unexpected changes.
Review a sample of cleaned records manually.
Record remaining data quality limitations.
Verification: Confirm that the cleaned dataset meets the quality requirements for its intended use.
16. Document and Approve the Cleaned Dataset
Record the original data sources.
Document cleaning operations performed.
Record the number of records added, changed, removed, or flagged.
Explain how missing values were handled.
Document duplicate-removal rules.
Record standardization and conversion rules.
Document assumptions and unresolved issues.
Save the cleaned dataset with a clear version identifier.
Obtain review or approval where required.
Provide the dataset and documentation to authorized users.
Schedule future quality checks if the data is refreshed regularly.
Verification: Ensure that another analyst can understand what changed, why it changed, and whether the dataset is suitable for its intended purpose.
Common Data Cleaning Mistakes to Avoid
Overwriting the original dataset: Preserve an unchanged source copy.
Deleting every record with missing values: Evaluate the impact before removing data.
Removing all outliers: Investigate whether unusual values are valid.
Deleting duplicates without checking business meaning: Distinguish repeated events from erroneous duplicate records.
Changing identifiers into numbers: Preserve meaningful leading zeros and exact identifier values.
Standardizing categories too aggressively: Keep distinct categories separate when their meanings differ.
Ignoring join errors: Validate record counts and unmatched keys after combining datasets.
Changing values without documentation: Record the transformation rules and reasons.
Ignoring privacy: Use only the data and access permissions necessary for the task.
Assuming cleaned data is automatically accurate: Validate the final dataset against trusted sources and business rules.
Final Data Cleaning Sign-Off
The dataset's purpose and structure are understood.
The original data is preserved.
Missing values and duplicates are reviewed.
Formats and data types are standardized.
Business rules and value ranges are validated.
Outliers are investigated appropriately.
Identifiers and relationships are checked.
Calculated fields are verified.
Privacy and access controls are reviewed.
Automated transformations are tested where applicable.
Final data quality checks are completed.
Cleaning decisions and limitations are documented.
Data owner: ____________________
Review date: ____________________
Dataset status: ____________________
Conclusion
Data cleaning improves the reliability of analysis by identifying missing values, duplicates, inconsistencies, invalid records, and transformation errors. Use this checklist to make cleaning decisions systematically, preserve data integrity, and prepare datasets for trustworthy reporting and analysis.
Learn. Prepare. Grow. — GoBizly
Explore more practical technology and AI checklists at GoBizly to strengthen your data preparation workflow.
