Excel UNIQUE Function
last modified October 5, 2026
The UNIQUE function is a dynamic array function in Excel that
returns the distinct values, or the distinct rows, of a range or array. It
removes duplicates without touching the source data and spills the result into
the neighbouring cells, so the list updates automatically when the source
changes. This tutorial covers the syntax, the by_col and
exactly_once arguments, unique rows versus unique columns, ordering
and case sensitivity, counting unique values with COUNTA, and how
UNIQUE combines with SORT and FILTER.
UNIQUE Function Basics
The UNIQUE function compares the entries of a range or array and
keeps the first occurrence of every distinct value. By default it compares rows,
so a vertical list produces a vertical list of unique values and a multi-column
range produces unique row combinations.
| Component | Description |
|---|---|
| Function Name | UNIQUE |
| Syntax | =UNIQUE(array, [by_col], [exactly_once]) |
| Arguments | array: range or array to examine (required) by_col: FALSE or omitted compares rows, TRUE compares columns (optional) exactly_once: TRUE keeps values seen exactly once, FALSE or omitted keeps all distinct values (optional) |
| Return Value | A spilled array of the distinct values or rows |
| Availability | Excel for Microsoft 365, Excel 2021, Excel 2024, and Excel for the web |
The table below describes each argument in more detail.
| Argument | Meaning |
|---|---|
| array | The range or array from which to return unique rows or columns, such as
A1:A10. It can also be an array constant or the result of
another function. |
| by_col | A logical value that controls the direction of the comparison.
FALSE or omitted compares rows and returns unique rows;
TRUE compares columns and returns unique columns. |
| exactly_once | A logical value that controls which values count as unique.
FALSE or omitted returns all distinct values, that is every
value that occurs at least once. TRUE returns only the values
that occur exactly once, dropping any value seen more than once. |
UNIQUE requires a version of Excel with dynamic arrays. It works in
Excel 2021 and later and in Microsoft 365; in Excel 2019 and earlier the
function does not exist and returns a #NAME? error. Because the
result is a dynamic array, it needs enough empty cells to spill into; otherwise
Excel reports a #SPILL! error. An empty input array produces a
#CALC! error because Excel cannot display a zero-length array.
Unique Rows vs Unique Columns
The by_col argument decides whether UNIQUE compares
rows or columns. With the default, every row is treated as a single record and
only whole rows that repeat are collapsed. With by_col set to
TRUE, the roles swap and repeated columns are collapsed.
| by_col | Compares | Returns | Typical source |
|---|---|---|---|
| FALSE or omitted | Rows | Unique rows | Vertical list or multi-column table |
| TRUE | Columns | Unique columns | Horizontal list or multi-row table |
For a single column of values the two settings behave differently: the default
treats each cell as a one-column row and deduplicates the values, while
TRUE treats the whole column as one record and returns it
unchanged. To extract unique values from a single row, set by_col
to TRUE.
Basic UNIQUE Example
This example demonstrates the simplest use of the UNIQUE function with a vertical range of values containing duplicates.
| A | B |
|---|---|
| Apple | |
| Orange | |
| Apple | |
| Banana | |
| Orange | |
| =UNIQUE(A1:A5) |
The table shows a simple spreadsheet with duplicate values in column A and a
UNIQUE formula in cell B6 that extracts unique values.
=UNIQUE(A1:A5)
This formula returns a vertical array of unique values from A1:A5. The result will be "Apple", "Orange", "Banana". The function automatically removes duplicate entries.
UNIQUE with Horizontal Range
UNIQUE can work with horizontal ranges by using the by_col parameter. This example shows how to extract unique values from a row.
| A | B | C | D | E |
|---|---|---|---|---|
| Red | Blue | Red | Green | Blue |
| =UNIQUE(A1:E1, TRUE) |
This table demonstrates UNIQUE's ability to work with horizontal data. The formula in E2 extracts unique values from the color names in row 1.
=UNIQUE(A1:E1, TRUE)
This formula returns a horizontal array of unique values from A1:E1. The second argument TRUE tells Excel to compare columns rather than rows. The result will be "Red", "Blue", "Green".
UNIQUE with Exactly Once Option
The exactly_once parameter lets you find values that appear only once in the source data. This example demonstrates this advanced feature.
| A | B |
|---|---|
| London | |
| Paris | |
| London | |
| Berlin | |
| Madrid | |
| Paris | |
| =UNIQUE(A1:A6, FALSE, TRUE) |
The table shows city names with some appearing multiple times. The formula in B7 uses the exactly_once parameter to find cities listed only once.
=UNIQUE(A1:A6, FALSE, TRUE)
This formula returns only values that appear exactly once in A1:A6. The result will be "Berlin" and "Madrid". London and Paris are excluded as they appear twice.
UNIQUE with Multiple Columns
UNIQUE can extract unique combinations from multiple columns. This example shows how to get unique row combinations.
| A | B | C |
|---|---|---|
| John | Sales | |
| Mary | IT | |
| John | Sales | |
| Peter | IT | |
| =UNIQUE(A1:B4) |
The table contains employee names and departments with some duplicate combinations. The formula in C5 extracts unique name-department pairs.
=UNIQUE(A1:B4)
This formula returns unique combinations from columns A and B. The result will be three rows: John-Sales, Mary-IT, and Peter-IT. The duplicate John-Sales row is removed.
Ordering and Case Sensitivity
UNIQUE does not sort. It preserves the order in which values first
appear, so the output order mirrors the source data. Wrap the call in
SORT when you need an alphabetical or numeric order.
The comparison is case-insensitive, so apple, Apple,
and APPLE are treated as the same value and only the first one is
kept. Text and numbers are treated as different types, however, so the number
10 and the text "10" are two separate entries. Blank
cells are included in the result and normally appear as 0.
| Source values | Result | Reason |
|---|---|---|
| Apple, apple, Apple | Apple | Comparison is case-insensitive, so all three are one value |
| 10, "10" | 10, "10" | Numbers and numeric text are different types |
| "" (blank cell) | 0 | Blank cells are returned and displayed as zero |
To drop blank cells from the result, filter them out before calling
UNIQUE: =UNIQUE(FILTER(A1:A10, A1:A10<>"")).
UNIQUE with SORT Combination
UNIQUE is often combined with SORT to return sorted unique values. This example shows this powerful combination.
| A | B |
|---|---|
| Zebra | |
| Apple | |
| Banana | |
| Apple | |
| Cat | |
| =SORT(UNIQUE(A1:A5)) |
The table contains unsorted values with duplicates. The formula in B6 first extracts unique values then sorts them alphabetically.
=SORT(UNIQUE(A1:A5))
This nested formula first gets unique values from A1:A5, then sorts them. The result will be "Apple", "Banana", "Cat", "Zebra". This combination is useful for creating sorted unique lists.
Counting Unique Values with COUNTA
Wrapping UNIQUE in COUNTA counts how many distinct
values the range contains. ROWS gives the same number for a
vertical result.
=COUNTA(UNIQUE(A1:A5))
For the fruit list above, UNIQUE returns Apple, Orange, and Banana,
so COUNTA returns 3. If the result has already
spilled to the worksheet, you can also count it with a spill reference such as
=COUNTA(B6#), where # means the whole spilled range.
UNIQUE vs Remove Duplicates vs COUNTIF
These three tools overlap but solve different problems. Choose the one that matches whether you want a live list, a permanent edit, or a count of duplicates.
| Tool | What it does | Dynamic | Best for |
|---|---|---|---|
UNIQUE |
Returns a new array of distinct values or rows | Yes, it recalculates and spills | A live deduplicated list that leaves the source intact |
| Remove Duplicates | Permanently deletes duplicate rows from the selected range | No, it is a one-time edit | Cleaning data in place when the original rows are no longer needed |
COUNTIF |
Counts how many times each value appears | Yes, it recalculates | Finding duplicates or values that occur a given number of times |
A common pattern combines them. =COUNTIF(A1:A5, A1:A5) returns the
count next to every entry, and
=UNIQUE(FILTER(A1:A5, COUNTIF(A1:A5, A1:A5)>1)) returns only the
values that are duplicated.
UNIQUE with FILTER Combination
UNIQUE can be combined with FILTER to extract unique values meeting specific criteria. This example demonstrates this advanced technique.
| A | B | C |
|---|---|---|
| New York | USA | |
| London | UK | |
| Paris | France | |
| Chicago | USA | |
| Madrid | Spain | |
| =UNIQUE(FILTER(A1:A5, B1:B5="USA")) |
The table contains cities and countries. The formula in C6 extracts unique cities only from the USA by combining FILTER and UNIQUE.
=UNIQUE(FILTER(A1:A5, B1:B5="USA"))
This formula first filters column A for rows where column B equals "USA", then returns unique values from the filtered results. The output will be "New York" and "Chicago". This shows UNIQUE's power in data analysis scenarios.
UNIQUE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Opened in Excel 2019 or earlier | #NAME? error | Use Excel 2021 or later, or a legacy array alternative |
| Spill range is blocked by other data | #SPILL! error | Clear the cells the result needs, or move the formula |
| The input array is empty | #CALC! error | Ensure the source has data, or wrap the formula in IFERROR |
| Formula is placed inside an Excel Table | #SPILL! error | Keep dynamic array formulas in a normal range, outside the table |
| Source workbook is closed | #REF! error when refreshed | Keep both workbooks open for cross-workbook dynamic arrays |
When a formula may legitimately find nothing, such as
=UNIQUE(FILTER(A1:A5, B1:B5="USA")) on data without matches, the
empty result from FILTER makes UNIQUE return
#CALC!. Supply an if_empty value to
FILTER, or wrap the whole expression in IFERROR, for
example =IFERROR(UNIQUE(FILTER(A1:A5, B1:B5="USA")), "None").
The UNIQUE function brings the database idea of a distinct list to
Excel. A single reference replaces helper columns and manual deduplication, and
because the result is a spilled dynamic array it stays in step with the source
data. Use by_col to choose rows or columns, exactly_once
to keep only values that never repeat, and SORT, COUNTA,
and FILTER to sort, count, and restrict the output. Remember that
UNIQUE is case-insensitive, keeps the order of first appearance,
returns #NAME? in Excel 2019 and earlier, #SPILL! when
the target cells are occupied, and #CALC! for an empty input.
Mastering UNIQUE will make your spreadsheets shorter, clearer, and
more responsive.
Author
List all Excel Formulas.