How to Filter Duplicates in Excel

Formulas & functions · Updated November 20, 2026

To filter duplicate rows in Excel for Microsoft 365 on Windows, add a helper column with a formula such as =COUNTIF($A$2:$A$100,A2)>1, fill it down, turn on filters with Data > Filter, and filter the helper column to TRUE. Replace the range with your data; this flags every row whose value in column A appears more than once. For a quick visual check, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

“Filter duplicates” can mean finding repeated values, showing only the rows that repeat, or removing extra copies. Those are different tasks. The safest method depends on whether you need to inspect the records, temporarily hide other rows, create a clean list, or permanently change the source data.

Choose the right way to find duplicates

Before changing a worksheet, decide what counts as a duplicate. A repeated email address may be enough to identify a duplicate contact, while an order might count as a duplicate only when its date, customer, and item all match. Excel’s built-in tools and formulas can check one column or a combination of columns, but they do not know the meaning of your data. You have to define the fields that make two records equivalent.

  • To spot repeats quickly: highlight duplicate cells with Conditional Formatting.
  • To show only repeated rows: flag them with a helper formula, then filter that column.
  • To make a separate list: use Advanced Filter or, in supported versions, a dynamic-array formula.
  • To delete extra copies: use Remove Duplicates only after deciding which columns define a duplicate and which copy to keep.

Filtering hides rows; it does not delete them. This makes a helper column a good choice when you want to review results and then restore the full list.

Highlight duplicate values without hiding rows

For a fast visual scan, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, select a formatting style and choose OK. Excel highlights values that occur more than once within the selected range. If you select a single column, this is a convenient way to find repeated entries without adding a formula column.

This method is useful for review, but it is not itself a filter. You can filter by the highlight color if the range has filters enabled: choose Data > Filter, open the dropdown on the relevant column, and select Filter by Color. Conditional formatting changes how cells look, not the underlying values, and its rules can cover a larger or smaller range than you expect. Check the selection and rule range if unrelated cells are highlighted or some repeats are missed.

Conditional Formatting is also less informative when you need a complete row-level result. A highlighted name tells you the name repeats, but not necessarily which whole records should be treated as duplicates. Use a helper formula when you need an explicit TRUE/FALSE flag for each row.

Flag repeated values with a helper column

Assume the values to check are in A2:A100, with a header in A1. Put a label such as Duplicate? in B1, then enter this formula in B2 and copy it down through B100:

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

The formula returns TRUE for every row whose value occurs more than once in the checked range, including the first occurrence. For example, if A2:A5 contains North, South, North, West, the results are TRUE, FALSE, TRUE, FALSE. The dollar signs keep the checked range fixed as you copy the formula; A2 adjusts to the current row.

To show only the extra occurrences after the first, use a running count instead. With entries in A2:A100, enter this in B2 and fill down:

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

For the same North, South, North, West example, the formula returns FALSE, FALSE, TRUE, FALSE. This is useful when you plan to retain the first occurrence and review or remove later repeats. A blank cell can also be counted as a repeated value if the checked range contains multiple blanks, so consider excluding blank rows if they should not be treated as duplicates.

Once the helper column is filled, choose Data > Filter if filters are not already on. Open the dropdown in the helper column, clear Select All, select TRUE, and confirm. The visible rows now meet the duplicate rule. To restore the list, clear the filter or choose Data > Clear; the helper formulas remain in place until you delete them.

Filter rows that match across multiple columns

A repeated value in one field does not always mean the whole record is duplicated. Suppose names are in A2:A100 and dates of birth are in B2:B100. To flag rows where the name-and-date combination has appeared earlier, enter this formula in C2 and fill down:

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

This returns TRUE starting with the second occurrence of a matching pair. For example, two rows with Lee and 4/12/1990 are considered repeats of one another, but Lee with a different date is not. Filter column C to TRUE to see later duplicate combinations. If you want every member of a repeated pair flagged, including its first occurrence, use fixed ranges instead: =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. The formula should be placed in C2 and filled down; it compares each row’s pair against the full set.

For records with more fields, add each relevant field as another range-and-criteria pair in COUNTIFS. Keep the ranges the same size. Do not include columns such as a unique transaction ID if that ID differs on every row; doing so would make otherwise repeated records look unique.

