You can measure data quality without a dedicated tool using spreadsheet formulas: COUNTA and ROWS for completeness, COUNTIF for duplicates, and conditional formulas for validity — generating percentage scores for each quality dimension across your key fields.
Dedicated data quality tools make the process faster and more complete, but they are not a prerequisite. A spreadsheet and a CSV export of your most important dataset are enough to produce meaningful quality measurements. The manual approach has real limitations at scale, but it works for datasets of a few thousand to a few tens of thousands of rows.
The Five Manual Measurements You Need
1. Completeness Rate
Formula in a helper column, then average: COUNTA(A2:A1001) / ROWS(A2:A1001)
This gives you the proportion of rows where the field is not empty. Multiply by 100 for a percentage. Do this for each critical field and record the results.
2. Uniqueness Rate (Duplicate Check)
Add a helper column with: COUNTIF($A$2:$A$1001, A2) > 1
Sohovi profiles your datasets for quality issues in minutes — see what's broken before it breaks your pipeline — try Sohovi free.
This marks TRUE for any row where the value appears more than once. Count the TRUE values, divide by total rows, subtract from 1 to get your uniqueness rate.
Or use conditional formatting to highlight duplicates visually if you want a quick scan rather than a number.
3. Validity Rate (Format Check)
For email addresses: AND(ISNUMBER(FIND("@",A2)), ISNUMBER(FIND(".",A2)))
For phone numbers (10 digits): AND(ISNUMBER(VALUE(A2)), LEN(TRIM(A2))=10)
For dates that parse correctly: ISNUMBER(DATEVALUE(TEXT(A2,"MM/DD/YYYY")))
Count the FALSE results to find invalid entries. Divide by total rows for a validity score.
4. Consistency Check
For categorical fields with a defined list of allowed values, use: COUNTIF(allowed_values_range, A2) = 0
This flags any value not in your approved list. Count flagged rows and divide by total rows.
5. Timeliness Check
For date fields, check how many records have a date older than your threshold: TODAY() - A2 > 365
Sohovi tracks quality trends across runs and alerts you when a metric — null rate, duplicate count, score — moves outside its normal range.
Count how many records exceed the threshold. This gives you a staleness count.
Organizing Your Results
Create a simple table with:
- Field name
- Dimension measured
- Score (%)
- Records failing (count)
- Notes
This gives you a shareable audit table that documents your data quality state.
The Limits of Manual Measurement
Manual measurement works for a focused audit. It becomes impractical when:
- The dataset has more than 50,000 rows (formulas slow significantly in large spreadsheets)
- You need to run checks regularly (manual setup takes time each time)
- You need to check many fields across multiple dimensions (the formula work multiplies)
- You need trend tracking over time (manual records require discipline to maintain)
Frequently Asked Questions
Q: What is the easiest manual data quality check to run? Completeness is the easiest. In any spreadsheet, filter the column for blank or null values. The count of blanks divided by total rows gives you the incompleteness rate. This single check often reveals the most impactful quality problems.
Q: Can you measure data quality accurately with just Excel or Google Sheets? Yes, for datasets up to approximately 50,000-100,000 rows and a manageable number of fields. Beyond that, performance degrades and the complexity of managing formula-based quality checks makes dedicated tools worthwhile.
Q: How do you find duplicates in a spreadsheet without a tool? Use conditional formatting to highlight duplicate values in the key field (email, customer ID). Or use the COUNTIF approach described above to mark duplicates with a helper column, then filter by that column to review. Remove duplicates using the built-in Remove Duplicates function.
Q: How long does a manual data quality measurement take for a 10,000-row dataset? Setting up the formulas for five dimensions across ten fields takes approximately 30-60 minutes the first time. Running subsequent checks against the same dataset takes 10-15 minutes once the formula structure is in place.
Q: What is the main disadvantage of manual data quality measurement? Reproducibility and scale. Manual checks require someone to set them up each time, are prone to formula errors, and do not produce an automatic audit trail. They are great for a one-off audit but not for ongoing monitoring.
Q: Can you measure accuracy manually? Accuracy is the hardest dimension to measure manually because it requires comparing your data against a ground truth. You can sample the data and manually verify a random subset against an authoritative source (a website, a public directory, a customer record), then extrapolate. This gives a rough accuracy estimate but is time-intensive.
Q: How do you handle special characters or mixed formats when running manual checks? Use TRIM and CLEAN functions to remove extra spaces and non-printable characters before running your checks. Mixed formats (like mixed phone number formats) may require custom formula logic or a manual review pass before formula-based validity checks are reliable.
Q: What should you do with the results of a manual quality measurement? Record them in a findings document, flag critical and high failures, and identify the source of the problem for each finding. A manual measurement that produces only numbers without analysis is less useful than a shorter measurement with documented findings and action items.
Q: Is it possible to automate manual checks using macros or scripts? Yes. Excel macros or Google Apps Script can automate the formula application and reporting, effectively building a lightweight automated quality checker inside a spreadsheet environment. This is a reasonable middle ground between fully manual and dedicated tool.
Q: When does a manual measurement approach stop being practical? When the time to set up and run the checks exceeds the time savings they produce, or when the datasets you need to check are too large for spreadsheet performance. At that point, a dedicated profiling tool becomes the practical choice.
Manual data quality measurement is underrated. Spreadsheet formulas cover the core dimensions for most real-world datasets, and the skills required are accessible to anyone who has used formulas before.
When manual measurement becomes impractical, Sohovi takes the profiling step from 30 minutes to under one minute — running completeness, uniqueness, and validity checks on your full dataset without any formula setup required.
