How to Audit Data Quality in Excel
Before you pivot, chart, or merge, check the data underneath. A quick audit surfaces the blanks, duplicates, mixed types, and hidden characters that quietly break lookups and totals.
Last updated: August 2026
| Column | Blank % | Duplicate % | Issues |
|---|---|---|---|
| 4% | 12% | hidden characters | |
| Amount | 0% | 2% | 3 numbers stored as text |
| Date | 1% | 0% | inconsistent date formats |
Method 1 — Audit with formulas
- Blanks:
=COUNTBLANK(A2:A1000)per column. - Filled count:
=COUNTA(A2:A1000). - Duplicates: add a helper
=COUNTIF(A$2:A2, A2)>1— any TRUE is a repeat. - Numbers stored as text:
=AND(ISTEXT(A2), ISNUMBER(--A2))flags a value that looks numeric but is text. - Mixed types: compare a column of
=ISNUMBER(A2)against=ISTEXT(A2)— if both appear, the column is mixed.
Thorough, but you rebuild these formulas for every column, every time.
Method 2 — Power Query data profiling
- Load the table with Data → From Table/Range.
- In the Power Query editor, open View → Data Preview and tick Column quality, Column distribution, and Column profile.
- You get valid / error / empty percentages and distinct/unique counts per column, computed on the first 1,000 rows (switch to "entire data set" at the bottom).
Method 3 — spot-check with conditional formatting
Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values colors repeats instantly, and a blanks rule (=ISBLANK(A2)) highlights gaps — handy for a fast visual scan, though it doesn't give you counts.
The 1-click way — Tellsheet
Column Health Scan writes a full quality report to a new sheet in one click: for every column it lists row count, blanks, blank %, distinct values, duplicate %, the dominant data type, and a plain-English Issues column — "mixed types", "3 numbers stored as text", "inconsistent date formats", "hidden characters", "extra spaces", "many duplicates". It's the map; Tellsheet's Cluster Values, Clean, and Fix Dates tools are the fixers for what it finds. Fully local — nothing is uploaded.
Frequently asked questions
How do I check data quality in Excel?
Profile each column for completeness and consistency: COUNTBLANK for blanks, COUNTIF for duplicates, ISTEXT for numbers-stored-as-text. Power Query's Column quality pane shows it visually, and Tellsheet's Column Health Scan produces the whole report in one click.
How do I find columns with mixed data types?
Compare ISNUMBER and ISTEXT counts down a column — if both are non-zero, it's mixed. Tellsheet's Column Health Scan detects the dominant type per column and flags mixed types, numbers-as-text, and inconsistent date formats automatically.
What is data profiling?
A summary of each column — value count, blanks, distinct values, distribution, and type — so you can catch quality problems before analysis. Power Query offers it under View → Data Preview; Tellsheet writes a profiling report to a new sheet.
Why does my VLOOKUP fail even though the values look identical?
Usually numbers-stored-as-text, trailing spaces, or hidden characters — exactly what an audit surfaces. Run Column Health Scan first, then clean the flagged column.
Related Excel guides
Audit every column in one click
Column Health Scan reports blanks, duplicates, types, and issues across your whole table — right inside Excel, with nothing leaving your computer.
Get Tellsheet free See pricing