Excel Formula to Remove Duplicates From a List Automatically

Formulas & functions · Updated November 19, 2026

With values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell to return one copy of each distinct value. Excel for Microsoft 365 spills the results into the cells below automatically, leaving the source list unchanged.

What the UNIQUE formula does

UNIQUE creates a separate list containing distinct entries from a range or array. It keeps the first occurrence of each value and, by default, returns the results in the order they appeared in the source. For example, if A2:A7 contains Apple, Pear, Apple, Plum, Pear, and Kiwi, the formula returns Apple, Pear, Plum, and Kiwi in that order.

This is useful when a report needs a current list of customers, products, categories, or other values without editing the original data. If an item is added to or removed from the source range, the formula’s results update. The formula does not delete or alter duplicates in the source column; it generates a distinct output list elsewhere.

UNIQUE is available in Microsoft 365 and Excel 2021 or later. It also works in Excel for the web and current Excel for Mac versions that support dynamic arrays. If you have an older desktop version, use the compatibility option below or Excel’s Remove Duplicates command instead.

Enter the formula and make room for results

Choose a blank cell outside the source list, such as C2, and enter the formula. Press Enter; in a version with dynamic arrays, you do not need to select a block of cells or copy the formula down. Excel places the returned values in a spill range beneath the formula cell.

  1. Keep the source values together in one column, such as A2:A20, with a heading in A1 if you have one.
  2. Click an empty cell where the results can extend downward, for example C2.
  3. Enter =UNIQUE(A2:A20) and press Enter.
  4. Use the returned list in another formula, chart, or report, or copy it and paste values if you need a fixed snapshot.

Leave the cells beneath the formula clear. If anything blocks the required spill area, Excel displays #SPILL! rather than overwriting existing content. Clear the obstructing cells or move the formula to a larger empty area. A spill formula cannot be placed inside an Excel Table, although it can refer to table columns and spill into cells outside the table.

Remove blank entries as well as duplicates

A range can contain empty cells as well as repeated values. Depending on the source and how blanks are represented, a simple UNIQUE formula may return a blank-like result or a zero for empty cells. To exclude genuinely empty cells before extracting distinct values, filter the source first.

With values in A2:A20 and the result starting in C2, use =UNIQUE(FILTER(A2:A20,A2:A20<>"")). FILTER keeps entries that are not empty, and UNIQUE then removes repeats. The displayed <> is the HTML form of Excel’s not-equal operator, <>; enter the operator itself in Excel. If the source range is entirely blank, FILTER has no matches and can return #CALC!. For a range that might be all blank, use this guarded version instead: =IFERROR(UNIQUE(FILTER(A2:A20,A2:A20<>"")),"").

There is an important difference between a truly empty cell and a formula that returns an empty string. The condition above excludes both in common list-cleaning situations, because each compares as an empty string. It does not remove cells containing spaces. A cell that looks blank but contains one or more spaces is still text, so trim or clean the source if those entries should count as empty.

Return values that appear only once

“Remove duplicates” can mean either keep one copy of each value or keep only values that occur exactly once. UNIQUE uses the first meaning by default: a value appearing three times still appears once in the output. Its optional third argument switches to the second meaning.

With values in A2:A20 and the result in C2, use =UNIQUE(A2:A20,FALSE,TRUE) to return only entries that occur exactly once. The second argument, FALSE, tells Excel to compare down the rows; the third argument, TRUE, requests values that occur once and only once. If Apple appears twice and Pear appears once, this version returns Pear, not Apple.

This option is handy for finding one-off entries, such as a product listed in just one transaction, but it is not the usual choice for building a clean category list. If you want one representative copy of every category, leave the third argument out.

Choose between a distinct list and unique rows

For a single column, UNIQUE compares the values in that column. For a multi-column range, it compares complete rows: two rows are duplicates only when their corresponding values across the selected columns match. That distinction matters when a table has names alongside departments or dates.

With records in A2:C20 and the output in E2, enter =UNIQUE(A2:C20) to return each distinct combination of values across columns A, B, and C. A row with the same customer name but a different date remains a separate result. Conversely, if you want a distinct list of names only, refer to the name column alone rather than the entire table.

