The UNIQUE function in Excel has a problem when it comes to handling blank cells. Blank cells are treated as zero and if you have a blank cell and a zero in a range then UNIQUE will return two zeros. Also, if a cell has a function that returns a blank cell, then that is treated differently than a blank cell. Let’s create a custom function to fix UNIQUE’s blank cell blind spot.
You can download the example file at the button at the bottom of this post.
The range A1:A12 has text, values, a zero, blank cells and a formula that returns a blank cell in A12.
As you can see in the image below the UNIQUE function in cell C1 returns two 0’s – not unique at all.
The formula in cell D1 in the image below treats blanks correctly.
The formula in cell D1 is.
We can use this formula to create a create a custom function to return unique entries and handle blank cells correctly.
If you are new to custom functions the blog post below explains them.
The LAMBDA function to test the solution in cell E1 is.
To convert this to a custom function we copy this part of the formula.
Click the Formulas tab and click the Define Name button.
The final solution is in cell F1 in the image below.
I use a prefix of fn for all my custom functions. This differentiates them from normal functions.