Filter Duplicates in Excel: Remove or Show Unique Rows

Formulas & functions · Updated November 20, 2026

To remove duplicate rows in Excel for Microsoft 365 on Windows, select your data, then choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. To display unique records without deleting the source data, use Data > Sort & Filter > Advanced and select Unique records only, or use the UNIQUE function in a blank area.

What “filter duplicates” can mean

People use “filter duplicates” for several different tasks: deleting repeated records, showing only distinct records, or finding repeated values so they can review them. These actions are not interchangeable. Removing duplicates changes the source list; filtering or extracting unique values lets you keep the original data available.

It also matters what counts as a duplicate. In a list of customer names, two rows with the same name might be duplicates—or they might be two different people. In an order table, the same customer can appear many times legitimately. Before choosing a method, decide whether a duplicate means:

  • The same value in one column, such as a repeated email address.
  • An identical row across several columns, such as matching name, date, and order number.
  • A repeated key in selected columns, even if other details in the rows differ.

That definition determines which columns to select in Remove Duplicates, which range to use with a formula, and whether the result should replace the source data.

Remove duplicate rows with the ribbon

Remove Duplicates is the quickest built-in choice when you want to clean the original list. It keeps one occurrence of each record according to the columns you select and deletes later matching rows. Make a copy of important data first: although Ctrl+Z (Mac: Command+Z) can undo the operation immediately, later edits may make it difficult to return to the prior state.

  1. Click a cell in the list. If the data is not a formatted table, make sure the whole range is contiguous and has a header row.
  2. Choose Data > Remove Duplicates.
  3. In the dialog, confirm whether the list has headers. Select only the columns that define a duplicate.
  4. Click OK. Excel reports how many duplicate values or rows were removed and how many unique values remain.

For exact duplicate records, leave every column selected. Excel then treats two rows as duplicates only when their values match across all selected columns. For example, if columns A through C contain Customer, Email, and City, identical entries across all three columns are duplicates when all three boxes are checked.

If you select Email alone, Excel keeps the first row for each email address and removes later rows with that email, even if the customer name or city differs. That may be useful when email is the unique identifier, but it can also discard meaningful information. Sort the list first if you want a particular occurrence—such as the newest record—to be kept. The operation retains the first matching row in the current order.

Show unique records without changing the source

Advanced Filter can display or copy distinct records while leaving the original list intact. Click in the data, then choose Data > Sort & Filter > Advanced. In the dialog, verify the List range and select Unique records only. Choose Filter the list, in-place to hide repeated records in the list, or Copy to another location to create a separate result. Keep the header row included in the list range and use a destination that will not overwrite other data.

In-place filtering is reversible: clear the filter to see the full list again. Copying the results gives you a separate snapshot, not a live formula result; if the source changes, run Advanced Filter again to refresh that copy. Advanced Filter considers the selected columns together, so selecting the entire table returns unique rows, while selecting one column returns distinct values from that column.

Use UNIQUE for a live result

In Microsoft 365 or Excel 2021, the UNIQUE function produces a dynamic result that updates as the referenced data changes. With complete records in A2:C100 and headers in row 1, enter this formula in an empty cell outside the source table:

=UNIQUE(A2:C100)

Excel returns each distinct row across columns A, B, and C once. For example, if the first and fourth records match in all three columns, the output includes that record only once. To return distinct entries from a single column instead, with names in A2:A100, use =UNIQUE(A2:A100); repeated names appear only once in the result.

The output “spills” into the cells below and, for a multi-column range, to the right. Leave that area empty or Excel may show a #SPILL! error. This method does not remove or alter the original rows. UNIQUE is available in Microsoft 365 and Excel 2021; it is not available in older perpetual versions such as Excel 2019. On Mac and Excel for the web, the function is available in supported Microsoft 365 versions, though ribbon layouts may differ.

Find duplicates with conditional formatting

