How to Highlight Blank Cells in Excel

To highlight blank cells in Excel, use a conditional formatting rule that colours any empty cell in your range. It takes about a minute, and the colour disappears by itself as soon as someone fills the cell in. This guide shows the quick built-in rule, formula rules for whole rows and "fake" blanks, and a one-off method with Go To Special, using a small staff list with gaps.

Here is the sample data in A1:D7:

How to highlight blank cells with a Blanks rule#

The built-in Blanks rule needs no formula:

1. Select the data, A2:D7. Leave out the headers.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Format only cells that contain.
4. In the first box, change Cell Value to Blanks.
5. Click Format, pick a fill colour on the Fill tab, and click OK twice.

The six empty cells turn coloured. Type a value into one of them and its colour goes away. This works the same way on Windows and Mac. Behind the scenes, Excel stores this rule as a formula that trims the cell and checks its length, so cells holding only spaces, or a formula that returns "", are highlighted too. To do the reverse, and colour cells that have something in them, choose No Blanks in step 4.

If conditional formatting is new to you, start with the beginner's guide to conditional formatting.

How to highlight blank cells with a formula#

A formula rule gives you more control. Select A2:D7, go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter one of these:

Write the formula for the top-left cell of the range, A2, with no $ signs. Excel adjusts it for every other cell, so B3 checks B3 and D7 checks D7.

Which one should you use? ISBLANK is TRUE only when a cell is completely empty. A cell with a formula like =IF(C2>0,C2,"") shows nothing but isn't empty, so ISBLANK ignores it, while =A2="" catches it. The IS functions guide explains ISBLANK, ISTEXT and ISNUMBER, and the IS functions lesson lets you test them on real cells.

How to highlight the whole row when a cell is blank#

To colour every row that has a gap, check the row instead of one cell. Select A2:D7 and use:

=COUNTBLANK($A2:$D2)>0

COUNTBLANK counts the empty cells in the row. If there's at least one, the whole row is highlighted: Ben, Carmen, Emma and Femi. The $ before A and D locks the columns, so every cell in the row looks at the same four cells. The row number has no $, so each row checks itself.

To flag rows missing one particular value, such as the manager, lock just that column:

=$D2=""

That colours Carmen's and Femi's rows. For more on locking columns like this, see how to write conditional formatting formulas.

How to stop the rule colouring empty rows below your data#

If you apply a blanks rule to whole columns, such as A:D, every empty row below your data turns coloured too. Either apply the rule only to the rows you use, or add a check that the row has data:

=AND($A2<>"",B2="")

This highlights a blank cell only when the Employee column in that row is filled in. New rows get checked as soon as you type a name. Turning the range into an Excel table (Insert > Table) also helps, because the rule grows with the table.

How to select and colour blanks with Go To Special#

For a one-off check, Go To Special selects every empty cell at once:

1. Select A2:D7.
2. Press F5 or Ctrl+G (on Mac, Ctrl+G also works), then click Special. You can also use Home > Find & Select > Go To Special.
3. Choose Blanks and click OK.
4. With the blanks selected, apply a fill colour from the Home tab.

This colour is plain formatting, not a rule. It stays even after someone fills the cell in, so use conditional formatting when the sheet is still changing. Go To Special is also the first step to fill blanks with a value or with the cell above; the guide to filling blank cells shows how.

How to count blank and filled cells#

Counting first tells you how big the problem is, and gives you a check after you clean up:

Blanks plus filled cells should equal all cells: 6 + 12 = 18. If they don't add up, some cells contain something you can't see. Watch out for one difference: COUNTBLANK counts formulas that return "" as blank, while COUNTA counts them as filled. The COUNTA function guide and the COUNT, COUNTA and COUNTBLANK lesson cover these functions in detail.

Why blank cells aren't highlighting#

If a cell looks empty but stays white, your rule is probably stricter than you think. ISBLANK and =A2="" rules treat some invisible content as a real value. The usual causes:

• A space or a few spaces. Someone pressed the spacebar to "clear" the cell. Use =LEN(TRIM(A2))=0 or the built-in Blanks rule, or select the cells and press Delete to clear them properly.
• A formula returning "". ISBLANK skips it. Use =A2="" or the Blanks rule.
• An apostrophe. A lone ' can make a cell count as filled for ISBLANK. Clear the cell.
• A zero hidden by formatting. A number format can hide 0, but 0 is not blank. Check the formula bar.
• The rule's range is wrong. Open Home > Conditional Formatting > Manage Rules and check the Applies to box.

The same rule ideas work for text, too: see how to highlight cells that contain specific text.

Practise it#

Try the exercise on this page: count the gaps in the staff list with COUNTBLANK, then picture the rule that would colour them. Next, practise conditional formatting in a live spreadsheet, check what's really in a cell with ISBLANK and other IS functions, and count filled and empty cells with COUNTA and COUNTBLANK. Each lesson checks your work instantly.