How to Find Blank Cells in Excel

The "where's the missing data" problem

You're auditing a data block. You suspect cells are missing values. The blank cells are scattered, not clustered. You need to find them, decide what to do with each, and fill or flag accordingly.

TL;DR — Key takeaways

• F5 > Special > Blanks selects every blank cell on the active sheet.
• =COUNTBLANK() counts blanks but doesn't show where they are.
• Conditional Formatting can highlight blanks but the rule lingers.
• A VBA macro paints blank cells light red as a one-shot audit; revert with Ctrl+Z when done.

Method 1: F5 > Special > Blanks

1. Select your range.
2. Press F5 to open Go To.
3. Click Special, check Blanks, click OK. Blank cells are now selected.

The selection shows you where blanks are. You can then type a value and Ctrl+Enter to fill all selected cells with the same value.

The catch: active sheet only. The selection disappears as soon as you click somewhere else.

Method 2: =COUNTBLANK

=COUNTBLANK(A2:A100)

Returns a count, not a location. Useful for "are there blanks?" but not "where are they?"

Method 3: Conditional Formatting

1. Select range.
2. Home > Conditional Formatting > New Rule > Format only cells that contain > Blanks > pick format.

Blank cells get formatted on the fly.

The catch: the rule lingers. Recalculates as data changes.

Method 4: The free VBA macro

Download Highlight Blank Cells. Free .xlsm with one macro.

1. Select the range.
2. Alt + F8, pick the macro, click Run.
3. Every blank or whitespace-only cell turns light red.
4. Hit Ctrl/Cmd + Z to revert when you're done auditing.

Static colors. No lingering rules.

What counts as "blank"

The macro treats a cell as blank if:

• It's truly empty (IsEmpty returns True), or
• It contains an empty string "", or
• It contains only whitespace characters (spaces, tabs).

This catches the common "stealth blank" problem where a cell has a stray space and looks empty but isn't.

A common scenario: grade book audit

Teacher grading 28 students across 5 assignments. Before running grade calculations, she wants to spot any missing entries.

1. Select the score block (28 rows by 5 columns).
2. Run Highlight Blank Cells.
3. Blank cells light up.
4. Walk each one. Decide: missing entry (fill in from paper records) or excused absence (mark differently).
5. Ctrl+Z to revert highlights before printing reports.

Versus visual scanning: more reliable. Scanning misses about 5% of blanks.

Frequently asked questions

Does it count cells with formulas evaluating to blank?

Yes. A formula returning "" is treated as blank and gets highlighted.

Will it overwrite existing colors?

Yes, on the painted cells. Ctrl+Z reverts.

Can I customize the highlight color?

Edit the .bas source. The fill is RGB(255, 199, 206) and font is RGB(156, 0, 6).

What about whitespace-only cells?

Treated as blank. The macro uses Trim$ to detect them.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running.

What to do next

After finding blanks, decide what to do with each:

• Fill with the value from above: Fill Blank Cells With Value Above.
• Delete the row entirely: Remove Blank Rows.
• Type missing values manually using the F5 Blanks trick (which lets you Ctrl+Enter to fill all selected blanks with the same value).