Get a separate list of duplicate rows

If you want to preserve the source and display matching records elsewhere, Excel for Microsoft 365 can use FILTER. With records in A2:B100 and the field to test in A2:A100, enter this in an empty cell outside the source data:

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

The result spills into neighboring cells and shows all rows whose column A value occurs more than once. With North, South, North, West in A2:A5, and associated details in column B, it returns the two North rows. It does not change the original data. FILTER and dynamic arrays are available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not recognize them. Leave the spill area clear, or Excel may show a #SPILL! error.

Another built-in option is Advanced Filter. Select the list, including its header, and choose Data > Advanced in the Sort & Filter group. To copy a distinct list elsewhere, choose Copy to another location, specify the destination, and select Unique records only. This creates a unique result, not a list of duplicates, so it is useful when the aim is to keep one representative of each value. The Advanced Filter dialog’s exact options depend on whether you choose to filter the list in place or copy it.

Remove Duplicates: when filtering is not enough

To permanently remove repeated records, make a backup or copy of the data first. Select a cell in the list or select the range, then choose Data > Remove Duplicates. Confirm that the data has headers if it does, and select the columns Excel should compare. If you select only Email, rows with the same email are considered duplicates even if other fields differ. If you select every column, Excel removes rows only when all selected values match. Choose OK to run the operation.

Excel keeps the first instance it encounters in the selected range and removes later matching rows. Sort the data first if a particular copy should be retained—for example, sort by an update date so the preferred record appears first. Remove Duplicates changes the data rather than merely hiding rows, and the operation cannot be reversed after the workbook is closed. Immediately after running it, Ctrl+Z can usually undo the change; on Mac, use Command+Z. A saved backup is safer, especially for important lists.

Common problems and accuracy checks

A formula can be correct and still flag records differently from your expectations. Check these details before acting on the results:

  • Extra spaces: “Taylor” and “Taylor ” may appear identical on screen but differ as text. Clean inconsistent source data before comparing it; TRIM can help remove ordinary leading and trailing spaces.
  • Numbers stored as text: A numeric-looking value stored as text can behave differently from a true number in comparisons. Check cell formats and, when needed, convert the source values consistently.
  • Blank cells: Multiple blanks may be treated as repeats. Exclude empty rows from the checked range or adapt the rule if blanks should not count.
  • Case differences: COUNTIF and Remove Duplicates are generally not case-sensitive for ordinary text. If “abc” and “ABC” must be treated as different, a case-sensitive comparison needs a different formula or process.
  • Range boundaries: A formula that stops at row 100 will not include records added in row 101. Use a sufficiently large range or convert the data to an Excel table so added rows are easier to include.
  • Sorting and headers: Keep the header outside the formula range and confirm the header option in Remove Duplicates. Sorting can also make it easier to decide which repeated record to keep.

For a final check, compare the number of flagged rows with the visible filtered results and inspect several examples manually. If a value appears twice, the all-occurrences formula should flag two rows, while the running-count formula should flag one later occurrence. That distinction is a frequent source of confusion.

Frequently Asked Questions

How do I filter only duplicate rows in Excel?
Use a helper column with a COUNTIF or COUNTIFS formula that returns TRUE for duplicates. Turn on Data > Filter, then filter the helper column to TRUE. Choose a running-count formula if you want only second and later occurrences.

Does Excel’s Remove Duplicates delete the first copy?
No. It retains the first instance in the selected range and removes later matching records. If you need a particular copy to remain, sort the data so that record comes first before using the command.

Can I find duplicates based on two columns?
Yes. Use COUNTIFS with both columns as criteria, then filter the resulting helper column. Include only the fields that define a duplicate; a unique ID column will usually prevent any rows from matching.

Can I filter duplicates without deleting them?
Yes. Conditional Formatting highlights them, and a helper formula lets you filter duplicate rows while leaving the source records intact. Clear the filter to show all rows again.

Why does Excel not identify two entries that look the same?
They may contain extra spaces, different underlying values, or other invisible differences. Compare the cell contents, clean the data consistently, and check that the formula range includes both entries.

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.