Excel Filter Duplicates in a Column: A Practical Guide

Formulas & functions · Updated November 19, 2026

Yes. In Microsoft Excel for Microsoft 365, add a helper column with =COUNTIF($A$2:$A$100,A2)>1, fill it down, then turn on Data > Filter and filter the helper column to TRUE. This shows every row whose value in column A occurs more than once; the methods below also cover showing only later repeats or making a separate list of duplicated values.

What Excel means by duplicate values

A duplicate is a value that appears more than once in the range you are checking. For a column of product codes, for example, duplicate filtering identifies codes that occur repeatedly. You may want to display all rows containing those codes, only the second and later occurrences, or a compact list of each duplicated code once. These are different results, so decide which one you need before filtering.

Excel’s ordinary filter menu does not have a built-in “show duplicates” command. Its checklist lets you include or exclude values, but it does not calculate whether a value appears elsewhere in the column. A helper formula supplies that test, and the regular filter then displays the matching rows without deleting data.

For the examples here, assume the column heading is in A1 and the records are in A2:A8: North, South, North, East, South, South, and West. North and South are duplicated. A row-level filter will show every North and South entry, while a distinct duplicate list will show North and South just once each.

Filter every row with a duplicated value

Use this approach when you want to review or work with all records whose column value is repeated. Put a heading such as “Duplicate?” in B1, enter the formula in B2, and fill it down through the last data row. Keep the original data and helper column within the same table or contiguous range so the filter applies to entire records.

With the values in A2:A8 and the helper formula going in B2, enter =COUNTIF($A$2:$A$8,A2)>1. It returns TRUE for both North rows and all three South rows, and FALSE for East and West. The dollar signs keep the checked range fixed as you copy the formula down; A2 changes to A3, A4, and so on.

  1. Make sure the data has a header row, and place the helper heading in the next available column.
  2. Enter the formula in the first data row of the helper column. Use the actual first and last rows of your list instead of A2:A8.
  3. Copy the formula down. Drag the fill handle, or select the helper cells and press Ctrl+D on Windows. On Mac, use Command+D to fill down.
  4. Select a cell in the data range and choose Data > Filter. You can also toggle filters with Ctrl+Shift+L on Windows; on Mac, the shortcut is Command+Shift+F.
  5. Open the helper column’s filter arrow, clear Select All if needed, select TRUE, and confirm. Excel displays the matching records while hiding the other rows.

To return to the full list, open that filter arrow and choose Clear Filter From, or turn off filtering. The helper formula remains in place and recalculates if the values change.

Show only the second and later occurrences

Sometimes the first occurrence is the record you want to keep, and you only want to flag subsequent repeats. In that case, compare each cell with the portion of the column from the first data row through the current row. With source values in A2:A8 and the helper formula in B2, enter =COUNTIF($A$2:A2,A2)>1 and fill it down.

For the example list, this marks the second North and the second and third South entries as TRUE. The first North and first South are FALSE, as are the single occurrences of East and West. Filter the helper column to TRUE to see just those later occurrences. This is useful when checking a list before removing repeated entries, but it does not decide which record is correct; review dates, identifiers, or other relevant fields before taking action.

Create a separate list of duplicated values

If you need each repeated value only once rather than all matching rows, Excel for Microsoft 365 can produce a dynamic list with UNIQUE and FILTER. With source values in A2:A8, enter this formula in an empty cell outside the source data: =UNIQUE(FILTER(A2:A8,(A2:A8<>"")*(COUNTIF(A2:A8,A2:A8)>1),"No duplicates")).

The example returns North and South, each once, in the order of their first appearance. The condition excludes empty cells, and the final argument displays “No duplicates” if no values qualify. The formula spills its results into the cells below, so those cells need to be clear. UNIQUE, FILTER, and dynamic array behavior are available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not support this formula.

This is a calculated list, not a filter on the original records. Use the helper-column method when you need to inspect the corresponding rows or other columns, such as order dates and customer names. Use the dynamic formula when a concise, automatically updating list of duplicate values is the goal.

Filter complete records, not just the duplicate cells

When a duplicate value belongs to a row with other information, filter the full dataset. For example, if column A contains customer IDs and columns B through D contain names, dates, and order totals, select a cell in the contiguous table before choosing Data > Filter. Excel adds filter arrows to the headers; filtering the helper column hides or displays complete rows, keeping each ID with its related details.

