UNIQUE Function and Blank Cells in Excel

Zero in on a problem

The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.

In the list below cell A8 is empty or blank. Cell A9 has a zero entered in it and cell A10 has a formula that returns a blank cell.

Cells A8 and A10 look the same but in Excel terms they are treated differently.

If we use the UNIQUE function on the list we see Excel returns the whole list.

But in the UNIQUE list the blank cell displays as zero and so does the zero cell.

Interestingly it doesn’t look like a unique list as there are two zeroes.

I thought maybe using two UNIQUE functions would get rid of the double zero, but I was wrong.

Then I thought maybe a separate UNIQUE using the UNIQUE spill range will remove the double zeros – and it did – interesting.

If your list has blanks and zeroes and you only want one zero to appear you might need to use two separate UNIQUE functions.

If you have a cell with a formula that returns a blank cell and an empty cell these two cells will be treated differently. The empty cell will return zero and the cell with a formula will display as a blank cell in the unique list – see example below.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.