For a key column plus related information, decide what counts as a duplicate before choosing the range. If the rule is “one row for each customer,” using the full row will not combine that customer’s different orders. UNIQUE does not choose a preferred order record or summarize related values. Use a separate lookup, sort, or summary method if the task requires selecting the latest record or aggregating amounts.

Formula versus Excel’s Remove Duplicates command

A formula is usually better when the source must remain intact and the distinct list should refresh as data changes. It creates a live result, makes the selection rule visible, and can be combined with other formulas. Its main requirements are a compatible Excel version and enough empty space for the spill range.

For a one-time cleanup of the original data, use Excel’s built-in command. Select the data, then choose Data > Remove Duplicates on the Windows ribbon. Confirm which columns Excel should examine and whether the selection has headers, then run the command. Excel removes matching rows from the selected data and keeps the first occurrence based on the chosen columns. Make a copy first if you may need the original records.

The command is destructive in the practical sense that it changes the selected range, whereas UNIQUE leaves the source alone. Use the command for a finalized dataset that should actually be shortened; use a formula for a view, a recurring report, or a result that needs to respond to future edits. Excel for Mac has a Remove Duplicates option on the Data tab as well; ribbon labels and placement can vary slightly by release.

Options for older Excel versions

Excel 2019 and earlier versions do not support the dynamic-array UNIQUE function. A basic helper formula can mark the first appearance of each value, though it leaves blank cells where repeats occur rather than compacting the results into a continuous list.

With values in A2:A20, enter this in B2 and fill it down through B20: =IF(COUNTIF($A$2:A2,A2)=1,A2,""). For the example list Apple, Pear, Apple, Plum, Pear, Kiwi, the helper column shows Apple, Pear, a blank, Plum, a blank, and Kiwi. You can filter out blanks or copy the marked values into another location. COUNTIF is not case-sensitive, so this method treats text entries that differ only by capitalization as matches.

Another option for older versions is the Remove Duplicates command, which is available without a dynamic-array formula. The Advanced Filter feature can also copy unique records to another location. These are useful when compatibility matters, but unlike a UNIQUE formula they do not automatically recalculate a distinct output list after the source changes.

Common problems and how to fix them

  • #SPILL! appears: Check for text, formulas, merged cells, or other obstructions in the output area. Move or clear them, then allow enough space for the full result.
  • Values that look alike both appear: Check for leading or trailing spaces, nonbreaking spaces, or other hidden characters. Clean the source values before extracting distinct entries; visual similarity does not guarantee identical underlying text.
  • Capitalization seems ignored: Standard duplicate comparisons in Excel are generally case-insensitive. For example, “North” and “NORTH” are normally treated as the same text for this purpose. If capitalization carries meaning, use a case-sensitive method rather than assuming UNIQUE distinguishes the entries.
  • The formula returns an error: Confirm that the Excel version supports UNIQUE and that the formula uses the correct range. In some regional settings, Excel uses semicolons rather than commas as argument separators; the examples here use the standard comma syntax.
  • The result does not include new rows: A fixed reference such as A2:A20 will not automatically extend to values entered in A21. Expand the range or use an Excel Table column as the source so added table rows can be included.

Frequently Asked Questions

Does UNIQUE delete duplicates from the original list?
No. It returns a separate list with repeated values reduced to one occurrence. To change the original data, make a backup and use Data > Remove Duplicates.

How do I make the unique list update automatically?
Use UNIQUE with a source range that covers the data you expect to receive. For a growing dataset, format the source as an Excel Table and refer to its column, or leave adequate room in a range reference. The formula recalculates when its referenced data changes.

Can I sort the distinct results alphabetically?
Yes. With the source values in A2:A20 and the result in C2, use =SORT(UNIQUE(A2:A20)). SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later. This sorts the output rather than changing the source list.

Can I remove duplicates based on just one column?
Yes, but decide what should happen to the other columns in each matching row. UNIQUE applied to the key column returns distinct keys only; applied to several columns, it returns distinct combinations of those columns. It does not automatically keep a chosen corresponding record.

Why does the formula show zero for a blank?
Empty cells in the source range can produce a blank-like result that Excel displays as 0 in some situations. Filter out empty entries before applying UNIQUE, or use the guarded FILTER formula above if the range could contain no nonblank values.

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.