For a growing list, convert the range to an Excel table with Insert > Table or Ctrl+T on Windows (Command+T on Mac). Confirm that the table has headers. Tables provide header filter arrows and normally extend formulas in a calculated column as new rows are added. Check that the helper formula is filled into new records, especially if the table’s calculated-column behavior has been changed.

Do not sort or filter only the helper cells while leaving adjacent records outside the selected range. That can separate a TRUE/FALSE result from the record it describes. Keeping the list contiguous, or using a table, helps Excel treat each record as a unit.

How this differs from highlighting and removing duplicates

Conditional formatting can make repeated values easier to spot, but it does not hide nonmatching rows. To highlight duplicates in a selected range, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is a visual check rather than a filter; it may be enough when you only need to scan the column.

Data > Remove Duplicates is different again: it deletes duplicate records from the selected range rather than filtering them for review. Make a copy or use Undo promptly if you choose that operation, and check which columns define a duplicate before confirming. Selecting just one column treats matching values in that column as duplicates; selecting multiple columns compares combinations across those columns. It is not a reversible substitute for a review filter once later workbook actions have occurred.

The Data > Advanced filter’s “Unique records only” option extracts unique records; it does not show only duplicated values. These commands are useful for different tasks: conditional formatting highlights, filtering hides rows temporarily, Remove Duplicates deletes records, and Advanced Filter can extract unique records.

Common issues and how to fix them

  • Blanks appear as duplicates: COUNTIF can treat blank cells as a matching value when blanks are included in the data. Exclude blank rows from the checked range, or add a blank test to the helper formula. With values in A2:A8 and the helper in B2, use =AND(A2<>"",COUNTIF($A$2:$A$8,A2)>1). Fill down, then filter TRUE.
  • Similar-looking values do not match: Extra spaces, nonbreaking spaces, punctuation, or a number stored as text can make entries different to Excel even when they look alike. Inspect the source values and clean or standardize them before counting. Trimming ordinary leading and trailing spaces can help, but imported text may contain other invisible characters.
  • Uppercase and lowercase are treated alike: COUNTIF is not case-sensitive, so North and NORTH count as the same value. If capitalization itself must distinguish records, a case-sensitive comparison needs a different formula approach.
  • The filter arrow is missing: Confirm that the active cell is inside the data range and that the first row is being used as headers. Check whether a worksheet or workbook restriction is preventing changes to filters.
  • New rows are not included: Expand the formula’s fixed range or convert the list to a table. A formula that checks only A2:A100 will not count records added below row 100.

Choose the method for the result you need

For most audits, a helper column plus the standard filter is the clearest choice: it is transparent, works with other fields in the row, and lets you inspect every instance. Use the running COUNTIF version when the first occurrence should remain unflagged. Use the UNIQUE/FILTER formula for a separate list of duplicated values, and conditional formatting for a quick visual scan.

Before removing anything, confirm the definition of “duplicate.” Two people can share a name but have different customer IDs; two orders can have the same product but different dates. A repeated value in one column does not necessarily mean an entire record is redundant. If a record should be considered a duplicate only when several fields match, compare those fields as a combined key or use a multi-column review rather than relying on one column alone.

Frequently Asked Questions

Can Excel filter duplicates without a formula?

Not with the standard filter menu alone. You can use Conditional Formatting to highlight duplicate values, but to filter all rows containing them, a helper formula such as COUNTIF is a straightforward option.

Does the duplicate filter delete any data?

No. Filtering hides rows that do not meet the condition; it does not remove them. The separate Remove Duplicates command deletes records, so use it only when you intend to change the data.

How do I show each duplicate value only once?

In Microsoft 365 or Excel 2021 and later, use UNIQUE with FILTER and COUNTIF to return a distinct list of values that occur more than once. Use a helper column instead if you need the full records behind those values.

Why are blank cells being marked as duplicates?

The formula may be counting multiple blank cells in its selected range. Exclude blanks from the range or use a helper formula that checks the current cell is not empty before counting it.

Can I filter duplicates across multiple columns?

Yes, but first define what makes a whole record duplicate. A repeated value in one column is not the same as repeated combinations across several columns. Create a helper test based on the relevant fields, or use Remove Duplicates only after selecting the columns that define a duplicate and reviewing a backup.

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.