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.

=UNIQUE(IF(A1:A12=””,””,A1:A12))

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.

Avoid the #DIV/0! Error in Excel

The LAMBDA function to test the solution in cell E1 is.

=LAMBDA(rng,UNIQUE(IF(rng=””,””,rng)))(A1:A12)

To convert this to a custom function we copy this part of the formula.

 =LAMBDA(rng,UNIQUE(IF(rng=””,””,rng)))

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.