Excel UNIQUE Function: Syntax, Examples and #SPILL! Fixes

UNIQUE needs Excel 2021, 2024 or Microsoft 365

Microsoft's UNIQUE page lists Excel for Microsoft 365 on Windows and Mac, Excel 2024, Excel 2021, Excel for iPad and iPhone, and Excel for Android tablets and phones. Excel 2019 and Excel 2016 are not on the list, so a workbook that uses UNIQUE will show errors there.

The function returns "a list of unique values in a list or range". It does not change the source. That makes it the safe option when you want a clean list next to your data and still need the original 100% intact.

The 3 arguments: array, by_col and exactly_once

The syntax is =UNIQUE(array,[by_col],[exactly_once]). Only the first argument is required.

• array: the range or array to read. It can be a single row, a single column or a block of both.
• by_col: FALSE or omitted compares rows and returns unique rows. TRUE compares columns and returns unique columns.
• exactly_once: FALSE or omitted returns every distinct row or column. TRUE returns only those that occur exactly once in the source.

A worked example: 8 names become 4, then 1

These are sample values. Put the names Ben, Anna, Ben, Dev, Cara, Ben, Anna, Cara in cells A2:A9. The count is Ben 3, Anna 2, Cara 2, Dev 1.

Type =UNIQUE(A2:A9) in C2 and press Enter. Excel fills C2:C5 with Ben, Anna, Dev, Cara, which is 4 cells. The order follows the first time each name appears.

Now type =UNIQUE(A2:A9,FALSE,TRUE). The result is one cell, Dev, because Dev is the only name that occurs once. Microsoft uses the same switch to list customers who used a service only once, for example to follow up with them.

Wrap UNIQUE in SORT for an alphabetical list

UNIQUE keeps source order. For A to Z, nest it: =SORT(UNIQUE(A2:A9)). On the sample, the 4 cells become Anna, Ben, Cara, Dev.

SORT is =SORT(array,[sort_index],[sort_order],[by_col]). The sort_order is 1 for ascending, which is the default, and -1 for descending. So =SORT(UNIQUE(A2:A9),1,-1) gives Dev, Cara, Ben, Anna. SORT is available in the same versions as UNIQUE.

If you need to sort by another column, Microsoft's SORTBY function takes a second range as the sort key.

Two columns at once: join them or pass a block

A block such as =UNIQUE(A2:B12) compares whole rows, so a row counts as a repeat only when both cells match. Anna in column A with Lee in column B is different from Anna with Ray.

Microsoft's third example joins first and last names with the & operator: =SORT(UNIQUE(B2:B12&" "&A2:A12)). That returns one column of full names, sorted. Choose the block form when you need both columns back, and the & form when you want one combined list.

Distinct is not the same as unique in the database sense

Many people expect "unique" to mean values that are not repeated. By default, UNIQUE returns each value once, so a name that appears 3 times still shows up. Only exactly_once TRUE gives the database meaning of unique.

Microsoft's page does not say whether the comparison is case-sensitive, and it does not describe how blank cells are handled. Test both on a copy of your data before you rely on them.

Google Sheets has its own UNIQUE(range, by_column, exactly_once), with both optional arguments defaulting to FALSE. Its help page warns that rows that look the same may differ by hidden text such as trailing spaces, and that numbers should be formatted the same way. The same advice applies in Excel when a duplicate refuses to merge.

Google's sample: 4 rows in, 3 rows out, then 2

Google's help page uses a 4-row block in B2:D5: Red, Yellow, Red; Blue, Magenta, Blue; Red, Yellow, Red; Green, White, Green. =UNIQUE(B2:D5) returns 3 rows, because the third row repeats the first. =UNIQUE(B2:D5,FALSE,TRUE) returns 2 rows, Blue, Magenta, Blue and Green, White, Green, since the Red, Yellow, Red row occurs 2 times and is dropped entirely.

Google's by_column switch flips the direction: =UNIQUE(B2:D5,TRUE) compares the 3 columns instead of the 4 rows. The third column repeats the first, so the result is 4 rows of 2 columns. Excel's by_col argument works the same way.

5 causes of #SPILL! and what to do about each

UNIQUE spills into the cells below or beside it. If it cannot, Excel shows #SPILL!. Select the cell and a dashed border shows the range it wants.

If a #SPILL! error is on your screen right now, Crade can read the screen and point to the cell that blocks the range.

• The spill range is not blank: use Select Obstructing Cells in the error menu, then delete or move what is in the way.
• Merged cells in the range: unmerge them or move the formula.
• The formula is inside an Excel table: spilled arrays are not supported in tables. Move the formula out, or use Table Design > Tools > Convert to range.
• The result would pass the worksheet edge: move the formula up or reference fewer cells.
• Out of memory: point the formula at a smaller range.

Tables and closed workbooks change what refreshes

If your source is an Excel Table and you use structured references, the spill range resizes by itself when rows are added or removed. Keep the UNIQUE formula outside the table, because of the #SPILL! rule above.

Across workbooks, dynamic arrays work only while both files are open. If the source workbook is closed, linked formulas return #REF! on refresh.

UNIQUE or Remove Duplicates: which one suits you

UNIQUE suits a live list that updates when the data changes, in a version from the list above. Remove Duplicates on the Data tab suits a one-time clean-up, and it permanently deletes rows. It also compares displayed text, so "3/8/2006" and "Mar 8, 2006" count as different values.

On Excel 2019 or older, Remove Duplicates or the Advanced filter with Unique records only are the options that exist.