Excel List Unique Entries with Formulas and Built-In Tools

Formulas & functions · Updated November 19, 2026

To list unique entries in Microsoft Excel for Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell, replacing the range with your data; Excel returns each different value once and spills the results into the cells below. If your Excel version does not support UNIQUE, use Data > Remove Duplicates to clean a copy of the list, or Advanced Filter to copy unique entries elsewhere.

What “unique entries” means in Excel

In everyday spreadsheet requests, “list unique entries” usually means make a list containing one instance of each different value. If a column contains Pear, Apple, Pear, Banana, Excel should return Pear, Apple, Banana. This is also commonly called a list of distinct values. The order from UNIQUE follows the first occurrence in the source range unless you sort the result.

There is a related meaning that is easy to confuse with distinct values: entries that occur exactly once. In the example above, Pear occurs twice, so it would not be included in a list of values appearing only once. Excel’s UNIQUE function can return either result, depending on its third argument. The distinction matters when you are identifying one-off transactions, names, or product codes rather than simply removing repeats.

Use the UNIQUE function

UNIQUE is the most direct option when you want a live result that updates as the source data changes. It is available in Microsoft 365 and Excel 2021 or later. Put the formula outside the source data, ideally on another worksheet or in a clear area, and leave enough empty cells for the answer to expand.

With the example entries Pear, Apple, Pear, Banana, Apple, Kiwi, and Banana in A2:A8, enter this in an empty cell:

With the example list in A2:A8, the formula returns Pear, Apple, Banana, and Kiwi, in that order:

=UNIQUE(A2:A8)

The result is a dynamic array: one formula produces a range of results, and Excel adjusts that range when the source changes. You normally enter the formula once in the top-left output cell, not copy it down. If a new value is added within the referenced range, the returned list recalculates automatically. If you expect the source list to grow beyond row 8, use a suitably larger range or an Excel Table column reference so additions are included.

Sort the unique list

UNIQUE preserves the order in which values first appear. That is useful when source order carries meaning, such as the sequence of first customer visits. For an alphabetical or numeric order, wrap UNIQUE in SORT.

With the same example data in A2:A8, this formula returns Apple, Banana, Kiwi, Pear:

=SORT(UNIQUE(A2:A8))

SORT and UNIQUE are both available in Microsoft 365 and Excel 2021 or later. Sorting is useful for a clean report, but remember that it changes the source order shown in the output. The original data remains unchanged. If you want reverse order, SORT has optional arguments for sort direction, but the default ascending order is sufficient for most basic lists.

Return values that occur exactly once

To show only values that appear once in the source, set UNIQUE’s third argument to TRUE. For example, with Pear, Apple, Pear, Banana, Apple, Kiwi, and Banana in A2:A8, the result is Kiwi because all the other values occur more than once.

With those entries in A2:A8, enter:

=UNIQUE(A2:A8,,TRUE)

The empty second argument leaves the by-column option at its default. The third argument asks for items that occur exactly once. This is not the same as removing duplicates: it excludes every repeated value altogether. Use the ordinary =UNIQUE(A2:A8) form when you want one copy of each value, even if it appeared many times.

List unique rows or combinations

UNIQUE can work on more than one column. If each row represents a record, it can return each distinct combination of values rather than deduplicating one column in isolation. This is useful for lists such as city-and-region pairs, department-and-role combinations, or product-and-size combinations.

With city names in A2:A100 and region names in B2:B100, enter the following in an empty area:

=UNIQUE(A2:B100)

Excel returns each different two-column row once. Thus, Northport with East and Northport with West count as separate records. If you select just column A instead, you get unique city names regardless of region. Choose the input columns to match what you mean by a duplicate: a repeated person name may be a duplicate by name alone, or only when both name and department match.

Remove duplicates from a source list

If you need to change the existing data rather than create a live output list, use Remove Duplicates. On Excel for Microsoft 365 for Windows, select the data and choose Data > Remove Duplicates. In the dialog, choose the columns Excel should compare, then select OK. Excel keeps the first occurrence of each matching row and removes subsequent duplicates from the selected range.

This operation edits the data in place. Make a copy of the worksheet or source range first if you may need the original order or repeated records later. The column selections determine what counts as a duplicate. For instance, selecting only the customer-name column can treat two records with the same name as duplicates even if their addresses differ; selecting name and address compares the full pair instead. Check the selection carefully before confirming.

