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.