The COUNTBLANK function allows you to total the number of blank or empty cells in a specified range.

It is used as show n below in the example. Cells C3, B5 and D5 are empty – so the COUNTBLANK function in D7 returns 3.

You may have a situation where the array is quite large and it is important that each cell has a value. To make it easier to trap blank cells, you can combine the COUNTBLANK inside an IF function…

=if(COUNTBLANK(A1:A500)>0,”Blank Cell”,average(A1:A500))

Conditional formatting would also help you spot blank cells.


Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog – below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.