Excel Filter Only Show Duplicates: A Practical Guide

Formulas & functions · Updated November 19, 2026

Excel does not have a built-in AutoFilter command that directly displays only duplicate values, but you can add a helper column, mark repeated values with a formula, and filter that column to TRUE. For values in A2:A100, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) in B2, fill it down, then use Data > Filter and select TRUE in column B.

Does Excel have a built-in duplicates filter?

Excel for Microsoft 365 on Windows has tools to highlight duplicates and remove them, but the standard Data > Filter menu does not include a direct Show duplicates option. A helper column is the clearest way to filter duplicates because it makes the test visible, lets you decide what counts as a duplicate, and leaves the source values untouched.

The formula in the opening example checks whether the value in each row appears more than once in the full range. It returns TRUE for every occurrence of a repeated, nonblank value, including its first appearance. For example, if column A contains Birch, Maple, Birch, Pine, Maple, the helper column marks both Birch rows and both Maple rows TRUE, while Pine is FALSE. Filtering the helper column to TRUE shows the repeated values and their original rows.

This approach works in an ordinary range or an Excel table. If your data is a table with headers, make sure the filter arrows are enabled; use Data > Filter if they are not. For a regular range, click inside the data and choose Data > Filter. The Windows shortcut Ctrl+Shift+L toggles AutoFilter. On Mac, use the Data tab or menu and choose Filter; keyboard shortcuts can vary with the keyboard and Excel version.

Set up the helper-column filter

  1. Keep a header row, and add a new heading such as Duplicate? in an unused column next to the data.
  2. With the values to check in A2:A100, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) in B2. Adjust the row numbers to cover the full data range.
  3. Copy or fill the formula down through the last data row. The dollar signs keep the checked range fixed as the formula is copied; A2 changes to A3, A4, and so on.
  4. Turn on filtering with Data > Filter. Open the dropdown on the Duplicate? header, clear Select All if needed, select TRUE, and confirm.
  5. To restore the complete list, clear the filter from that dropdown or use Data > Clear. The helper formulas and source data remain in place.

Filtering hides rows; it does not delete them. That matters when you later copy results, sort the data, or share the workbook. A visible TRUE result is a convenient flag, not a permanent classification: if you edit the source values, the formula recalculates and the filtered list can change.

Choose what counts as a duplicate

Before filtering, decide whether a duplicate means a repeated value in one column or a repeated record made from several columns. In a customer list, for instance, repeated last names may be legitimate, while repeated email addresses may indicate duplicate contacts. Checking the wrong column can produce a technically correct filter that answers the wrong question.

The COUNTIF test compares each cell against the values in the selected range. In normal Excel comparisons it is not case-sensitive, so Birch and BIRCH count as the same text. Leading or trailing spaces are different characters: Birch and Birch followed by a space may not match. Likewise, a date that appears identical on screen can have a different underlying time value, and numbers stored as text can behave differently from numeric values in some comparisons. If results look surprising, inspect and standardize the underlying data before treating the matches as errors.

The example ignores blanks deliberately. Without the AND check, blank cells can be counted against other blank cells and flagged as duplicates. That may be useful if empty entries themselves are what you are auditing, but it is usually noise when looking for repeated names, IDs, or products. If a blank is a meaningful part of a record, decide explicitly how it should affect your test.

Show only later occurrences, not the first one

Sometimes the goal is not to list every member of a duplicate group; it is to identify the extra entries that could be removed after review. Use a running count so the first appearance is treated as the original and only later appearances are flagged.

With values in A2:A100 and the helper formula entered in B2, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) and fill down. The first part of the range stays anchored at row 2, while its ending row grows with each copied formula. In the Birch, Maple, Birch, Pine, Maple example, the first Birch and first Maple return FALSE; the later Birch and Maple return TRUE. Filter TRUE to see only later occurrences.

This is a useful review list, not an instruction to delete rows automatically. The earliest row is not necessarily the best record to keep: it could have outdated information, missing fields, or an incorrect date. Compare the full records and confirm the appropriate version before removing anything.

