This project demonstrates a production-grade

This project demonstrates a production-grade data cleaning workflow designed to transform an unstandardized, error-prone sales dataset (~2,000 transaction records) into an analysis-ready asset. The focus is on preserving raw data lineage, enforcing data privacy regulations, repairing mathematical and structural inconsistencies, and implementing reproducible cleaning techniques using native Excel functionality.

Key Technical Deliverables & Methodology

Data Lineage & File Integrity: Established non-destructive audit trails by archiving raw data externally and internally, then migrated source .csv data into structured .xlsx workbooks to preserve calculation metadata and multi-tab workflows.

Data Privacy Compliance (PII Scrubbing): Audited and stripped Personally Identifiable Information (customer names, phone contacts, email addresses) to align the analytical environment with data privacy standards before reporting.

Text Normalization & Deduplication: Cleansed categorical attributes (cities, regions, product lines) using nested TRIM() and PROPER() functions, removed formula dependencies via static value conversion, and systematically eliminated duplicate records to prevent skewed transactional totals.

Arithmetic Reconciliation & Error Recovery: Identified faulty quantities, missing discounts, and #N/A unit prices. Used algebraic reverse-engineering:

$$\text{Unit Price} = \frac{\text{Subtotal}}{\text{Quantity} \times (1 - \text{Discount})}$$

to restore true transaction economics before recalculating VAT (7.5%) and gross invoice amounts.

Custom Dynamic Date Parsing: Resolved mixed-standard date entries (US MM/DD/YYYY vs. UK DD/MM/YYYY) where default type conversion failed, engineering a dynamic extraction formula utilizing IF(), ISNUMBER(), DATE(), RIGHT(), LEFT(), and MID().

Missing Value Imputation (The 3Rs Framework): Evaluated incomplete attributes under the Remove, Replace, or Retain paradigm, imputing unrecorded categorical dimensions (Sales Rep, Suppliers) with standardized "Unknown" categories to prevent attrition bias during aggregation.