Data Quality Checks in Excel: Formulas That Flag Bad Rows
By ExcelReady TeamUpdated 6 min read
The short answer
Data quality checks in Excel are formulas that test each row and flag problems before you analyse the data. Use ISNUMBER to catch numbers stored as text, COUNTIF to find duplicate IDs, LEN to check lengths and spaces, and a blank test for missing values. Combine them with AND inside IF to show OK or Check on every row.
Key takeaways
- Test one rule per formula first, then combine the rules with AND inside IF for a single status column.
- ISNUMBER(D2) is FALSE for a number stored as text, which SUM and SUMIF quietly skip.
- COUNTIF($A$2:$A$7,A2)>1 flags every row whose ID appears more than once.
- LEN checks fixed-length codes, and LEN(B2)<>LEN(TRIM(B2)) catches extra spaces.
- Count the flagged rows at the top of the sheet so problems can't hide below the fold.
On this page+
Data quality checks in Excel are simple formulas that test every row and flag the ones that would break your analysis: blanks, numbers stored as text, duplicate IDs, codes of the wrong length and stray spaces. You write one test per rule, then combine them into a status column that says OK or Check. This guide is for analysts, admins and anyone who receives exports and imports and needs to trust the numbers before building a report.
Why run data quality checks before you analyse?#
Bad rows rarely cause an error message. They cause wrong answers. A number stored as text is skipped by SUM, a duplicate ID is counted twice, and "North " with a trailing space is missed by COUNTIF. The total looks fine and is wrong.
A checks column catches these problems in seconds and keeps working when new data is pasted in. All examples use this customer list in A1:D7:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Customer | Amount | |
| 2 | C-1001 | Ana Ruiz | ana@example.com | 250 |
| 3 | C-1002 | Ben Cole | ben@example.com | 180 (stored as text) |
| 4 | C-1003 | chloe@example | 95 | |
| 5 | C-1004 | Dev Shah | dev@example.com | 410 |
| 6 | C-1002 | Ben Cole | ben@example.com | 180 |
| 7 | C-105 | Ema Lund (with a leading space) | ema@example.com | −20 |
Every row except 2 and 5 has at least one problem. The formulas below find them all.
Check for blanks and missing values#
A missing value is the easiest problem to test. For one cell, use ISBLANK or compare with empty text:
=ISBLANK(B4)
=B4=""Both return TRUE for row 4, where the customer name is missing. The difference: ISBLANK is FALSE for a cell that holds a formula returning "", while B4="" is TRUE for both. For a count, =COUNTBLANK(B2:B7) returns 1. The guide to ISBLANK, ISNUMBER and ISTEXT explains why a cell that looks empty can still fail ISBLANK.
Check for numbers stored as text with ISNUMBER#
A number stored as text looks normal but behaves like a word. ISNUMBER catches it:
=ISNUMBER(D3)This returns FALSE for row 3, because 180 was imported as text. Real numbers, including negatives, return TRUE. To count the bad cells in a column, subtract the numbers from all filled cells:
=COUNTA(D2:D7)-COUNT(D2:D7)The result is 1. The ISNUMBER function guide covers the details. To fix the values, use Data > Text to Columns > Finish on the column, or VALUE in a helper column. The guide to cleaning messy data in Excel walks through both.
Find duplicate IDs with COUNTIF#
An ID should appear once. COUNTIF counts how many times each ID appears in the whole column:
=COUNTIF($A$2:$A$7,A2)>1Fill it down. Rows 3 and 6 return TRUE, because C-1002 appears twice. The $ signs lock the range so it doesn't shift as you fill (press F4 on Windows or ⌘T on a Mac to add them). To count how many rows share an ID, use =SUMPRODUCT(--(COUNTIF(A2:A7,A2:A7)>1)), which returns 2. The COUNTIF function guide covers the criteria rules.
Check lengths, spaces and formats with LEN#
LEN counts characters, which makes it a quick test for fixed-length codes and hidden spaces:
| Check | Formula | Row that fails |
|---|---|---|
| ID must be 6 characters | =LEN(A7)=6 | Row 7: C-105 has 5 |
| No extra spaces in the name | =LEN(B7)=LEN(TRIM(B7)) | Row 7: leading space |
| Email has @ and a dot after it | =ISNUMBER(SEARCH("@*.*",C4)) | Row 4: no dot after @ |
| Amount can't be negative | =D7>=0 | Row 7: −20 |
TRIM removes extra spaces, so if the length changes, the cell had some. The email test uses SEARCH with wildcards: * stands for any characters, so "@*.*" means "an @, then later a dot". SEARCH returns a position or an error, and ISNUMBER turns that into TRUE or FALSE; see how ISNUMBER and SEARCH check for text. The LEN function guide has more length tests.
This email check is a rough filter, not full validation. It catches obvious typos, which is usually enough.
Combine the checks into one status column#
Once each rule works, combine them with AND inside IF. AND is TRUE only when every test passes. In E2:
=IF(AND(B2<>"",ISNUMBER(D2),COUNTIF($A$2:$A$7,A2)=1,LEN(A2)=6),"OK","Check")Fill it down. Rows 2 and 5 show OK; rows 3, 4, 6 and 7 show Check. Add or remove tests inside AND to match your own rules.
A status of "Check" doesn't say why. In Microsoft 365 or Excel 2019 and later, TEXTJOIN can list the reasons:
=TEXTJOIN(", ",TRUE,IF(B2="","No name",""),IF(ISNUMBER(D2),"","Amount is text"),IF(COUNTIF($A$2:$A$7,A2)>1,"Duplicate ID",""))Row 3 shows Amount is text, Duplicate ID. TRUE as the second argument skips the empty results, so clean rows stay blank.
Finally, put a summary at the top of the sheet: =COUNTIF(E2:E7,"Check") returns 4. Pair it with a conditional formatting rule that fills Check rows in light red, and problems are hard to miss.
Prevent bad data at the source#
Checks find problems after the fact. Where people type data in by hand, stop the problems first with Data > Data Validation: allow only whole numbers in Amount, set a text length of 6 for IDs, or offer a drop-down list. The guide to data validation rules shows each setting. Keep the checks column anyway, because pasted data skips validation.
The same approach works for any list you receive, from a CRM export to survey results you need to summarise.
Practise it#
Each check here uses a skill you can practise on its own. Practise IS functions in a live spreadsheet to test values with ISNUMBER and ISBLANK, use the COUNTIF lesson to find duplicates, and fix what you find in the cleaning messy text lesson. Every lesson checks your formula as soon as you press Enter.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
In E3, check row 3 with IF and AND: it is OK only if the amount (D3) is a real number, the ID (A3) appears once in $A$2:$A$7, and the ID is 6 characters long. Otherwise show Check.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Status | |||||
| 2 | ||||||
| 3 | 180 | |||||
| 4 | C-1003 | |||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
Tip: AND takes the three tests separated by commas: ISNUMBER(D3), COUNTIF(...)=1 and LEN(A3)=6.
Practise this in a lesson
Frequently asked questions
How do I check data quality in Excel?
Add a helper column with formulas that test each rule: blanks, numbers stored as text, duplicates, wrong lengths and values out of range. Combine the tests with AND inside IF so each row shows OK or Check, then count the Check rows.
How do I find numbers stored as text in Excel?
Use =ISNUMBER(D2), which returns FALSE for a number stored as text. To count them in a column, use =COUNTA(D2:D7)-COUNT(D2:D7), which counts filled cells that are not real numbers.
How do I flag duplicate IDs with a formula?
Use =COUNTIF($A$2:$A$7,A2)>1 and fill it down. It returns TRUE on every row whose ID appears more than once. Lock the range with $ so it doesn't move as you fill.
What is the difference between data validation and data quality checks?
Data validation stops bad values from being typed in the first place. Data quality checks are formulas that find problems in data that is already there, such as an import or a pasted export.