Excel Filter for Duplicate Values: Find and Review Them

Formulas & functions · Updated November 19, 2026

To filter duplicate values in Excel for Microsoft 365 on Windows, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill it down, and filter that column for TRUE. This marks every occurrence of a value that appears more than once; it does not delete anything, so you can review the matching rows safely.

What an Excel duplicate filter does

Excel’s ordinary column filter does not have a built-in “show duplicates” command. The practical method is to calculate a TRUE or FALSE result beside each record, then use the column’s filter menu to display the TRUE rows. The original data stays in place, and you can clear the filter to see everything again.

For example, imagine names in A2:A8 are Rivera, Patel, Rivera, Chen, Patel, Kim, and a blank. Rivera and Patel are duplicates because each appears twice. Chen and Kim are not. A duplicate filter based on the name column shows both Rivera rows and both Patel rows, not just the later copy.

“Duplicate” depends on what you decide counts as the same record. It might mean a repeated email address, invoice number, product code, or combination of fields. Choose that key before filtering; otherwise, you may flag legitimate records or miss repeated ones.

Use a helper column to filter every duplicate occurrence

Suppose the values to check are in A2:A100, with a header in A1. Put a heading such as Duplicate? in B1. With the data in those cells, enter this formula in B2:

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

The result is TRUE for every value whose total count in A2:A100 exceeds one, and FALSE otherwise. In the example above, both Rivera rows and both Patel rows return TRUE. The dollar signs keep the checked range fixed when you copy the formula down; A2 changes to A3, A4, and so on.

  1. Enter the formula in B2 and fill it down through the last data row. Make sure every record has a result.
  2. Select a cell in the data, then choose Data > Filter. Excel adds dropdown arrows to the header row.
  3. Open the dropdown in Duplicate? and leave TRUE selected. Clear FALSE, then select OK.

You will now see all rows whose value occurs at least twice. On Windows, Ctrl+Shift+L toggles AutoFilter on or off. The ribbon route works without a shortcut and is also the clearest option if the shortcut behaves differently because of keyboard settings. On Mac, use Data > Filter; ribbon layout and shortcut behavior can vary by Excel version.

If blank cells are present, COUNTIF treats blank entries as a value that can occur more than once. To avoid flagging blank keys, use this formula instead. With the same A2:A100 layout, enter it in B2 and fill down:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

This returns FALSE for an empty A cell, even if many blank cells appear in the range. If blank is a meaningful category in your data, use the first formula instead.

Show only the extra occurrences

Sometimes the goal is not to review every member of a repeated group, but to identify rows that occur after the first instance. A running count can do that. With names in A2:A100 and a helper column beginning in B2, use:

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

Fill the formula down, then filter for TRUE. The expanding end of the COUNTIF range means each row is compared only with the entries from the first data row through the current row. The first Rivera therefore returns FALSE; the second Rivera returns TRUE. Patel behaves the same way. This identifies later repeats according to the current row order, so sorting the data first can change which occurrence is treated as the first one.

Use this version carefully if you plan to delete rows. A repeated value is not necessarily an erroneous record: two people can share a name, and a product can appear on multiple legitimate orders. Confirm that the chosen key should be unique before removing anything.

Check duplicates using more than one column

A value may be unique by itself but duplicated in combination with another field. For example, an invoice number might be reused by different suppliers, while the supplier-and-invoice pair should be unique. If suppliers are in A2:A100 and invoice numbers are in B2:B100, put a helper heading in C1 and enter this in C2:

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

Fill it down and filter column C for TRUE. COUNTIFS checks whether the current supplier and current invoice number occur together in more than one row. For instance, Northwind with invoice 104 could be flagged if that pair appears twice, while Contoso with invoice 104 would not be flagged unless its own pair also repeats.

For a composite key, decide which columns define sameness and include each one as a criteria range and criterion in COUNTIFS. Make sure the ranges are the same size and cover the same rows. If some fields may be blank, decide whether two records with blank fields should count as a match; blank criteria can match other blank cells.

Filter a table and keep the results easy to maintain

If your records are an Excel Table, its header filters make the review convenient, and a formula in a table column normally fills down as the table grows. To make a table, select a cell in the data and choose Insert > Table, confirming that the table has headers if appropriate. Add the helper formula in a new table column, then filter that column for TRUE.

A fixed range such as A2:A100 does not automatically include records added below row 100. When the list changes, extend the formula range and copy the formula to new rows, or use a Table so new records are included more naturally. Always check that the helper formula reaches the last record before relying on the filtered result.

