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.
= COUNTBLANK (range)
Range argument is required and it consists of cells range where you want to count blank cells.
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.
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;
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
It’s important that the COUNTBLANK function formula is entered relative to the “first cell” in the selection, B5 in the above example.