Check duplicates across multiple columns

To find repeated records, compare all the fields that define a record. Suppose column A contains email addresses and column B contains department names. If a duplicate means the same email-and-department combination, checking column A alone would flag someone who appears in two departments. A two-column test avoids that false match.

With the first field in A2:A100 and the second field in B2:B100, enter =AND(A2<>"",B2<>"",COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1) in C2 and fill down. Filter column C to TRUE to show rows where that pair occurs more than once. COUNTIFS applies both conditions to the same row, so a match requires the email and department to match together.

This particular formula skips a row if either field is blank. If blanks are valid parts of your record definition, remove the blank checks and decide whether records with missing fields should match one another. For more columns, add another range-and-criteria pair to COUNTIFS, keeping every criteria range the same size. For example, a transaction might be defined by date, account, and amount; use all three only if that combination is genuinely what makes the record unique.

Other ways to identify duplicates

Conditional formatting is useful when you want to see duplicate cells without adding a helper column. Select the values, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel applies a visual format to repeated values in the selected range. This makes a quick scan easier, but it does not by itself isolate the matching rows in the way a TRUE/FALSE helper column does.

You may also be able to use the filter dropdown’s Filter by Color option after applying a duplicate highlight. That can be convenient for a small review, but it depends on the formatting and is less explicit than a formula column. If you need to repeat the process, audit the logic, or filter by a multi-column key, a helper column is generally easier to understand and maintain.

Data > Remove Duplicates serves a different purpose: it deletes duplicate rows from the selected data, keeping one occurrence according to the columns you choose. It is not a filter and does not simply hide matches. Make a copy of the worksheet or workbook first if you might need the original rows. After removal, Excel reports how many duplicate values were removed and how many unique values remain.

Common problems and checks

  • No filter arrows appear: Click within the dataset and choose Data > Filter. Check that the header row is included and that blank rows have not split the data into separate regions.
  • The formula flags every blank: Use the nonblank guard in the example, or change the logic deliberately if blanks should count.
  • Expected matches are missing: Check extra spaces, hidden characters, inconsistent number formats, and whether the compared range includes all rows. A fixed range ending at row 100 will not include new entries below row 100.
  • Only one field is being checked: Repeated names or product descriptions are not necessarily duplicate records. Use COUNTIFS for the full set of fields that defines a record.
  • The filtered list seems stale: Confirm calculation is enabled and the formula has been filled through every row. Also check whether new data was added outside the formula range and filter range.

For frequently updated lists, convert the range to an Excel table with Insert > Table or Ctrl+T on Windows. Tables expand as rows are added and carry formulas and filters into new rows, reducing the chance that a new record sits outside a manually fixed range. Review the formula and table references after conversion so the helper checks the intended data column and excludes its own helper column.

Frequently Asked Questions

Can I filter duplicates without a formula?
For a quick visual check, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then consider Filter by Color in the column dropdown. A formula helper is more dependable when you need to show matching rows, define duplicates across several columns, or explain the result to someone else.

Does Excel keep the first duplicate when I filter?
A filter only hides rows that do not meet its condition; it does not remove or prioritize records. The COUNTIF formula that checks the whole range flags every occurrence. Use the running-count formula if you want TRUE only on later appearances.

How do I find duplicate rows instead of duplicate cells?
Choose the columns that together define a record and compare them with COUNTIFS. A repeated value in one column can be legitimate, so include enough fields to distinguish records that should remain separate.

Is Excel’s duplicate check case-sensitive?
The COUNTIF and COUNTIFS comparisons used here are not case-sensitive in ordinary use. If capitalization should distinguish entries, this method alone will not enforce that rule; standardize the values or use a deliberately case-sensitive approach.

Does Remove Duplicates show duplicates in a filter?
No. Data > Remove Duplicates removes repeated entries from the selected data and keeps one instance; AutoFilter hides rows temporarily. Use a helper column when you want to inspect matches without deleting them.

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.