9 Essential Data Cleaning Techniques Every Analyst Must Master

1. Missing‑Value Handling

Missing values are the most frequent data‑quality issue (e.g., empty phone numbers, order amounts, shipment dates, product categories). Deleting rows outright can discard important business signals. Before acting, determine whether the null represents a data error or a legitimate business status.

If it is a collection omission, consider supplementing the data.

If it reflects a business state, do not fill arbitrarily.

If the missing proportion is low, deletion may be acceptable.

If the proportion is high, assess the impact on overall conclusions.

Typical handling methods:

Delete : suitable when the missing proportion is very low and does not affect overall judgment.

Fill : use mean, median, mode, business defaults, or the previous valid record.

Retain : some nulls are actual business states (e.g., “not shipped”, “not collected”).

Mark : add a flag field such as is_missing to treat missingness as an analytical dimension.

2. Duplicate‑Value Handling

Duplicates can dramatically distort results. Effective deduplication starts by identifying a field that uniquely represents a business record.

Orders → order number.

Customers → customer ID, phone number, or name + social‑credit code.

Products → product code.

Financial vouchers → voucher number + date + account + amount.

Common deduplication approaches:

By unique ID (e.g., order number, customer ID, product code).

By composite fields (e.g., customer name + phone, order date + amount + store).

By business rules (e.g., retain the most recent record for a repeatedly registered customer).

By similarity (e.g., names with slight variations that refer to the same entity).

Two common pitfalls:

Failing to delete required records inflates totals.

Deleting records that should stay removes true business facts.

Deduplication rules must be validated with business stakeholders.

3. Outlier Handling

Outliers are data points that deviate sharply from the norm (e.g., an order amount that spikes dramatically, a customer age of 180, negative inventory, gross margin > 100 %, or a ten‑fold sales surge in one day). They fall into two categories:

Data errors (entry mistakes, unit errors, field misalignment, sync issues).

Genuine business anomalies (large client bulk purchases, promotional spikes, channel stockpiling, price adjustments, one‑off project revenue).

Core steps:

Business‑rule validation : e.g., age cannot be negative, inventory cannot be negative without reason, gross margin should not exceed 100 %.

Statistical identification : use mean, standard deviation, box plots, percentiles to spot values far from normal ranges.

Time‑trend comparison : check whether a metric suddenly jumps and deviates from historical trends.

Business‑event correlation : link spikes to promotions, policy changes, price adjustments, or channel shifts.

Outliers are often the most valuable entry points for analysis, provided the source (error vs. real change) is correctly identified.

4. Format Standardization

Inconsistent formats break downstream processing. Typical inconsistencies:

Date formats: 2026/1/1, 2026-01-01, 2026年1月1日.

Amount formats: 1000, 1,000, ￥1000, 1000元.

Region names: Shanghai, Shanghai City, SH, Shanghai Municipality.

Consequences include inability to aggregate dates by month, amounts not usable in calculations, regions not groupable, customer names not matchable, and tables that cannot be joined.

Standardization steps:

Date unification : convert all dates to a single format, e.g., YYYY‑MM‑DD.

Amount unification : strip currency symbols, commas, and units, keeping only numeric values.

Text unification : trim spaces, remove special symbols, unify case and full‑/half‑width characters.

Region name unification : map to standard administrative names (province, city, district).

Code unification : ensure consistent length and rules for product codes, customer codes, organization codes.

5. Data‑Type Conversion

Fields that appear numeric may be stored as text, preventing calculations. Examples:

Amount field shows 1000 but is text.

Date field recognized as text.

Customer ID stored as number, losing leading zeros.

Percentages expressed as 20% or 0.2.

Amounts mixed with units like “元” or “万元”.

Before conversion, ask:

Is the field used for calculation or identification?

Is it a continuous value or a categorical label?

Can it be aggregated?

Will conversion lose information?

Not every numeric‑looking field should be treated as a number; identifiers such as phone numbers, ID numbers, or product codes must not participate in arithmetic.

6. Metric‑Definition (Scope) Unification

Different departments often use different definitions for the same metric, leading to contradictory reports. Example of “revenue” definitions:

Finance: recognized revenue.

Sales: contract amount.

Operations: received payment.

Management: cash received.

Unification actions:

Metric name : use a single term for each concept (e.g., sales, revenue, cash‑in, GMV).

Formula : agree on a single calculation, e.g., gross margin = gross profit / revenue (not gross profit / sales).

Statistical range : decide whether to analyze the entire business or core segments, tax‑included vs. tax‑excluded amounts.

Time scope : choose a consistent reference (order date, payment date, shipment date, or revenue‑recognition date).

Data source : specify the authoritative system/table when conflicts arise.

Without unified definitions, more data leads to more disputes.

7. Field Splitting and Merging

Raw business fields are often not analysis‑ready. Example – address field Guangdong Province Shenzhen City Nanshan District Tech Park should be split into province, city, district, and detailed address. Product name Men‑Black‑XL‑Spring can be split into color, size, style, season.

Conversely, merging may be needed (e.g., province + city → “region”, customer name + phone → “customer identifier”).

Key considerations:

Determine the granularity of each row (order, customer, product, fee, monthly summary). Incorrect granularity leads to double‑counting (e.g., joining order details with order headers without proper handling inflates amounts).

Ask for each row: what does it represent? Which fields are dimensions? Which are metrics? Which need splitting? Which need merging?

8. Code Mapping

Enterprise data often contains the same entity named differently across systems. Examples:

Customer level: A‑class, key customer, core customer, VIP.

Channel name: online, e‑commerce, web store, platform channel.

Region name: East China, East Zone, Shanghai Region, Jiang‑Zhe‑Hu area.

Build a standard dictionary to map disparate names to a single canonical term (e.g., “Shanghai”, “Shanghai City”, “SH” → “Shanghai City”). Code mapping resolves cross‑system consistency.

9. Data Validation

After cleaning, validation ensures the process did not introduce new errors. Common checks:

Total validation : compare order counts, sales totals, and customer counts before and after cleaning.

Range validation : ensure amounts, percentages, and dates fall within reasonable bounds.

Logical validation : e.g., shipment date cannot precede order date; refund amount cannot exceed order amount.

Consistency validation : verify that the same customer, product, or organization is consistent across tables.

Sampling verification : randomly sample rows and cross‑check against source systems.

Validation prevents “cleaning‑more‑and‑more‑wrong” and is essential for trustworthy dashboards, financial analysis, and management reporting.

Overall Insight

Good data cleaning solves three core problems:

Can the data be calculated? (Missing values, outliers, format and type issues prevent stable computation.)

Can the data be compared? (Inconsistent definitions, codes, and structures prevent cross‑department or cross‑system comparison.)

Can the data be trusted? (Without validation, rules, and traceability, analysis cannot support business decisions.)

When these conditions are met, the data becomes calculable, comparable, trustworthy, and reusable, forming a solid foundation for downstream analysis and reporting.