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.
Instead of using the first UNIQUE as a helper column for the second UNIQUE, I believe this single formula will produce the same spilled result…
=UNIQUE(FILTER(A2:A11,NOT(ISBLANK(A2:A11))))
Thanks Rick
you could replace empty cells with a spaces (spacebar 🙂 ) and you can use function normal
Spaces are a whole other problem.
In general avoid using space(s) by themselves in a cell.
In regards to your first two observations (UNIQUE not collapsing the numeric zero and the zero produced from the blank cell to a single zero)… Excel does not convert the blank cell to a zero until it is outputted from the formula, hence, UNIQUE does not see the blank cell’s zero because it is still working on the range and has not been outputted yet for the conversion to take place, so UNIQUE still sees the blank as a blank, not as a zero. The same goes for when you nested the two UNIQUE’s… the formula has not outputted the blank while each UNIQUE is working. That also explains why your third observation (applying the second UNIQUE to the output range from the first UNIQUE)… at that point, there are two physical zeros in the range and that second UNIQUE sees both zeros and, thus, collapses them to a single zero.
Hi Rick
Thanks very much for that explanation, it makes the outcome clearer.
It also explains an interesting thing Owen Price identified on a LinkedIn comment when using ISBLANK in combination with UNIQUE.
Thanks again.
Excel has a problem with “blank” cells.
There are true blank cells, which are totally empty, no space, no “”, no formula, just null.
There are cells that look blank, such as the formula that returns “”. But “” is text, and is treated in some cases like a zero and in others like a blank cell. And a true blank cell is also treated as either a null value or a zero, and it sometimes is turned into a zero.
No function returns a true blank, so we need to rely on “”, or NA() if preparing chart source data. Some years ago, there was a discussion about creating a BLANK() or NULL() function, but apparently it would have broken the calc engine and melted your hard drive. Or something.
I never rely on ISBLANK, because I’m never sure how it treats blanks and “” (I must have gotten burned in the past). I always test whether LEN() is zero, which works the same for blank cells and “”.
Rick’s comment is very astute, as always.
Hi Jon
Thanks for commenting.
Yes Excel as a whole struggles with empty cells and any cells that look blank.
Empty cells can stop some shortcuts from working as well.
I also avoid ISBLANK in most cases and check for “”.
Yes Rick’s response was very clear and explains why we end up with two zeroes.
Thanks again
Neale