Data Cleaning Demystified: 10 Essential Techniques from Missing Values to Outlier Handling

Many analysts jump straight to building dashboards as soon as they receive raw data, only to encounter mismatched numbers, unanswered business questions, and requests like “where did this figure come from?”. The root cause is often dirty data that has never been properly cleaned.

1. Missing‑Value Handling (4 approaches)

Delete rows when missing records are few and their removal does not affect overall analysis, but never delete an entire order because an auxiliary field is empty.

Fill with fixed values such as “Unknown”, “Unclassified”, or “To‑Confirm” for categorical fields, preserving the record while flagging the gap.

Fill with statistical values – mean, median or mode – choosing median when extreme values exist.

Business‑logic completion – e.g., calculate sales amount as quantity × price, or join missing customer regions from master data.

The principle is: complete what can be inferred, compute what can be reasonably derived, and delete only truly unusable rows. These rules should be embedded in a stable data pipeline rather than applied ad‑hoc in Excel.

2. Duplicate‑Value Handling

Exact duplicate rows can be removed, but duplicate keys may represent legitimate state changes. For example, the same order number with two different statuses ("Pending" vs "Shipped") requires keeping the latest record. Identify a business key (order number, order + product code, etc.) and define retention rules such as:

Keep the row with the most recent update timestamp.

Prefer records marked “Confirmed”.

Prioritize higher‑quality data sources.

Aggregate transactional data instead of deleting it.

Technical duplicates are distinguished from business‑driven changes before de‑duplication.

3. Format Unification

Inconsistent date strings ("2026‑07‑01", "2026/07/01", "20260701", "01‑07‑2026") look identical to humans but break downstream calculations. Other format issues include spaces in phone numbers, mixed percentage representations (0.15 vs 15), currency units embedded in values, case‑sensitive text, and numeric codes stored as text. These inconsistencies cause aggregation errors, sorting problems, and failed joins.

4. Text Standardization

Trim leading/trailing spaces.

Normalize case (e.g., finebi → FineBI).

Remove special characters, line breaks, tabs, full‑width symbols.

Maintain a mapping table for synonyms (e.g., multiple spellings of a customer name).

5. Data‑Type Conversion

Values that appear numeric may be stored as text, preventing sum, average, or sorting operations. Common conversions:

Text → Number.

Text → Date.

Timestamp → Date‑time.

Percentage string → Decimal.

Code fields → Text (to preserve leading zeros).

6. Outlier Handling (4 methods)

Fixed thresholds based on business limits (e.g., discount ≤ 100 %).

Mean ± 3 × StdDev for relatively stable distributions.

IQR method : lower = Q1 − 1.5 × IQR, upper = Q3 + 1.5 × IQR.

Business‑rule detection (e.g., completed order with zero sales, delivery date earlier than order date, inventory imbalance, profit margin = ‑300 %).

Outliers are flagged, investigated, and then either corrected, retained with a note, or removed.

7. Unit and Metric Consistency

Fields like sales amount may represent order value, invoiced amount, or received payment, each with different units (yuan vs ten‑thousand yuan, tax‑included vs tax‑excluded, RMB vs USD). Inventory may be counted in pieces, boxes, or pallets. Clearly document the unit, time grain, inclusion of tax, and currency conversion for every metric.

8. Logical Consistency Checks

Time logic : order ≤ ship ≤ receipt.

Amount logic : sales = quantity × price − discount; unpaid = receivable − paid; gross profit = revenue − cost.

Inventory logic : ending = beginning + in − out ± adjustments.

Status logic : cancelled order should have zero shipped quantity, etc.

9. Relationship Validation

When joining tables (orders, customers, products, payments, costs), verify that keys match, one‑to‑many relationships are expected, primary keys remain unique, and record counts before/after join are reasonable. Unexpected drops (e.g., 10 000 orders reduced to 9 500 after join) indicate missing or mismatched master data.

10. Field Splitting & Reconstruction

Complex fields often embed multiple attributes (e.g., "A001‑East‑Standard‑Red" → region, product line, version, color). Split such fields into atomic columns; similarly split addresses, device codes, cost hierarchies, or dates into year/quarter/month/week. Conversely, sometimes fields need merging (e.g., order number + line number to form a unique key).

11. Making the Process Reproducible

Repeated ad‑hoc cleaning leads to irreproducible results, metric drift, and endless re‑validation. A robust pipeline should include:

Data profiling (volume, types, null ratios, distinct values).

Business‑key definition.

Standardized field definitions (names, types, formats, codes, metric definitions).

Explicit cleaning rules for nulls, duplicates, outliers, and unmatched records.

Logging of deletions, fills, outlier counts, and metric changes.

Automation of the rules so that new data triggers the same process without manual intervention.

With the rules codified, analysts can focus on higher‑level questions such as why sales grew without profit increase, why inventory turnover slowed, or why a high‑revenue region shows high collection risk.

In summary, data cleaning is not merely removing blanks; it is about establishing unified data standards, embedding business logic into an automated pipeline, and ensuring that downstream dashboards, models, and AI queries are built on trustworthy, consistent data.