Excel Unique Values Without Duplicates: Methods That Work

Formulas & functions · Updated November 19, 2026

To show each value once in Excel, use the UNIQUE function: with a list in A2:A7 containing Apple, Pear, Apple, Plum, Pear, and Kiwi, enter =UNIQUE(A2:A7) in an empty cell; the result is Apple, Pear, Plum, and Kiwi. In Microsoft 365 or Excel 2021, the results spill into the cells below automatically. If you need to change the original list itself, use Excel’s Remove Duplicates command instead.

Get a distinct list with UNIQUE

UNIQUE is usually the quickest choice when you want a separate list that updates as the source data changes. It returns distinct entries from a range or array. The source data remains untouched, and Excel displays the results in neighboring cells as a dynamic array.

Enter the formula in a blank cell outside the source list, then press Enter. You do not need to select a block of cells in advance. If the source list grows, use a range that includes the new rows or put the data in an Excel table and refer to its column. A table expands as you add records, which can make a unique list more convenient to maintain.

The example in the opening paragraph produces four values, in the order they first appear: Apple, Pear, Plum, and Kiwi. That order is useful when you want to retain the sequence of first appearances. UNIQUE does not sort the result by default.

The function is available in Microsoft 365 and Excel 2021. It is also available in Excel for the web. Older desktop versions that do not support dynamic arrays may show a #NAME? error or fail to calculate the formula. If you need to share a workbook with people using older Excel, consider the built-in Remove Duplicates command or Advanced Filter instead.

Distinct values versus values that appear once

“Unique values” can mean two different things. Most often, people want a distinct list: keep one copy of each entry and discard its extra repetitions. That is what =UNIQUE(A2:A7) does. It keeps one Apple and one Pear even though each appears twice.

Sometimes the goal is to find values that occur exactly one time and exclude anything repeated. The third argument of UNIQUE handles that case. With Apple, Pear, Apple, Plum, Pear, and Kiwi in A2:A7, enter =UNIQUE(A2:A7,,TRUE) in an empty cell. The result is Plum and Kiwi, because each occurs once; Apple and Pear are excluded. The blank second argument accepts the default setting for comparing rows or columns.

This distinction matters in tasks such as finding products listed only once, spotting one-off survey answers, or identifying records that need review. It is not the same as removing extra copies while retaining one example. Before using the third argument, decide whether repeated items should remain in your output at all.

Sort the results or filter them by a condition

To alphabetize a distinct list, combine UNIQUE with SORT. With Apple, Pear, Apple, Plum, Pear, and Kiwi in A2:A7, enter =SORT(UNIQUE(A2:A7)) in an empty cell. The result is Apple, Kiwi, Pear, and Plum. This changes the order of the output, not the source data.

You can also return distinct values that meet a condition by nesting UNIQUE around FILTER. Suppose A2:A7 contains Pen, Notebook, Pen, Folder, Notebook, Marker, and B2:B7 contains Open, Closed, Open, Open, Closed, Open. To list each item with an Open status, enter =UNIQUE(FILTER(A2:A7,B2:B7="Open")) in an empty cell. The result is Pen, Folder, and Marker. The ranges used by FILTER must line up row by row so each item is tested against its own status.

For a list that may contain blank cells, filtering blanks out first can make the result cleaner. With A2:A6 containing a blank, Red, a blank, Blue, and Red, enter =UNIQUE(FILTER(A2:A6,A2:A6<>"","")) in an empty cell. The result is Red and Blue. The final empty-string argument tells FILTER what to return if no entries meet the condition; it helps avoid a #CALC! result for an empty match.

Return distinct rows, not just one column

UNIQUE can compare complete rows across multiple columns. This is useful when a record is defined by a combination, such as a customer and product pair. If you select only the customer column, Excel can tell you which customers appear, but it cannot distinguish the different customer-product combinations.

For example, suppose A2:B5 contains these rows: North, Tea; South, Coffee; North, Tea; North, Coffee. With the two-column data in A2:B5, enter =UNIQUE(A2:B5) in an empty cell. The returned rows are North–Tea, South–Coffee, and North–Coffee. The repeated North–Tea row appears only once, while the two different North records remain separate.

By default, the function compares rows when the source has multiple columns. Its optional second argument changes the direction of comparison: setting it to TRUE compares columns instead. That option is mainly useful when your data is arranged horizontally, with entries running across columns rather than down rows. In ordinary column-based lists, the default is the appropriate choice.

Remove duplicates from the original data

If you want to delete duplicate records rather than create a separate result, use Excel’s Remove Duplicates command. This changes the selected data, so save a copy first if the original list may be needed later.

  1. Select a cell in the data or select the range you want to clean. If the records span several columns, include all columns that belong together.
  2. On Windows, choose Data > Remove Duplicates.
  3. In the dialog box, confirm whether the data has headers, then select the columns Excel should use to identify duplicates.
  4. Choose OK. Excel keeps one record for each repeated combination in the selected columns and reports how many duplicates it removed.

