Data cleaning is the process of detecting and correcting errors, inconsistencies, and inaccuracies in a dataset so it can be reliably used for analysis, operations, or reporting. The process has eight distinct steps. Skip any one of them and the problems you miss in that step will show up as errors downstream — in your reports, your campaigns, your CRM, or your AI outputs.
This guide walks through all eight steps with practical methods for each. At the end, there's a downloadable checklist you can use on any dataset.
The 8 Steps
- Profile and inspect
- Remove duplicates
- Fix structural errors
- Standardize formats
- Handle missing values
- Validate against rules
- Handle outliers
- Document and make it repeatable
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
Step 1: Profile and Inspect
Before fixing anything, understand what you're dealing with. A data profile answers:
- How many rows and columns?
- What is each column's data type (text, number, date)?
- What percentage of values are missing per column?
- What are the min, max, and most frequent values in each column?
- Are there obvious outliers or impossible values?
In Excel: Use a pivot table on each categorical column to see value distributions. Use =COUNTBLANK(A:A)/COUNTA(A:A) to get null rates per column.
Breaking point: Excel profiling is manual and covers only what you think to check. A 50-column dataset takes hours to profile by hand.
In Sohovi: Upload the file — you get a complete profile (null rates, type distributions, top values, outliers) for every column in under a minute.
What to document from this step: A list of the columns with the most problems. This becomes your cleaning priority list.
Step 2: Remove Duplicates
Duplicate rows inflate counts, skew averages, and cause embarrassing duplicate outreach. There are two kinds:
Exact duplicates: Two rows where every value is identical. Easy to find with Excel's Remove Duplicates or =COUNTIFS().
Fuzzy duplicates: Two rows that represent the same entity but with slight differences — a typo in a name, different email for the same person, company name variant. These require fuzzy matching.
In Excel: Data → Remove Duplicates handles exact matches. For fuzzy duplicates, add TRIM() and LOWER() helper columns and re-run, catching whitespace and casing differences.
Breaking point: Name variants, transposed words, and abbreviated company names ("Acme Corp" vs "Acme Corporation") are invisible to exact matching. Excel's Power Query fuzzy merge covers some cases but requires significant configuration.
See the dedicated guide: How to Remove Duplicate Rows in Excel and Fuzzy Matching Explained.
What to document: Duplicate rate before and after (e.g., "removed 147 of 3,200 rows, 4.6% duplicate rate").
Step 3: Fix Structural Errors
Structural errors are problems with how values are formatted or entered — not wrong data, just inconsistently formatted data that makes downstream matching and analysis unreliable.
The most common:
Casing: "new york", "New York", "NEW YORK" are three values for the same thing. Standardize with =PROPER(A2) or =LOWER(A2) depending on your preferred format.
Whitespace: Extra spaces before/after values cause invisible mismatches ("Acme " ≠ "Acme"). Fix with =TRIM(A2).
Non-printing characters: Line breaks embedded in cells (Char(10), Char(13)), non-breaking spaces (Char(160)), and zero-width spaces cause lookup failures and export problems. Fix with =CLEAN(TRIM(A2)).
Typos in categorical fields: "Active", "activ", "Activee" when the valid value is "Active". Use a pivot table to find all unique values in a categorical column and spot the variants.
In Excel: TRIM, CLEAN, PROPER, LOWER, SUBSTITUTE cover most structural fixes. For categorical cleanup, use Find & Replace.
Breaking point: Detecting all categorical variants manually is tedious for columns with dozens of possible values. One missed "Acctive" propagates into all downstream reports.
Step 4: Standardize Formats
Format standardization means ensuring all values in a column follow the same pattern — regardless of where they came from or who entered them.
Dates: "03/04/2026", "April 3, 2026", "2026-04-03", "03-Apr-26" are all the same date represented four different ways. Pick one format — ISO 8601 (YYYY-MM-DD) is the safest for interchange — and convert everything to it. See: CSV Dates Are Wrong in Excel.
Phone numbers: "(044) 2498-XXXX", "+91 44 2498 XXXX", "04424XXXXXX" are the same number in different formats. Standardize to E.164 (+[country code][number], no spaces). See: How to Standardize Phone Numbers.
Addresses: Abbreviated vs. written out ("St" vs "Street"), different country/state code formats, postal code inconsistencies. Address standardization usually requires a reference dataset or API — for simple cases, a lookup table of standard abbreviations works.
Identifiers (ZIP codes, product codes, serial numbers): Numeric identifiers with leading zeros need text-type columns. See: Excel Keeps Removing Leading Zeros.
Step 5: Handle Missing Values
Every missing value requires a decision. There are four options:
Delete the row: Appropriate when the row is useless without the missing value (e.g., a lead record with no contact information), and when deleting it won't introduce bias. Wrong if the missing data is itself informative.
Fill with a default: Appropriate for optional fields with a known sensible default (e.g., country = "US" for a dataset where most records are US-based). Wrong when the default would create a false impression of completeness.
Fill with a derived value: Impute from other columns (e.g., derive city from ZIP code using a reference table). Requires a reliable reference.
Flag and leave: Mark missing values explicitly (e.g., add an is_address_missing boolean column) and leave the value null. Appropriate when the missing value is genuinely unknown and any fill would be misleading.
The question nobody asks: Why is this value missing? Never collected (field added later in the system's history), lost in export (a system bug), or genuinely N/A (field doesn't apply to this record type)? Different causes need different treatments.
In Excel: Filter on blanks (Data → Filter → blank), decide case by case, fill using IF(ISBLANK()) formulas.
Breaking point: Filling missing revenue with $0 wrecks your averages. Filling missing category with "Unknown" inflates an artificial category. Always preview the effect of your fill strategy on summary statistics before applying.
See the dedicated guide: Handling Missing Values Without Python.
Step 6: Validate Against Rules
Validation checks whether values conform to expected rules — not just whether they're present, but whether they're plausible.
Type validation: Is the email column actually email addresses? Is the date column actually dates? Are the quantity columns all positive integers?
Range validation: Is the age column between 0 and 120? Is the order amount between $0.01 and $10,000? Values outside plausible ranges may be errors (a quantity of 999999) or data entry mistakes (a negative discount amount).
Format validation: Does the email match [something]@[something].[something]? Does the phone number have the right number of digits after stripping formatting? Does the ZIP code match a 5-digit or 9-digit pattern?
Referential validation: Does every customer_id in the orders table correspond to a real customer in the customers table? Are all country codes in your reference list of valid ISO 3166-1 codes?
In Excel: Data Validation rules on columns, ISNUMBER/ISTEXT/ISERROR formulas, MATCH against a lookup table.
Breaking point: Excel validation runs forward (prevents bad data entry) but doesn't retroactively flag existing bad values. COUNTIF formulas to check existing data are manual to set up and easy to miss.
Step 7: Handle Outliers
Outliers are values that are technically valid but significantly different from the rest — a $1 order in a dataset of $500 orders, a customer with 0 purchases in a dataset otherwise ranging from 5–50.
Outliers are NOT automatically errors. Some are genuine (your biggest customer really did order 10,000 units). Some are errors (a quantity of -1 from a form bug). The process:
- Identify: Use
=AVERAGE()± 2–3 standard deviations to flag statistical outliers. Or visual inspection (a scatter plot or simple sort) for obvious anomalies. - Investigate: Is this plausible? Check the source record, transaction log, or ask the data owner.
- Decide: Correct it (if it's clearly an error), flag it (if it might be valid but needs downstream handling), or leave it (if it's legitimate).
What NOT to do: Don't delete outliers automatically. Removing your biggest customer's purchases because they "look like outliers" will wreck your revenue analysis.
Step 8: Document and Make It Repeatable
Data cleaning without documentation is temporary. Next month's export will have the same problems. The fix:
-
Document what you found and what you changed. A simple log: "removed 147 duplicates; standardized date column to YYYY-MM-DD; filled 23 missing country values with 'US' based on phone prefix." This is your audit trail.
-
Save your cleaning steps as a reproducible process. Power Query saves transformation steps and replays them on new data. A Sohovi validation profile saves your rules and applies them to every future upload.
-
Set up ongoing validation. Define what "good" looks like for each key column and check every new dataset against those rules before it enters your workflow.
The 8-Step Checklist
Use this on every dataset before analysis, migration, or campaign send:
- [ ] Profile all columns (null rates, type distribution, top values, outliers)
- [ ] Remove exact duplicates
- [ ] Remove/merge fuzzy duplicates (name variants, format differences)
- [ ] Fix casing, whitespace, and non-printing characters
- [ ] Standardize date, phone, and address formats
- [ ] Decide on missing value treatment per column (delete / default / derive / flag)
- [ ] Validate types, ranges, formats, and referential integrity
- [ ] Flag or correct outliers with investigation
- [ ] Document changes in a cleaning log
- [ ] Save cleaning steps for repeatability
Frequently Asked Questions
Q: What's the difference between data cleaning and data transformation? Data cleaning fixes errors and inconsistencies — making bad data good. Data transformation changes the structure or format of data for a specific use case (e.g., pivoting rows to columns, aggregating records). Cleaning precedes transformation; you clean first so the transformation operates on reliable inputs.
Q: How long should data cleaning take? A reasonable estimate for a well-profiled, moderately messy 10,000-row dataset is 2–4 hours manually (in Excel), or 20–40 minutes with a purpose-built tool for the profiling and automated checks. The more complex the structural errors and the more columns involved, the longer it takes. If you're spending more than a day on a single dataset, consider whether you have a data quality process problem at the source.
Q: Can I skip steps if the dataset looks clean? The profiling step (Step 1) tells you whether you can safely skip later steps. Don't skip profiling — it's how you know the data is actually clean rather than assuming it. If profiling shows no duplicates and no missing values, you may be able to skip Steps 2 and 5 confidently.
Q: Should I clean data before or after importing it into a system? Before, where possible. Cleaning post-import is harder because errors have already propagated. The ideal workflow: profile → clean → validate → import → validate again post-import.
Q: What's the best tool for data cleaning? For spreadsheet-based work without engineering resources: Sohovi (browser-based, handles Steps 1–6 automatically) or OpenRefine (free, powerful, technical). For pipeline-based work with engineers: Great Expectations or dbt tests. For most small businesses, Excel covers the basics and a browser-based tool covers what Excel misses.
Steps 1–6 of this checklist are one screen in Sohovi — upload your dataset and work through the profile, dedup, and validation checks in a single session. Free to try, nothing to install, your file never leaves your browser.