# How to flag rows with missing data in Excel

Count the empty cells in the row: =IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete").

**Excel:** `=IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Name | Email | Phone | City | Check |
| Omar Ruiz |  | 555-0187 | Austin |  |
| Lena Park | lena@corp.example | 555-0141 | Denver |  |
| Noor Haddad | noor@corp.example | 555-0122 | Boise |  |
| Tom Becker | tom@corp.example |  |  |  |
| Priya Shah | priya@corp.example | 555-0199 | Reno |  |
|  | ines@corp.example | 555-0160 | Tulsa |  |

Formula in E2, filled to E7: `=IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")`

Result: Missing data; Complete; Complete; Missing data; Complete; Missing data

## How it works

COUNTBLANK counts the empty cells in the row. Anything above zero means at least one field is missing, whichever column it's in, so one formula covers the whole row.

## Other ways

- `=IF(COUNTA(A2:D2)<COLUMNS(A2:D2),"Missing data","Complete")`: Counts the filled cells instead and compares with the number of columns.
- `=IF(OR(A2="",B2="",D2=""),"Missing data","Complete")`: Checks only the required columns (here name, email and city) when some fields are optional.

## Common mistakes

- A cell holding a space looks empty but isn't blank, so COUNTBLANK misses it. Clean imports with TRIM.
- COUNTBLANK counts formulas that return "" as blank, while COUNTA counts them as filled.

## Questions

**How do I list which fields are missing?**

In Excel 365: `=TEXTJOIN(", ",TRUE,IF(A2:D2="",$A$1:$D$1,""))` returns names like "Phone, City". In Google Sheets wrap it in ARRAYFORMULA.

**How do I count incomplete rows?**

Count the flags: `=COUNTIF(E2:E7,"Missing data")`.

**How do I check only the required fields?**

Point COUNTBLANK at those columns, for example email and phone: `=IF(COUNTBLANK(B2:C2)>0,"Missing data","Complete")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/flag-rows-with-missing-data