Unique in Excel: Get Values Without Duplicates

To list each value once in Excel for Microsoft 365, enter =UNIQUE(A2:A10) in an empty cell, replacing A2:A10 with your data range. Excel returns a dynamic list of distinct values and updates it when the source data changes. If you want to delete duplicates from the original data instead, use Data > Remove Duplicates.

What UNIQUE means in Excel

Excel’s UNIQUE function creates a list that contains one copy of each different value in the selected array. For example, if a column contains Red, Blue, Red, and Green, the result is Red, Blue, Green. By default, the function keeps the values in the order they first appear; it does not sort them alphabetically.

This is often called getting a unique list, but there are two related meanings worth separating:

Distinct values: show each value once, even if it appears several times in the source. This is the default behavior of UNIQUE.
Values appearing exactly once: show a value only if it occurs one time in the source. Use UNIQUE’s exactly_once argument for this.

These are different questions. If a customer name appears five times, a distinct list includes the name once. An exactly-once list leaves it out because it was repeated. Choosing the right meaning prevents a common reporting mistake.

Use the UNIQUE formula step by step

With names or other values in A2:A10, and an empty cell available for the results, enter =UNIQUE(A2:A10). Excel displays the distinct values in cells below the formula. The example formula assumes the source occupies A2:A10; its result depends on the actual values in those cells.

Click an empty cell that has room below it for the result.
Type the formula, changing A2:A10 to match the source range.
Press Enter. Excel spills the returned values into neighboring cells automatically.
Use the list as a report, a source for another formula, or a quick way to inspect categories in the data.

For a concrete example, suppose A2:A8 contains Pear, Apple, Pear, Banana, Apple, Plum, and Banana, in that order. With that layout, =UNIQUE(A2:A8) returns Pear, Apple, Banana, and Plum. Each appears once, preserving its first-seen order. You enter the formula only in the first result cell; do not copy it down as though it were a traditional row-by-row formula.

The cells that display the output form a spill range. Excel marks the entire range when you select the formula cell. If cells in the intended output area are occupied, Excel may show a #SPILL! error. Clear the obstructing cells or move the formula to a larger empty area.

Return values that occur exactly once

To exclude every value that repeats in the source, use the optional exactly_once argument. With Pear, Apple, Pear, Banana, Apple, Plum, and Banana in A2:A8, enter =UNIQUE(A2:A8,,TRUE). The blank second argument leaves by_col at its default, and TRUE asks Excel to return only items that occur once. For this example, the result is Plum: it occurs once, while Pear, Apple, and Banana each occur twice.

This is useful for finding one-off entries, such as an order ID that appears only once in a transaction list. It is not the formula to use when you simply want a clean category list. For that more common task, use =UNIQUE(A2:A8) with the same A2:A8 layout; it returns all four different values, including the repeated categories once each.

Sort the unique list or return unique rows

UNIQUE preserves the source order, which can be helpful when order carries meaning. To display a sorted list, combine it with SORT. With the example values in A2:A8, enter =SORT(UNIQUE(A2:A8)). The result is Apple, Banana, Pear, and Plum in ascending order. The formula assumes a one-column source; SORT arranges the distinct output rather than changing the original data.

UNIQUE can also compare whole rows. With records across columns A through C and data in rows 2 through 10, enter =UNIQUE(A2:C10). Excel returns each distinct row once, considering the combination of values across the columns. For instance, two rows with the same person but different department values are different rows and both remain. This can be useful for creating a list of unique customer-and-product pairs.

The function’s optional by_col setting changes the direction of comparison. Most worksheet lists run down columns, so the default is appropriate. If the values to compare are arranged across columns instead, the by_col argument can be set to TRUE. For ordinary column data, avoid adding that argument unless you specifically need to compare columns as units.

Filter blanks and use the results elsewhere

If a source range includes empty cells, you may prefer not to include an empty item in the output. With values in A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")). This first keeps nonblank cells and then returns distinct values from them. For example, if A2:A6 contains Pear, a blank, Pear, Apple, and a blank, the result is Pear and Apple. The layout assumes one source column; make sure the range contains at least one nonblank value, because FILTER needs a result to return.

