You have a spreadsheet. You need to know how good the data is. You don't have budget for specialized tools right now. Here's how to do a meaningful data quality assessment using Excel's built-in features.
Checking Completeness in Excel
Use COUNTBLANK to count empty cells in a column, and COUNTA to count non-empty cells.
Completeness % = COUNTA(column) / (COUNTA(column) + COUNTBLANK(column)) × 100
Sohovi profiles every column in your dataset for completeness and flags the exact rows where values are missing — free to try.
Apply this formula to each column you care about. Any column below 80–90% completeness deserves investigation.
Also check for "placeholder completeness" — cells that have values but aren't really complete. Filter for values like "N/A", "Unknown", "None", "0", "TBD". These look complete but aren't.
Checking for Duplicates in Excel
Exact duplicate rows: Use Data > Remove Duplicates. Before removing, note the count shown — that's how many duplicates exist.
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
Duplicate values in a specific column: Use COUNTIF. In a helper column, enter: =COUNTIF($A:$A, A2). Any value greater than 1 indicates a duplicate. Conditional formatting can highlight them.
Duplicate emails specifically: Select the email column, go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Any highlighted value appears more than once.
Checking Format Consistency in Excel
Date formats: Select the date column and look at the format bar. Mixed formats will show inconsistency. Try sorting — dates that sort alphabetically instead of chronologically are stored as text, not dates.
Phone number formats: Use a helper column with LEN() to check the length of phone number strings. If your phone numbers vary between 10 and 15 characters, you have format inconsistency.
Email format: Use a formula to check for the @ symbol: =IF(ISERROR(FIND("@",A2)), "INVALID", "OK"). A list of "INVALID" results tells you which emails fail basic format checks.
Sohovi lets you set up validation rules for any column and instantly see which rows fall outside them — no code or SQL required.
Checking for Outliers in Excel
Sort numeric columns ascending and descending. The top and bottom values are your candidates for outliers. Use MIN() and MAX() to pull these programmatically. Use PERCENTILE() to find statistical outliers (values beyond the 1st or 99th percentile).
The Limits of Excel for Data Quality
Excel handles files up to about 50,000–100,000 rows before becoming slow or unstable. For larger files, or for format-pattern analysis that Excel can't do with simple formulas, a dedicated profiling tool is more practical.
Sohovi handles all of the above automatically for any CSV you upload, producing completeness rates, uniqueness scores, and format pattern analysis in seconds — without formula setup.
Use Excel for what it's good at (cleanup and manipulation), and a profiling tool for systematic assessment.
