How to Fix #SPILL! Error of Unique Function in Excel

In this article, Buffcom.net wants to share with you a small error that may occur when using the unique function in Excel, which is the #SPILL! error of the Unique function in Excel.

How to Fix #SPILL! Error of Unique Function in Excel

This error is quite rare, but in my opinion, it’s worth sharing. You never know, maybe 1% of you will encounter this error and need a solution.

Suppose we have a spreadsheet like this:

First, we will remove duplicates from column A. We use the unique function to keep only the unique values in column A. Before removing duplicates, we use conditional formatting in Excel to highlight the duplicate values for you to preview.

Now, in cell B1, we will use the unique function with the syntax =UNIQUE(A1:A8)

When we press enter to execute the function, an error appears. You can see that the unique function needs empty space to execute, but there are already values below, and the unique function cannot overwrite them.

To fix this error, you just need to create enough empty space for the unique function to return the values.

That’s it! We hope this article helped you understand and solve the #SPILL! error of the unique function in Excel.