Highlight rows with blank cells

Highlight Rows with Blank Cells

The Excel COUNTBLANK function returns a count of all the empty cells in a range. Cells that contain text, numbers, errors, etc. are not counted. The COUNTBLANK function can also be used to highlight rows with blank cells as a custom formula in conditional formatting in Excel.

Formula

= COUNTBLANK (range)

Where,

Range argument is required and it consists of cells range where you want to count blank cells.

Explanation

For highlighting rows with blank cells, you can use conditional formatting with the help of a simple formula that is based on Excel COUNTBLANK function. Once you have saved the rule, the rows with blank cells will be highlighted.

This works as follows:

  • Conditional formatting is ideally applied to every cell in the active selection at the time the rule creation takes place.
  • Here, the column references have been locked so as to prevent columns from altering as the formula is assessed, but the row references remain relative so that row numbers are given enough freedom to change. The result is a formula that applies the same logic to every cell in the same row.
  • If COUNTBLANK function comes across any blank cells in a specific row, it gives out a positive number, which Excel finds to be TRUE, triggering the rule.
  • If COUNTBLANK is unable to find any blank cell, it gives out a zero, which is assessed as FALSE, and the formatting isn’t triggered.

Example

In this example, we have details of students like their Name, Registration Number and Course. To highlight rows with blank cells, the following formula is used in Conditional formatting using Excel COUNTBLANK function;

=COUNTBLANK($B5:$E5)

This works as follows:

  • Conditional formatting applies to each active cell in the selected range when applying the rule.
  • If COUNTBLANK function comes across any blank cells in a row of the selected range, it gives out a positive number, which Excel finds to be TRUE, triggering the rule. In this example, row 6 and 9 have blank cells. Thus they are highlighted.
  • If COUNTBLANK does not find any blank cell in a row of the selected range, it gives out a zero, which is assessed as FALSE, and the formatting isn’t triggered. In this example, rows 5, 7 and 8 do not have any blank cells.

Figure1. Example to highlight rows with blank cells in Excel 1

Notes

It’s important that the COUNTBLANK function formula is entered relative to the “first cell” in the selection, B5 in the above example.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar