Excel Unique Exclude Duplicates: 5 Ways

Formulas & functions · Updated November 22, 2026

To exclude duplicates and list only unique values in Excel, use the UNIQUE function: =UNIQUE(range). For example, =UNIQUE(A2:A20) returns each distinct item from that range once. This works in Excel 365 and Excel 2021; for older versions, use Advanced Filter or Remove Duplicates.

What “Unique” Means in Excel

Excel distinguishes between unique and distinct values. A unique value appears only once in the entire range. A distinct value is any value that appears at least once, regardless of repeats. The UNIQUE function returns distinct values by default. If you truly want only values that appear exactly once (no duplicates at all), use =UNIQUE(range,,TRUE). The third argument, TRUE, tells Excel to return items that occur only once.

Using the UNIQUE Function (Excel 365/2021)

The UNIQUE function is the simplest modern method. It spills results automatically into adjacent cells, so you don’t need to drag formulas. Syntax: =UNIQUE(array, [by_col], [exactly_once]).

  • Basic distinct list: With data in A2:A20, enter =UNIQUE(A2:A20) in C2. Result: each distinct value from A2:A20 appears once, in the order of first occurrence.
  • Exactly-once values: Enter =UNIQUE(A2:A20,,TRUE) in E2. Result: only values that appear exactly once in A2:A20.
  • Unique rows (multiple columns): With data in A2:C20, enter =UNIQUE(A2:C20) in E2. Result: unique rows based on all three columns combined.
  • By column: Use =UNIQUE(A2:C2, TRUE) to get unique values across a row.

The function spills down as far as needed. If you get a #SPILL! error, clear cells below the formula.

Advanced Filter for Older Excel Versions

For Excel 2019, 2016, or earlier, Advanced Filter is the reliable way. Steps:

  1. Select your data range (e.g., A1:A20, including header if any).
  2. Go to Data > Sort & Filter > Advanced.
  3. Choose Copy to another location.
  4. Set List range to your data.
  5. Leave Criteria range blank.
  6. Set Copy to to an empty cell (e.g., C1).
  7. Check Unique records only.
  8. Click OK.

This copies distinct values to the new location. It works on rows, so if you have multiple columns, it removes duplicate entire rows, not individual cell values.

Remove Duplicates Command

The Remove Duplicates tool physically deletes duplicate rows from your data. It’s destructive, so always copy your data first. Steps:

  1. Select the range or table.
  2. Go to Data > Data Tools > Remove Duplicates.
  3. Choose which columns to check for duplicates.
  4. Click OK. Excel shows how many duplicates were removed.

This is not a formula; it permanently alters your data. Use it when you want to clean a dataset, not when you need a dynamic list. Note: it keeps the first occurrence and removes later ones.

Pivot Table for Unique Counts

A Pivot Table can list distinct values and count them. To get a unique list:

  1. Select your data and insert a Pivot Table (Insert > PivotTable).
  2. Drag the field you want to de-duplicate into the Rows area.
  3. The row labels show each distinct value. To count occurrences, drag the same field into Values area (it defaults to Count).

This is useful when you also need counts. However, it doesn’t produce a formula-based list; it’s a static report unless you refresh.

Power Query (Get & Transform)

Power Query is built into Excel 365 and 2016+. It creates a query that can refresh. Steps:

  1. Select your data range.
  2. Go to Data > Get & Transform Data > From Table/Range.
  3. In the Power Query editor, select the column(s) you want.
  4. Right-click the column header and choose Remove Duplicates.
  5. Click Close & Load.

This loads a new worksheet with distinct values. The query refreshes when your source data changes (right-click the result and choose Refresh). It’s non-destructive and handles large datasets well.

Common Problems and Solutions

  • #SPILL! error: Something is in the cells below the formula. Clear them or move the formula to a column with empty space.
  • Case sensitivity: UNIQUE is case-insensitive by default. “Apple” and “apple” are treated as duplicates. To be case-sensitive, you’d need a helper column with EXACT or a complex array formula.
  • Blank cells: UNIQUE includes blanks as a distinct value (shows 0 or empty). To exclude blanks, wrap in FILTER: =UNIQUE(FILTER(A2:A20, A2:A20<>"")).
  • Trailing spaces: “Apple ” and “Apple” are different. Use TRIM first: =UNIQUE(TRIM(A2:A20)).
  • Old Excel versions: No UNIQUE function. Use Advanced Filter or a helper column with COUNTIF and filter.

Frequently Asked Questions

How do I count unique values in Excel? Use =COUNTA(UNIQUE(A2:A20)) to count distinct values. For exactly-once values, =COUNTA(UNIQUE(A2:A20,,TRUE)). In older versions, use =SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)), but this fails with blanks and text numbers.

Can I use UNIQUE with multiple columns? Yes. =UNIQUE(A2:B20) returns unique rows based on both columns. If you want unique values from a single column but the data spans two columns, you need to stack them first, e.g., =UNIQUE(VSTACK(A2:A20, B2:B20)) (Excel 365 only).

Does UNIQUE work on Excel for Mac? Yes, UNIQUE is available in Excel 365 for Mac and Excel 2021 for Mac. The syntax and behavior are identical. Advanced Filter is under Data > Advanced on Mac.

Why does Remove Duplicates leave gaps? It doesn’t leave gaps; it removes entire rows, shifting remaining rows up. If you see gaps, you likely used Advanced Filter with “Copy to another location” and didn’t select enough cells for the output.

Can I make the unique list update automatically? Yes, the UNIQUE function updates automatically when source data changes. Power Query requires a manual refresh (or set auto-refresh in query properties). Advanced Filter and Remove Duplicates are static.

Is there a shortcut to remove duplicates? No dedicated shortcut, but you can press Alt+A+M (Windows) to open Remove Duplicates after selecting data. On Mac, it’s Cmd+Option+M after selecting the range.

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.