Filtering hides nonmatching rows; it does not move them or change their values. To return to the full list, choose Data > Clear to clear filters, or use the relevant column dropdown and select all values. If multiple filters are active, clearing all filters is often simpler than adjusting only one dropdown.

Filtering versus highlighting and removing duplicates

These Excel tools serve different purposes. A helper formula plus AutoFilter is useful when you want to inspect full records, sort the matches, or keep the original rows unchanged. Conditional formatting is useful for a quick visual scan, but it does not itself filter the rows. Removing duplicates changes the data and keeps only one instance according to the columns you select.

  • Highlight repeated values: Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range; it does not create a filter or remove records.
  • Keep one copy and remove repeats: Select the data, then choose Data > Remove Duplicates. In the dialog, select the columns that define a duplicate. Excel removes repeated rows based on those selected columns, so save a copy or use Undo immediately if the outcome is not what you intended.
  • Show unique records: Choose Data > Advanced and use the unique-records option if you need a distinct list. Advanced Filter can isolate unique records; it is not a direct command for displaying only duplicates.

A common misconception is that “Remove Duplicates” is the way to filter duplicates. It is not: it deletes matching rows from the selected data range. If the purpose is investigation, use a helper formula or conditional formatting first.

Common reasons a duplicate is missed or falsely flagged

COUNTIF compares underlying cell values, and some details can make apparently identical entries behave unexpectedly. If the result looks wrong, check the following before changing the formula:

  • Extra spaces: “Rivera” and “Rivera ” may look alike but contain different text. TRIM can remove ordinary leading and trailing spaces, but cleaning should be done deliberately, preferably in a separate column so you retain the source values.
  • Numbers stored as text: A numeric-looking entry stored as text can behave differently from a true number in comparisons. Check cell formats and imported data, and standardize the key column if the values are intended to have one consistent type.
  • Case differences: COUNTIF is not case-sensitive. Entries such as ABC and abc are treated as the same for this test. If capitalization is meaningful, this formula alone does not provide a case-sensitive duplicate check.
  • Wrong comparison range: The formula must cover the complete set of records, not just the visible rows or a partial section. Hidden rows still count in the range.
  • Incomplete key: Checking a last name alone can flag different people who share that surname. Add a more appropriate field, or use a combination such as email address and account number.
  • Formula values and source data: A helper formula updates when its referenced values change. If the filtered result seems stale, check calculation settings and confirm that the source cells have actually been updated.

Sorting or filtering the source list does not change what COUNTIF means when its range is fixed, but it can affect the “later occurrence only” formula because that formula deliberately depends on row order. If row order matters, note the original order before sorting.

Use a dynamic formula when you want a separate results list

For a separate extract rather than an interactive filter on the source, Excel 2021 and Microsoft 365 include FILTER. With names in A2:A100 and complete records in A2:B100, enter this formula in an empty cell outside the source data:

=FILTER(A2:B100,COUNTIF(A2:A100,A2:A100)>1,"No duplicates")

The formula spills matching rows into adjacent cells, returning every record whose name appears more than once. If Rivera and Patel each occur twice, it returns both rows for each name; if no name is repeated, it displays No duplicates. Leave enough empty space below and to the right for the results to spill. The formula checks one key column, so replace the ranges with the appropriate columns for your data.

This produces a live results list rather than hiding rows in the original range. It is handy for reporting, but it is not a substitute for checking whether the key is appropriate or whether repeated records are valid. FILTER is available in Microsoft 365 and Excel 2021 or later; older Excel versions can use the helper-column method.

Frequently Asked Questions

Can Excel filter duplicates without deleting them?
Yes. Add a helper column using COUNTIF, fill the formula down, and filter the helper column for TRUE. Unlike Data > Remove Duplicates, this only hides nonmatching rows and leaves the original records intact.

How do I show only the second and later copies?
Use a running COUNTIF range such as =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) when the values start in A2. Filter the result for TRUE. The first occurrence is not marked, and later occurrences are.

Can I find duplicate rows based on two columns?
Yes. Use COUNTIFS with both columns as criteria, such as the supplier and invoice example above. The row is flagged only when that particular combination appears more than once.

Does Excel treat uppercase and lowercase as different?
No. COUNTIF is not case-sensitive, so capitalization alone does not make two text values distinct. Check and standardize the data if case differences matter to your task.

Why does my formula mark blank cells as duplicates?
The COUNTIF test can count blank cells too. Add the nonblank check A2<>"" inside AND, as shown in the blank-safe formula, if empty keys should not be considered duplicates.

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.