How to flag rows with missing data in Excel

Q: 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.

Q: How do I count incomplete rows?

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

Q: 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").

Formula (Excel and Google Sheets)

=IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")

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

Result on the example: one result per row in E2, filled down to E7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in E2, filled down to E7. Change any input and the result updates, or edit the formula to experiment.

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.

=IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")

Result"Missing data"

A2:D21 row × 4 cols: "Omar Ruiz", (empty), "555-0187", "Austin"
A range: 4 cells in one row.
COUNTBLANK(A2:D2)1
COUNTBLANK: Counts empty cells.
- rangeA2:D21 row × 4 cols: "Omar Ruiz", (empty), "555-0187", "Austin"
COUNTBLANK(A2:D2)>0TRUE
The > operator tests greater than.
IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete")"Missing data"
IF: Returns one value if a test is true, another if false.
- logical_testCOUNTBLANK(A2:D2)>0TRUE
- value_if_true"Missing data"
- value_if_false"Complete"not used

In Google Sheets

The same formula works in Google Sheets: =IF(COUNTBLANK(A2:D2)>0,"Missing data","Complete"). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IF(COUNTA(A2:D2)<COLUMNS(A2:D2),"Missing data","Complete")	Missing data, Complete, Complete…	Counts the filled cells instead and compares with the number of columns.
=IF(OR(A2="",B2="",D2=""),"Missing data","Complete")	Missing data, Complete, 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Flag rows with missing data

In E2, show "Missing data" when any of A2:D2 is empty, otherwise "Complete". Fill down to E7.

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").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.