Before submitting a form or importing data, confirm every required cell is filled. COUNTBLANK counts the empties in a row, and a flag marks records that are incomplete.
The example
Email left blank → incomplete.
| A | B | |
|---|---|---|
| 1 | Record | Status |
| 2 | Row 5 (all filled) | Complete |
| 3 | Row 6 (no email) | Missing fields |
The formula
The formula:
How it works
How it works:
COUNTBLANK(required_range)counts the empty cells among the required fields.- Zero blanks → Complete; one or more → flag for follow-up.
- Show which are missing by counting per field, or by listing the blank headers.
- Beware: a cell holding
""(empty text from a formula) isn’t blank to COUNTBLANK in all cases — test with=A2=""if unsure.
Tell the user what’s missing, not just that something is. Beyond a Complete/Incomplete flag, list the gaps: =TEXTJOIN(", ", TRUE, IF(required_cells="", header_row, "")) (array) names every empty required field. A form that says “Missing: Email, Phone” gets fixed; one that just says “Incomplete” gets ignored.
Try it: interactive demo
Required fields (leave some blank).
Variations
Count missing
How many empty:
List missing fields
Name the gaps (array):
All records complete?
Whole sheet:
Pitfalls & errors
Empty text vs blank. A formula returning "" may not count as blank — test with =A2="".
Spaces aren’t blank. A cell with a space is “filled” — TRIM-check if needed.
Define required. Point the range only at the cells that must be filled.
Practice workbook
Frequently asked questions
How do I check required fields are filled in Excel?
How do I list which fields are missing?
Why does a blank-looking cell count as filled?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class