Excel data quality: blank cells, duplicate keys and incorrect data types
A plausible Excel total does not prove reliable source data. Duplicate identifiers, text dates and missing fields require different checks.
Define uniqueness first
If OrderId is meant to be unique, a repeated identifier can inflate a report. In a worksheet where column A holds order identifiers, COUNTIF can reveal repetition; check blank cells first if missing IDs should not be counted as duplicates. Do not confuse line items, where order IDs may validly repeat, with an order-header table that requires one row per order.
=IF(A2="","",COUNTIF($A:$A,A2))Check types and missing values
A text value such as '01/02/2021' may be interpreted differently across regional settings. Verify that date cells carry proper date values rather than ambiguous text. Blank, zero and text values in price or quantity fields require different treatment. Preserve an untouched copy of the imported file and document each conversion rule before modifying the working dataset.
Reconcile independent counts
Cross-check formula-based duplicate counts against a PivotTable or an independent source query. Include blank IDs, unique IDs, duplicates and identifiers with surrounding whitespace as separate test cases. Function names and separators can differ in localized Excel installations; the sample uses English function names. On large sheets, measure recalculation cost before filling whole-column formulas everywhere.
Reproducibility and the 2021 context
This is a modern examination of Excel data-cleaning practices relevant to 2021, not a backdated publication. Record the workbook version, column transformations and validation outputs. Without those details, reproducing the same report becomes difficult. Where appropriate, a documented Power Query pipeline can make recurring imports easier to audit and maintain.
Comments
No comments yet. Be the first to contribute.
✎ Write a comment