conditional formatting to detect nulls

Topic

New Reply

is it possible to highlight cells with no data (null)? Conditional Formatting doesn’t provide this option, which is something of a fail, in my humble opinion

Reply | Quote

Replies

If you want to highlight cells that contain neither a value nor a formula:
– Select the range.
– Note the address of the active cell within the selection; let’s say it is B3.
– Select Format | Conditional formatting…
– Select Formula Is from the first dropdown.
– Enter the formula =ISBLANK(B3) in the box next to it, using the cell address noted above.
– Click Format…
– Specify the pattern color that you want.
– OK your way out.

Reply | Quote

thanks – that works, but it’s a little strange… if i highlight a large range and enter =ISBLANK(A1:O26) <> Excel happily applies formatting across the entire dataset. weirdness.

Reply | Quote

You should use

=ISBLANK(A1)

where A1 is the active cell within the selection (usually the upper left corner). Excel will automatically adjust the cell reference for the other cells within the selection. Do *not* use

=ISBLANK(A1:O26)

where A1:O26 is the selected range!

Reply | Quote

You should use

=ISBLANK(A1)

where A1 is the active cell within the selection (usually the upper left corner). Excel will automatically adjust the cell reference for the other cells within the selection. Do *not* use

=ISBLANK(A1:O26)

where A1:O26 is the selected range!

Another way with similar approach :

Reply | Quote

Another way with similar approach :

The result is not quite the same. The suggestion I posted will highlight cells that are really blank, i.e. they contain neither a constant value nor a formula. Yours will also highlight cells that contain a formula resulting in an empty string.
Which one to use depends on what the original poster intended – one method is not better or worse than the other, just different.

Reply | Quote