If the goal is to inspect repeated values rather than remove them, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Select the range first, choose Duplicate in the dialog, and choose a formatting style. Excel highlights values occurring more than once in that selected range.

This is a visual check, not a duplicate-row filter. For example, applying the rule to a customer-name column highlights repeated names even if the other cells in those rows are different. It will not establish that entire records are identical. You can filter a table by the resulting cell color using the column’s filter menu, but review the matching records before deleting anything. To remove the highlighting later, use Home > Conditional Formatting > Clear Rules and choose whether to clear rules from the selected cells or the whole sheet.

Use a helper column to filter repeated entries

A helper formula is useful when you want to keep the first occurrence and filter the remaining rows, or when you need a visible TRUE/FALSE test. Suppose names are in A2:A7, with Anna, Ben, Anna, Cara, Ben, and Dan in that order. Enter this in B2 and fill it down through B7:

=COUNTIF($A$2:A2,A2)>1

The first Anna and first Ben return FALSE; the second Anna and second Ben return TRUE. Turn on filters with Data > Filter, then filter column B to TRUE to show later repeats, or to FALSE to show only the first occurrence of each name. In this example, the TRUE rows are the second Anna and second Ben. The expanding range, from $A$2 to the current row, is what makes the formula count each value seen so far rather than flag every occurrence.

To identify duplicate combinations across columns, a COUNTIFS formula can test a composite key. With customer names in A2:A100, dates in B2:B100, and order IDs in C2:C100, enter this in D2 and fill down:

=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2)>1

TRUE means that the same combination of name, date, and order ID occurs more than once in the specified rows. Filter the helper column to review those records. Unlike the first-occurrence COUNTIF example, this formula flags every row belonging to a repeated combination, including its first appearance.

Choose the right method and avoid common mistakes

Use the method that matches the intended result, not simply the one that takes the fewest clicks:

  • Delete repeated records: Choose Remove Duplicates after confirming which columns define a match. Save a copy first when the data matters.
  • Temporarily view distinct rows: Use Advanced Filter with Unique records only and filter in place.
  • Create a separate, updating list: Use UNIQUE in Microsoft 365 or Excel 2021, with clear space for the result.
  • Review repeated values: Apply Duplicate Values conditional formatting, or use a helper formula when you need to filter or label matches.

A common mistake is selecting the wrong columns. If a timestamp or a changing note is included, otherwise similar records may not count as duplicates. If too few columns are selected, Excel may delete rows that differ in important ways. Another mistake is assuming that matching-looking values are always identical: leading or trailing spaces, text-versus-number differences, and inconsistent spellings can affect comparisons. Clean or standardize the data when needed, and check a sample of the results before treating the list as authoritative.

Also distinguish repeated values from repeated rows. A person’s name may appear many times in a transaction table because each row represents a separate purchase. Removing rows based on the name alone would collapse legitimate transactions. Use a genuine identifier or the full set of fields that makes a record unique.

Frequently Asked Questions

How do I remove duplicates in Excel but keep the first one?
Use Data > Remove Duplicates and select the columns that define a duplicate. Excel retains the first matching row in the current order and removes later matches. Sort first if a specific row should be retained.

Can I filter duplicates without deleting them?
Yes. Use Data > Sort & Filter > Advanced and select Unique records only to filter in place or copy distinct records elsewhere. A helper column can also label repeated rows so you can filter and inspect them.

How do I highlight duplicate values in Excel?
Select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range; it does not verify that whole rows match.

Why does Remove Duplicates keep rows that look the same?
The selected columns may include a field that differs, such as a date, space, or hidden detail. Check which columns are selected and inspect the underlying values. To define duplicates by fewer fields, select only those key columns—but confirm that differences in the other columns can safely be discarded.

Does UNIQUE update when the source changes?
Yes. In Microsoft 365 and Excel 2021, UNIQUE returns a dynamic array based on its referenced range. A copied result from Advanced Filter does not update automatically, and a formula will not include new rows outside its referenced range unless the range is adjusted or made appropriately dynamic.

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.