Remove Duplicates is appropriate for a one-time cleanup, not for a result that should refresh whenever new records arrive. You can usually undo the operation immediately with Ctrl+Z on Windows, but a backup is safer, especially after further workbook edits. In Excel for Mac, the same feature is available from the Data tab; ribbon layout can differ slightly by release. Excel for the web also offers data cleanup features, though the exact controls may vary with the current web interface.

Copy unique entries with Advanced Filter

Advanced Filter is a built-in alternative when you want a static copy of unique records and do not want a dynamic formula. On Windows, select a cell in the data range, then choose Data > Sort & Filter > Advanced. In the dialog, choose Copy to another location, confirm the List range, specify a Copy to cell, and check Unique records only before selecting OK.

Include the column headers in the list range when your data has headers, and put the copied result somewhere that will not overwrite the source. Advanced Filter can copy distinct rows across several columns, so it is useful when a record is defined by a combination of fields. Unlike a UNIQUE formula, the copied list does not update automatically when the source changes; run the filter again to refresh it. On Mac, Advanced Filter is also on the Data tab, although ribbon grouping can differ by version.

Choose the method that fits the task

The right choice depends on whether you need a refreshed result, a permanent cleanup, or compatibility with an older workbook. A quick comparison:

  • UNIQUE: Best for a changing list and a result that recalculates. It requires Microsoft 365 or Excel 2021 and later.
  • Remove Duplicates: Best for permanently cleaning a copy of the original data. It edits the selected range.
  • Advanced Filter: Best for copying unique values or rows as a one-time snapshot, without replacing the source.
  • PivotTable: Useful when the distinct items are part of a summary or report. Place the field in Rows to display its labels, then refresh the PivotTable when the source data changes.

For recurring reports, UNIQUE is often easiest to maintain. For a one-off handoff where the output should not change, a copied list from Advanced Filter may be more suitable. If you are preparing a cleaned dataset for further analysis, Remove Duplicates can be efficient, provided you preserve a backup and define duplicates using the right columns.

Common problems and how to fix them

If Excel displays #SPILL!, the formula cannot place its full result in the cells it needs. Look for existing values, merged cells, or other obstructions in the output area. Clear the blocking cells or move the formula to a larger empty area. A dynamic array formula also cannot spill inside an Excel Table; place it outside the table, or convert the table to a normal range if appropriate.

If an entry you expect to be unique is missing, check whether Excel considers it the same as another value. Leading or trailing spaces can make text entries look alike while being different values. Inconsistent spelling, punctuation, and hidden characters can also split what a person considers one category into several results. Clean the source data before creating the list if those variations should be treated as the same entry.

Blank cells may appear in the output if the source range includes blanks. Limit the source range to populated data where practical, or remove blank rows before building the list. Also check whether a numeric-looking value is stored as text in some rows and as a number in others; inconsistent data types can affect matching and sorting. Excel’s duplicate handling is generally not case-sensitive, so entries such as “oak” and “Oak” are normally treated as the same text for this task.

Finally, make sure the formula points to the intended range and does not include a header unless you want the header returned as an item. A range that stops too early omits later entries; an excessively large range can bring in unwanted blank results. If colleagues using older Excel versions need the workbook, provide a static copied result or use a compatible method, since unsupported versions may show a formula error instead of the spilled list.

Frequently Asked Questions

How do I list unique values in Excel without deleting anything?
Enter =UNIQUE(A2:A100) in an empty cell, changing the range to fit your data. The formula returns a separate list and leaves the original values intact. It requires Microsoft 365 or Excel 2021 or later.

How do I list unique values in alphabetical order?
With your entries in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. SORT arranges the distinct results in ascending order while the source list stays unchanged.

How can I remove duplicates but keep one copy?
Select the data and choose Data > Remove Duplicates. Select the columns that define a repeated record and confirm. Excel keeps the first matching record and removes later matches from the selected data, so save a copy first if the original matters.

Why does UNIQUE return a spill error?
#SPILL! means the output cells are not available. Clear cells blocking the result, unmerge cells in the output area, or move the formula. The result also cannot spill from a formula entered inside an Excel Table.

Can I get values that appear only once?
Yes. If the list is in A2:A100, use =UNIQUE(A2:A100,,TRUE). That excludes values repeated in the source; it does not return one copy of each repeated value.

Related

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.