Because the result is a spill range, you can refer to it from another formula by adding the spill operator to the formula cell reference. For example, if the UNIQUE formula is in D2, another formula can refer to D2# to mean the whole spilled list. This is useful when building a dropdown or another calculation from a changing set of categories. Data Validation’s list source may not accept every spill reference directly in every setup, so a named range that refers to the spill range can be a practical workaround.

UNIQUE versus Remove Duplicates

Use UNIQUE when you want a separate, refreshable list and need to retain the source records. Use Remove Duplicates when you intend to change the selected data by deleting repeated records. In Excel for Microsoft 365 on Windows, select the data and choose Data > Remove Duplicates. If the first row contains headings, confirm that My data has headers is selected, then choose which columns Excel should use to identify duplicates.

Excel keeps the first occurrence and removes later rows that match the selected columns. If you select only the Email column in a table, records with repeated email addresses are treated as duplicates even if other fields differ. If you select every column, rows are removed only when all selected column values match. Review the selection carefully: removing duplicates changes the data rather than creating a separate result list.

For example, with order records in A2:C100, choosing all three columns removes only fully repeated records. Choosing just the order ID column can remove rows that share an ID even when their dates or amounts differ. Make a copy before a cleanup when the removed rows might matter. You can undo the operation immediately with Ctrl+Z on Windows or Command+Z on Mac, but undo is not a substitute for a backup if you continue working or save changes.

Choose the right method for your Excel version

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is also supported in current Excel for the web. In older perpetual versions such as Excel 2019 and Excel 2016, UNIQUE is not available, so an entered formula may return #NAME?. On Mac, the function is available in supported Microsoft 365 and newer Excel versions too; the formula syntax is the same. Dynamic-array behavior may differ in older installations that have not received the required updates.

If UNIQUE is unavailable, you can use Data > Advanced to filter unique records to another location, or use Data > Remove Duplicates if changing the source is acceptable. Advanced Filter is a useful built-in option for a one-time result, but unlike a dynamic-array formula, its copied list does not automatically update when the source changes. Power Query is another option for a repeatable cleanup: load the data, remove duplicates in the query, and refresh the query when the source changes.

For current Microsoft 365 users, UNIQUE is usually the simplest choice for an evolving list. For a one-time cleanup of a large imported file, Remove Duplicates or Power Query may be more suitable. The deciding factor is whether the original rows must remain intact and whether the result should update automatically.

Common problems and checks

#SPILL!: Check for content, merged cells, or other obstacles in the cells where the results need to appear. Move the formula or clear the output area.
#NAME?: Confirm the function is supported by your Excel version and that UNIQUE is spelled correctly. Older Excel releases do not include it.
Unexpected separate entries: Look for trailing spaces, nonprinting characters, or inconsistent data such as a number stored as text. Values that look alike may not be identical to Excel.
Missing expected items: Check the source range boundaries and whether a filter is excluding values. If using exactly_once, remember that every repeated item is deliberately omitted.
Results do not include new rows: A fixed reference such as A2:A100 does not include data entered below row 100. Increase the range or use an Excel Table column reference so added table rows are included.

Data cleanup often matters more than the formula. For example, “North” and “North ” may appear identical on screen but differ because the second value has a trailing space. Likewise, the text 0042 and the number 42 are not necessarily the same kind of value. Standardize the source before relying on a unique list for matching, counting, or reporting.

Frequently Asked Questions

How do I remove duplicates but keep one copy in Excel?
For a separate list, enter =UNIQUE(A2:A100) with the source values in A2:A100. To remove repeated rows from the selected data itself, use Data > Remove Duplicates and choose the columns that define a duplicate.

How do I get unique values in alphabetical order?
With the source list in A2:A100, enter =SORT(UNIQUE(A2:A100)). UNIQUE first returns one copy of each value, and SORT arranges that output in ascending order.

How do I find values that appear only once?
With values in A2:A100, enter =UNIQUE(A2:A100,,TRUE). This returns only values that occur once, not one copy of every repeated value.

Why does UNIQUE return #SPILL?
The formula needs empty cells to display its full result. Clear the cells in the spill area, unmerge obstructing cells, or move the formula where enough room is available.

Can I use UNIQUE in Excel 2019?
No. UNIQUE requires Microsoft 365 or Excel 2021 and later. In Excel 2019, use Advanced Filter for a separate one-time unique list, or Remove Duplicates to clean the selected data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.