Column selection determines what Excel considers a duplicate. If you select only the email column, two rows with the same email count as duplicates even if their names or other details differ. If you select every column, Excel removes only rows that match across all selected columns. Check this setting carefully, especially when a repeated name can legitimately have different associated information.

On Mac, the Remove Duplicates command is also on the Data tab, though the ribbon layout can vary slightly by Excel version. The command edits the selected range on both platforms. You can undo it immediately with Ctrl+Z on Windows or Command+Z on Mac, but keeping a backup is safer if you have continued working after the cleanup.

Use Advanced Filter or a manual check in older Excel

In versions without UNIQUE, Advanced Filter can create a separate list of distinct records without deleting the source. Select the data range, then choose Data > Sort & Filter > Advanced. In the dialog, choose Copy to another location, set the destination, and select Unique records only before confirming. Menu grouping or wording may vary slightly in older releases, but Advanced Filter is the built-in alternative to a dynamic unique-list formula.

Advanced Filter is useful when you need a one-time extract, but it does not update automatically when the source changes. Run it again to refresh the output. If you only need to find duplicate cells visually, Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values can highlight repetitions. Highlighting is not removal: it marks cells so you can inspect them, while leaving the data intact.

Copying the distinct output of a formula and pasting it as values is another option when you need a fixed list. Copy the spilled results, then use Paste Special to paste values into the destination. This breaks the link to the source, so later edits to the original data will not update the pasted list.

Common problems and ways to avoid them

  • #SPILL! appears: The formula’s output area is blocked by existing content, merged cells, or another obstruction. Clear the cells where the results need to appear, or move the formula to a larger empty area. The formula itself can remain in one cell.
  • Unexpected extra entries appear: Values that look alike may not be identical. Leading or trailing spaces, nonprinting characters, and numbers stored as text can make entries differ. Clean the source data when appropriate; TRIM can remove ordinary extra spaces, but it does not remove every kind of invisible character.
  • The list does not include new rows: A formula using a fixed range such as A2:A20 will not include data entered in A21. Expand the range or use an Excel table reference so the source can grow more easily.
  • The result is in the wrong order: UNIQUE preserves the order of first appearances. Wrap it in SORT if you need alphabetical or ascending order.
  • The formula works in one file but not another: A workbook opened in an older Excel version may not support dynamic arrays. Check the recipient’s version or replace the formula result with pasted values when a live formula is not required.

Excel’s ordinary duplicate handling is generally not case-sensitive, so do not depend on it to distinguish entries solely because one is uppercase and another is lowercase. Also, visual formatting such as font color does not define whether two cell values are duplicates. Decide what counts as the same record, and normalize the source consistently before removing entries.

Which method should you choose?

Choose the method based on whether the result should update and whether the source can be changed:

  • Use UNIQUE for a separate, automatically updating list in Microsoft 365 or Excel 2021. Add SORT if you want an ordered result, or FILTER if only certain records qualify.
  • Use Remove Duplicates when you want to clean the original range and keep one record from each duplicate group. Make a backup first and choose the comparison columns deliberately.
  • Use Advanced Filter for a separate, one-time list in an older Excel version or when you prefer a built-in dialog instead of a formula.
  • Use conditional formatting when you want to find and inspect duplicates without changing the data.

For a changing report or dashboard, a formula is usually easier to refresh. For a finished import that must be physically cleaned, Remove Duplicates is direct. If the data is important, keep the raw source on a separate worksheet and perform cleanup on a copy; that makes it easier to verify what was removed and repeat the process if the rules change.

Frequently Asked Questions

How do I remove duplicates but keep one copy in Excel?
Use =UNIQUE(A2:A20) in an empty cell to create a separate list with one copy of each value. To delete repeated records from the selected range itself, choose Data > Remove Duplicates.

Does UNIQUE remove duplicates from my original list?
No. It returns a separate spilled result and leaves the source cells unchanged. Use Remove Duplicates if the original data itself should be edited.

How do I list only values that appear exactly once?
Set the third argument of UNIQUE to TRUE. For a list in A2:A20, use =UNIQUE(A2:A20,,TRUE). This excludes values that occur more than once.

Why does UNIQUE show a spill error?
#SPILL! usually means Excel cannot place the results because one or more destination cells are occupied or merged. Clear the spill area or move the formula to an unobstructed range.

Can I remove duplicates based on only one column?
Yes. In the Remove Duplicates dialog, select only the column that defines a duplicate. Excel then keeps one row for each value in that column, so other details in discarded rows may be lost.

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.