Excel does not have a dedicated “filter duplicates” command in a column’s filter menu. To show repeated records, add a helper column that identifies duplicates and filter it; to highlight them, use Conditional Formatting; to delete them, use Remove Duplicates. The right choice depends on whether you want to inspect, extract, or permanently remove repeated data.
What “filter duplicates” can mean in Excel
People use “filter duplicates” to describe several different tasks. Excel’s ordinary filter arrows let you show or hide rows according to values, but they do not automatically identify which values occur more than once. A useful way to separate the options is to ask what result you need:
- Find repeated values visually: highlight duplicate cells with Conditional Formatting.
- Show only rows with repeated values: calculate a duplicate flag in a helper column, then filter that column.
- Create a separate list of repeated rows: use a dynamic-array formula such as FILTER in a supported version.
- Keep one copy and delete the others: use Data > Remove Duplicates.
These options are not interchangeable. Highlighting does not hide rows, filtering does not delete data, and Remove Duplicates changes the source range. Save a copy before removing records if you may need to restore them.
Decide what counts as a duplicate
Before using a tool, define the key that makes a record a repeat. If a customer list has one row per person, you might compare email addresses. If an order table has one row per order line, you might compare both the order number and item code. Two rows can have the same name but represent different people, while a repeated email might be the exact issue you need to catch.
Excel’s Remove Duplicates dialog lets you select one or more columns. Selecting just Email treats matching email values as duplicates even if other columns differ. Selecting Name and Email compares the combination of those fields. For a full-row comparison, select every column in the range. Make this choice deliberately: choosing too few columns can remove legitimate records, while choosing too many may leave duplicates that differ in an irrelevant field such as a note or timestamp.
Highlight duplicates without changing the data
For a quick visual check, select the cells to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style in the dialog and select OK. Excel marks values that appear more than once in the selected range; it does not delete or rearrange rows.
To turn the highlights into a row filter, first make sure the data has headings, then apply Data > Filter to the range. Open the filter arrow for the highlighted column and, where the menu offers it, use Filter by Color to show the duplicate formatting color. This is convenient for a quick review, but a helper column is usually clearer for repeatable work: it makes the test explicit and can be copied, checked, or filtered consistently.
Conditional Formatting is also useful when you want to spot repeats while keeping the whole list visible. It is a display rule, not a permanent label; changing the data or the selected range can change which cells are highlighted.
Filter duplicate rows with a helper column
A helper column is a reliable method when you want the regular filter arrows to display only matching records. The example below treats repeated values in column A as duplicates. Put a heading such as Duplicate status in B1, enter the formula in B2, and fill it down through B8.
With the values to check in A2:A8 and the status formula entered in B2, use =IF(COUNTIF($A$2:$A$8,A2)>1,"Duplicate","Unique"). It labels every occurrence of a value that appears more than once. For example, if A2:A8 contains Ada, Ben, Ada, Cara, Ben, Ben, and Dev, the formula returns Duplicate for both Ada rows and all three Ben rows; Cara and Dev are labeled Unique.
Next, select the heading and data range, then choose Data > Filter. Open the arrow in the Duplicate status column and select only Duplicate. Excel now displays rows whose key occurs at least twice. Clear the filter to see the complete data again. If the range is an Excel table, its heading filters are normally available automatically, and the table can make added rows easier to manage.
Sometimes you want to flag only the second and later appearances, leaving the first occurrence unmarked. With the same values in A2:A8 and the formula in B2, enter =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence") and fill down. In the example, the second Ada and the second and third Ben are marked Duplicate; the first Ada and first Ben are labeled First occurrence. This is useful for reviewing which rows could be removed while preserving the first record.
Return matching rows with a formula
If you want a separate, automatically updating result instead of filtering the source list, FILTER can spill matching rows into empty cells. With records in A2:B8, keys in A, and the first occurrence of each repeated key included in the output, enter this formula in an unused cell: =FILTER(A2:B8,COUNTIF(A2:A8,A2:A8)>1,"No duplicates"). It returns each row whose key in column A occurs more than once, including all occurrences, or displays No duplicates if none qualify.
FILTER is available in Microsoft 365 and Excel 2021 or later. The results are formula output, not a separately stored copy: they update as the source data changes, and the cells below and beside the formula must be clear so the result can spill. If you need a fixed extract, copy the resulting cells and use Paste Values in a separate location. For an older Excel version without FILTER, use the helper-column method and copy the visible rows instead.
Remove duplicate records permanently
To keep one occurrence and remove repeats from the selected range, select the data and choose Data > Remove Duplicates. If the range has headings, confirm that My data has headers is selected. In the column list, check the fields that define a duplicate, then choose OK. Excel reports how many duplicate values were removed and how many unique values remain.
Excel retains one instance of each matching record and removes the additional ones from the selected data. It does not combine information from duplicate rows or decide which row is more complete. If two records share an email but have different phone numbers, for example, removing duplicates based on Email alone keeps one row and discards the other row’s remaining data. Review the selection and make a backup first when that information matters.
Remove Duplicates is a one-time change, unlike a filter or formula. Undo may reverse the operation immediately, but it is safer not to depend on undo as a recovery plan, especially after making other edits. The Remove Duplicates command is available in current Excel desktop versions; Excel for the web also provides data cleanup options, though exact ribbon layout can vary. On Mac, use the Data tab and look for Remove Duplicates; the dialog’s column-selection logic is the same.
Use Advanced Filter for a one-time extract
Excel’s Advanced Filter can copy records that meet criteria to another location, and it can also return unique records. On Windows, open Data > Advanced in the Sort & Filter group. Choose whether to filter the list in place or copy it to another location, specify the list and any criteria range, and use Unique records only when you want distinct records rather than repeats.
That last setting is a common source of confusion: Unique records only excludes duplicates; it does not extract the duplicate rows. To extract repeats with Advanced Filter, first create a helper column that labels them, then use that column as a criterion for the filter. For most everyday tasks, an ordinary filter on the helper column is simpler and easier to update.
Common problems and accurate results
A duplicate check is only as good as the values it compares. Extra spaces, inconsistent spelling, different punctuation, or a number stored as text can make entries that look similar behave differently. Conversely, two records can have the same key while other important fields differ. Inspect the underlying values and decide whether to standardize the data before treating matches as duplicates.
- Blank cells: A formula that counts a key across a fixed range can classify repeated blanks as duplicates. If blank keys should be ignored, add a separate blank check to the helper formula or remove incomplete records from the review first.
- Case differences: COUNTIF is not case-sensitive, so values such as “ab12” and “AB12” are generally treated as a match by that helper method. If capitalization is meaningful, use a case-sensitive approach rather than relying on COUNTIF.
- Changing data size: A formula referring only to A2:A100 will not include later rows outside that range. An Excel table or a suitably expanded range helps keep checks aligned with new records.
- Sorting and first occurrences: The running COUNTIF formula labels the first appearance based on the current row order. Sort the data first if a particular record should be retained as the first instance.
- Filtering the wrong field: A duplicate test based on an ID column finds repeated IDs, not necessarily identical full rows. Use the columns that match your definition of a duplicate.
Which method should you choose?
For a quick scan, use Conditional Formatting. For a filterable list that you can inspect and revisit, use a helper column. For a live extracted set in a modern Excel version, use FILTER. Use Remove Duplicates only when you have decided which columns define a repeat and are ready to alter the source data. Advanced Filter is worth considering for a one-time copy or a unique-record extract, but it is not the most direct way to identify repeats.
If you are cleaning important records, preserve the original data and check a sample of the flagged rows before deleting anything. A repeated customer ID may be an error, but repeated product names, dates, or transaction amounts can be perfectly valid. The spreadsheet can find matches; the meaning of those matches depends on the structure and purpose of your data.
Frequently Asked Questions
Does Excel have a button to filter duplicates?
Not as a dedicated option in the standard column filter menu. Add a helper column that labels duplicates, then filter that column to show only the matching rows.
How do I find duplicates in one Excel column?
Select the column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. To filter the matching rows, use a COUNTIF helper column and filter for its Duplicate label.
How do I show only unique values instead?
For a formula result in Microsoft 365 or Excel 2021 and later, use UNIQUE on the range. Alternatively, use Data > Advanced and select Unique records only, or use Remove Duplicates if you intend to change the original data.
Does Remove Duplicates delete every copy?
No. It keeps one instance of each record according to the columns you selected and removes the additional matching instances. It does not merge information from those rows.
Can I filter duplicates based on multiple columns?
Yes. For the built-in Remove Duplicates command, select all columns that together define a duplicate. For a helper formula, build a key that combines the relevant fields or use a formula that checks the full combination.
Will filtering duplicates delete any data?
No. A normal filter hides rows that do not meet its condition; clearing the filter shows them again. Deletion occurs only when you use a command such as Remove Duplicates or manually delete rows.
Related
- Excel Unmerge Shortcut: How to Unmerge Cells on Windows
- Excel Undo Shortcut: How to Reverse Changes Safely
- Excel Subscript Shortcut: Apply Subscript to Cell Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Protect Sheet: Let a Table Expand SafelyIn Excel for Microsoft 365 on Windows, there is no built-in Protect Sheet setting that reliably lets people…
- Excel XLOOKUP Between Two Dates: Formula and ExamplesTo use XLOOKUP for a date that falls between a start date and an end date, test both…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of…
- XLOOKUP in Excel: Find a Value and Return Matching DataTo find a value in Excel and return its matching result, use XLOOKUP: with product IDs in A2:A6,…
- Excel IFERROR vs ISERROR: Which Should You Use?Use IFERROR when you want to replace an error with a value such as a message or blank-looking…
- Excel Find and Substitute Formula: Examples and TipsTo find text and replace it in an Excel formula, use SUBSTITUTE: it replaces matching text in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.