To filter duplicate records in Excel, add a helper column with a COUNTIF formula, then filter that column for TRUE; to delete duplicates instead, select the data and choose Data > Remove Duplicates. Highlighting, filtering, extracting, and deleting are different jobs, so choose the method that matches what you want to do before changing your data.
What “filter duplicates” can mean
Excel does not have one command that both identifies duplicate records and gives you every possible way to handle them. People often use “filter duplicates” to mean one of several things: show repeated values, keep only one copy of each record, remove extra copies, or create a separate list of repeated entries. These tasks call for different tools.
- See duplicates in place: use Conditional Formatting to highlight matching cell values.
- Show duplicate rows only: create a helper column and filter it.
- Make a separate duplicate list: use a dynamic array formula in Microsoft 365 or Excel 2021 and later.
- Delete repeated records: use Remove Duplicates, after preserving a copy of the original data.
A duplicate is defined by the columns you compare. If a customer name appears several times, those rows may be duplicates by name but not by order number. Decide whether a match means “same email,” “same ID,” or “same values across the entire row” before choosing a method.
Highlight duplicate values without changing the data
For a quick visual check, select the cells you want to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a formatting style and select OK. Excel highlights repeated values in the selected range; it does not remove or hide rows.
This is useful when you want to review a list before deciding what to do. But highlighting cells in a single column is not the same as identifying duplicate records across several columns. If you select an entire table, Excel evaluates duplicate cell values within the selected cells; a matching first name, for example, may be highlighted even when the full records differ. Use a helper column for a row-level check.
To stop or change the highlighting, select the range and use Home > Conditional Formatting > Clear Rules or manage the rule through Home > Conditional Formatting > Manage Rules. These formatting rules are non-destructive: they change how cells look, not the underlying values.
Filter rows using a helper column
A helper column is a reliable way to show only rows whose key value appears more than once. Suppose IDs are in A2:A100, with a header in A1. Enter a heading such as “Duplicate?” in B1. With IDs in A2:A100 and the first check in B2, enter =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) in B2, then fill the formula down to B100. TRUE marks an ID that occurs more than once; FALSE marks a unique ID or a blank cell.
- Select a cell in the data and choose Data > Filter to add filter arrows to the headers.
- Open the filter arrow in the helper column and select only TRUE.
- Review the visible rows. Clear the filter when you want to see the full list again.
The formula uses absolute references for the full ID range so that range stays fixed as you fill down, while A2 changes to the ID on each row. COUNTIF counts occurrences of the current ID, and AND prevents blank cells from being treated as duplicates. If your list grows beyond row 100, extend the formula range or use an Excel Table so new records are included more conveniently.
To mark only the extra occurrences after the first one, use a running count instead. With IDs in A2:A100 and the check in B2, enter =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) and fill down. The first instance returns FALSE, while later instances return TRUE. This is helpful when you want to keep the first row and inspect or remove only subsequent copies.
Check duplicates across multiple columns
When a duplicate means that two fields match together, compare both fields rather than just one. For example, a person’s name alone may not identify a duplicate, but the combination of name and email might. With names in A2:A100, email addresses in B2:B100, and the check in C2, enter =AND(COUNTA(A2:B2)>0,COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1) in C2 and fill down. TRUE means another row has the same name-and-email combination.
For a full-row comparison, include every column that defines a record in COUNTIFS, giving each criteria range and its matching current-row value. All criteria ranges must cover the same number of rows. Leave out columns that are expected to differ, such as a transaction timestamp, if they should not prevent otherwise identical records from being considered duplicates.
Be explicit about blanks. The example above ignores a completely empty row, but a partially filled row can still match another row with the same blank and nonblank fields. If that is not appropriate, add a rule that requires a particular key field to be filled in.
Extract duplicate rows into a separate list
If you want a separate, updating list instead of filtering the source table, use FILTER in a blank area. With IDs in A2:A100 and complete records in A2:C100, enter =FILTER(A2:C100,(A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)>1),"No duplicates") in an empty cell. The result includes every row whose nonblank ID occurs more than once, including the first occurrence. It does not change the source records.
FILTER and dynamic array formulas are available in Microsoft 365 and Excel 2021 or later. The returned results spill into adjacent cells, so keep the output area clear; otherwise Excel may show a #SPILL! error. Edits to the source range update the result. This formula checks duplicates by ID only; for a composite key, create a helper key or use a multi-column approach suited to your data.
For a list of distinct records, rather than a list of records that are repeated, use UNIQUE. With records in A2:C100, enter =UNIQUE(A2:C100) in a blank cell. It returns one copy of each distinct row. That is useful for creating a de-duplicated view, but it does not tell you which rows were duplicates or how many times each one appeared.
Remove duplicate records safely
To permanently remove extra copies from a range, first save a separate copy of the workbook or duplicate the worksheet. Then select a cell in the data and choose Data > Remove Duplicates. Confirm whether the data has headers, select the columns that define a duplicate, and choose OK. Excel keeps one occurrence of each matching record and removes the others from the selected data.
The selected columns determine the comparison. If you select only the email column, rows with the same email count as duplicates even if other fields differ. If you select every column, Excel removes only rows whose values match across all selected columns. Choosing fewer columns can therefore delete information you intended to keep. Check the selected column list carefully, and consider filtering or highlighting first when the correct definition is uncertain.
Remove Duplicates is a one-time edit, not a live filter. It changes the data in place, and ordinary Undo may be available immediately afterward, but it is safer to preserve a copy rather than rely on undo history. In Excel for Mac, the command is also available from the Data tab; Excel for the web may present ribbon controls differently. If you do not see it, check the Data tab or open the workbook in the desktop app.
Common causes of unexpected matches
Excel’s common duplicate tools and COUNTIF comparisons are generally case-insensitive, so values such as “AB12” and “ab12” may be treated as the same. Spaces and hidden characters can cause the opposite problem: two entries that look alike may not match because one contains a leading space or a nonprinting character. Check the source values before assuming the duplicate tool is wrong.
- Leading or trailing spaces: clean the source values or use TRIM in a separate cleanup column.
- Inconsistent formatting: a value stored as text may behave differently from a number in some comparisons or calculations.
- Partial matches: comparing only a name or city can flag different records as duplicates.
- Incomplete ranges: a formula ending at row 100 will not count records added below row 100.
- Filtered or hidden rows: filtering the display does not itself define duplicates; inspect the actual range and selected columns.
For a careful review, sort or filter by the key field, inspect representative matches, and verify that the proposed duplicate rule fits the data. If records differ in a meaningful field, they may be related entries rather than duplicates. For example, two rows for the same customer can represent separate purchases, so deleting one based only on the customer name would lose valid information.
Frequently Asked Questions
How do I filter only duplicate rows in Excel?
Use a helper column with a COUNTIF formula that returns TRUE when the key appears more than once, then turn on Data > Filter and show TRUE. For duplicates defined by several fields, use COUNTIFS with those fields.
Does Remove Duplicates keep the first row?
It keeps one occurrence and removes the other matching records. In a standard unsorted range, the retained occurrence is the first one encountered from the top. Sort the data first if you need a particular record retained.
Can I find duplicates without deleting them?
Yes. Conditional Formatting highlights matching values, a helper column lets you filter repeated rows, and FILTER can return matching rows in a separate area in supported Excel versions. None of those methods requires deleting the source data.
Why does Excel say there are no duplicates when entries look identical?
Check for extra spaces, hidden characters, inconsistent data types, or a comparison range that excludes some rows. Conversely, a case-insensitive comparison can treat entries with different capitalization as duplicates.
What is the difference between UNIQUE and Remove Duplicates?
UNIQUE creates a formula-driven list of distinct values or rows while leaving the source intact. Remove Duplicates edits the selected data and removes repeated records. Use UNIQUE for a refreshed view and Remove Duplicates when you deliberately want to change the source range.
Related
- Excel Task List Template: Set Up and Customize Yours
- Excel Template for a Budget Plan: Build Your Own
- Excel Meal Planning Template: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel CONCATENATE: Does It Keep Text Formatting?No. Excel’s CONCATENATE, CONCAT, and TEXTJOIN functions combine values into text, but they do not preserve rich-text styling…
- How to Add Single Quotes in an Excel Formula CorrectlyIf A2 contains North and you want the result to display as ‘North’, enter this formula in B2:…
- SUMIF in Excel: Meaning, Syntax, and How to Use ItIn Excel, SUMIF adds numbers only when their matching cells meet one condition; it is a conditional sum.…
- How to Divide in Excel: Formulas and ExamplesTo divide in Excel, enter a formula with a forward slash, such as =A2/B2, and press Enter. Replace…
- Excel: How to Substitute Multiple Text ValuesIn Excel, substitute multiple text strings by nesting SUBSTITUTE functions, one for each change. If A2 contains “The…
- Show Formulas in Excel on iPad: View Cell FormulasIn Excel on iPad, tap a cell to see its formula in the formula bar. To display formulas…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.