You've inherited a spreadsheet that looks like it was maintained by four different people with four different systems over four years. Column names are inconsistent, some rows have empty critical fields, values are formatted differently throughout, and you're pretty sure there are duplicates. You need to clean it — and you don't write code.
Here's the process.
Step 1: Profile Before You Touch Anything
Before making any changes, understand what you have. Upload the file to Sohovi or run basic checks in Excel to answer:
- Which columns are mostly empty? (Don't waste time standardizing a column that's 40% null)
- Which columns have duplicate values? (Where to focus deduplication)
- Which columns have format inconsistencies? (Where to focus standardization)
- Does the file contain personal data? (What requires careful handling)
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
Profile first, clean second. Cleaning without understanding what you have leads to circular work.
Step 2: Remove Obvious Errors First
Before standardizing, remove the clearly bad data:
- Delete test rows (look for "test", "fake", "example", "do not use" in any column)
- Delete rows that are completely empty
- Delete obvious duplicates using Excel's Remove Duplicates function on the unique identifier column
Step 3: Standardize the Most Important Columns
Focus on the columns you'll actually use — don't spend time standardizing columns you'll never filter, join, or analyze.
For categorical columns (status, country, industry, type): Find/Replace to standardize spelling and capitalization variants. Create a lookup table of all current values and their standardized equivalent.
For date columns: Use Excel's Text to Columns or format conversion functions to standardize to one format (YYYY-MM-DD works best for most uses).
For phone numbers: Remove all non-numeric characters and standardize to a consistent format using SUBSTITUTE and text functions.
Step 4: Handle Missing Values
For each column with significant nulls, decide:
- Can it be filled in? (Look up the missing values from another source)
- Should it be flagged? (Add a "needs_review" marker)
- Is the record usable without it? (If not, the record may need to be excluded from certain uses)
Sohovi profiles every column in your dataset for completeness and flags the exact rows where values are missing — free to try.
Don't delete records just because they have missing values — you may need them for non-affected use cases.
Step 5: Validate After Cleaning
After cleaning, run a final profile to confirm the improvements: completeness rates should be higher, duplicate counts should be lower, format consistency should be better. Compare to the initial profile to measure improvement.
Data cleaning isn't a one-time project — it's a recurring process. Build cleaning steps into your standard workflow for any dataset you use regularly, and the maintenance burden decreases over time.
