The Problem
A contact list is only useful when the key fields are filled in. Missing phone numbers and email addresses turn a normal sheet into a follow-up project because the team cannot rely on it when they need to call or message someone quickly.
The hard part is not spotting one empty cell. The hard part is measuring the gaps across the whole list and seeing exactly which rows still need work. A useful cleanup sheet should show the missing-data totals and also create a row-level status that people can filter later.
The flow below shows how those two layers work together. The summary counts tell you how big the data-quality problem is, while the row flag tells you where to start fixing it.
The Problem: Measuring Missing Contact Details A cleanup sheet needs both missing-field counts and row-level follow-up flags.
In this workbook, each team member has phone and email fields, and some of those cells are empty. The challenge is to count the missing phone and email values, then mark the email status for each row so the incomplete records are easy to find.
- The summary shows how many phone numbers are missing.
- The summary also shows how many email addresses are missing.
- The status column turns the email blanks into a practical follow-up list.
That connection matters. A count alone tells you there is a problem, but the row flag tells the team which records need action.
How We Solve It
We handle the task from two angles.
- First, we count how many blanks exist in the phone and email columns.
- Second, we flag each row based on whether the email field is empty.
That gives us a clean summary at the top and an action list inside the table.
Method 1: COUNTBLANK for the totals
Method 1: Count empty cells in one column with COUNTBLANK.
COUNTBLANK is the fastest way to answer a simple question like, "How many phone numbers are missing?" You point it at a range and it returns the number of empty cells. That is exactly what we need for the summary section in this challenge.
This solves the summary part of the problem because it turns a messy visual scan into one clear count. Instead of checking each row by eye, the worksheet can return the total number of missing phone or email entries right away.
=COUNTBLANK(B2:B6)
Method 2: Compare counts with COUNTA
Method 2: Compare a full roster to a partly filled column to estimate missing entries.
If one column should have a value for every person, you can compare the filled roster to the filled data column. That pattern is useful when your sheet structure makes a direct blank count less convenient or when you want to explain the gap in terms of missing records.
This solves the same counting problem from another angle. By comparing how many people exist in the roster to how many actual entries are present in the contact column, the difference shows how many records are still incomplete.
=COUNTA(A2:A6) - COUNTA(C2:C6)
Method 3: Flag each row with ISBLANK
Method 3: Mark each row so missing data is easy to filter later.
The summary tells us how much is missing, but the status column tells us where to look. By combining ISBLANK with IF, we can label each row as MISSING or OK. Once that column is filled, filtering the problem rows becomes easy.
This solves the row-level part of the problem. Instead of only knowing that some records are missing data, we can see exactly which rows need follow-up, which makes the cleanup list much more useful in practice.
=IF(ISBLANK(C2),"MISSING","OK")
Function Explanation
1. COUNTBLANK
COUNTBLANK counts empty cells inside a range. It is direct and readable, which makes it a great fit for the summary formulas in this challenge.
Here, it helps separate the cleanup problem into measurable pieces. Phone blanks and email blanks can be counted independently, so the team knows which field needs more attention.
2. COUNTA
COUNTA counts non-empty cells. When you subtract the filled email count from the total roster count, the difference shows how many records are incomplete.
This is useful when the roster column is the source of truth and another field is supposed to be filled for every person. It gives another way to explain the gap.
Learn more this functionCOUNTA
3. ISBLANK
ISBLANK checks whether a cell is empty. In this challenge, it powers the row-level flag so the sheet does more than just count the gaps.
That row-level flag is what makes the summary actionable. Once each row says MISSING or OK, the cleanup work is easier to filter and assign.
One thing to watch for is fake blanks. A cell that contains a space is not truly blank, so a quick cleanup step may be needed if imported data looks empty but does not count as empty.
Count the missing phone numbers and email addresses first, then fill the status column so each row clearly shows whether the email field is complete or still missing.