Yes. In Excel for Microsoft 365 on Windows, you can filter duplicate records by adding a helper column with a COUNTIF formula, then filtering that column for TRUE; you can also highlight duplicates, extract unique records, or permanently remove repeated entries. Choose the method that fits your goal, because filtering and highlighting do not delete data, while Remove Duplicates does.
What Excel Means by a Duplicate
A duplicate is a value or record that matches another one according to the columns you choose to compare. That definition matters: two rows may share a customer name but represent separate orders, while two rows that match in every field may be accidental copies. Before changing a list, decide whether “duplicate” means a repeated value in one column, a repeated combination of fields, or an identical whole row.
Excel’s built-in duplicate tools generally compare values without distinguishing uppercase and lowercase. For example, “North” and “north” are normally treated as matching for these tasks. Extra spaces can cause the opposite problem: “North” and “North ” may look alike but be treated as different text. If matching rules are important, clean or standardize the data first.
- One-column match: Find repeated email addresses in a customer list.
- Combination match: Find repeated invoice numbers for the same vendor.
- Whole-row match: Find records where every selected field is the same.
Filter Duplicate Values with a Helper Column
A helper column is the most flexible way to see only records that have a duplicate. It leaves the original rows in place and lets you use Excel’s ordinary filter controls. The example below checks whether each name in column A appears more than once in the list.
- Assume the list has headers in row 1 and names in A2:A7. In B1, enter a heading such as Duplicate?
- With names in A2:A7 and the formula entered in B2, use
=COUNTIF($A$2:$A$7,A2)>1. It returns TRUE for every name that occurs more than once and FALSE for a name that occurs once. - Fill the formula down through B7. For example, if A2:A7 contains Cedar, Maple, Cedar, Pine, Maple, and Birch, the Cedar and Maple rows return TRUE; Pine and Birch return FALSE.
- Select a cell in the data and choose Data > Filter. Use the dropdown in the Duplicate? column to show TRUE only.
This version marks every copy of a repeated value, including its first occurrence. If you want to keep the first instance visible and identify only later repeats, change the formula. With the same names in A2:A7 and the formula entered in B2, use =COUNTIF($A$2:A2,A2)>1 and fill it down. The expanding range counts from the first data row to the current row, so the first Cedar is FALSE and the second Cedar is TRUE. The first Maple is FALSE and the second Maple is TRUE.
To detect duplicate combinations, use COUNTIFS. With invoice numbers in A2:A7, vendor names in B2:B7, and the formula entered in C2, use =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1 and fill it down. This flags later occurrences of the same invoice-and-vendor pair, even if either value appears elsewhere with a different partner. Add more field pairs to COUNTIFS if your definition requires more columns.
Highlight Duplicates Without Filtering
For a quick visual check, conditional formatting can mark repeated cells without adding a formula column. Select the cells to examine, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format in the dialog and confirm. Excel highlights duplicate values within the selected range, which is useful for spotting repeats before deciding what to do with them.
Selection scope affects what the highlighting tells you. Selecting just the email column finds repeated email addresses. Selecting an entire multi-column table does not mean Excel compares each complete row as one combined record in the same way as Remove Duplicates; the rule highlights duplicate values in the selected cells. For matching combinations across fields, a COUNTIFS helper column is more precise.
You can sometimes use a filter by color to show cells with a conditional-formatting color, but that is a visual workaround rather than a clear duplicate test. A TRUE/FALSE helper column is easier to audit, sort, and reuse, especially when the workbook will be updated later.
Remove Duplicate Rows Permanently
When you have confirmed which records should go, Excel’s Remove Duplicates command can delete repeated entries. Make a copy of the worksheet or workbook first if the original data may be needed. Filtering rows out of view is reversible; deleting duplicate rows is a data change that may be difficult to reconstruct.
- Select a cell in the list or select the full data range, including its headers.
- Choose Data > Remove Duplicates.
- In the dialog, confirm whether the data has headers, then select the columns that define a duplicate.
- Choose OK. Excel reports how many duplicate values were removed and how many unique values remain.
If you select only Email, rows with the same email are considered duplicates even when other fields differ. Excel keeps one row from each matching group and removes the other rows; do not assume it will merge information from the deleted records. If you select all the columns, only rows matching across all selected columns are removed. For example, two orders for the same customer are not duplicates if their order numbers differ and that column is included.
The command changes the selected data in place. Sorting is not required first, and removing duplicates is not the same as filtering: removed rows no longer appear in the range. Excel’s interface may look slightly different on Mac, but the Remove Duplicates command is available from the Data tab there as well.
Show Unique Records Instead
Sometimes the aim is not to inspect duplicates but to produce a clean list of distinct entries while preserving the source. In Excel for Microsoft 365 on Windows, select the relevant range and choose Data > Advanced in the Sort & Filter group. In the Advanced Filter dialog, choose Copy to another location, set the List range and Copy to destination, and select Unique records only. Excel copies one instance of each unique record to the destination rather than deleting the source data.
For a simple single-column list, you can also use the UNIQUE function in Microsoft 365 or Excel 2021 and later. With names in A2:A7 and the formula entered in C2, use =UNIQUE(A2:A7). Excel spills a list of distinct names starting in C2. This returns unique values, not just values that occur exactly once: if Cedar appears twice in the source, Cedar appears once in the result. The source list remains intact, and the result updates when the referenced data changes, subject to the formula’s range.
These options answer a related but different question. To see which source rows repeat, use a helper formula and filter. To make a separate deduplicated list, use Advanced Filter or UNIQUE. To alter the original list, use Remove Duplicates only after confirming the comparison columns.
Choose the Right Method for the Job
- Use a helper formula and filter when you need to inspect duplicate rows, keep the originals, or identify later copies separately.
- Use conditional formatting for a fast visual scan of repeated values in a selected range.
- Use Remove Duplicates when you deliberately want to delete repeated records from the selected data.
- Use Advanced Filter or UNIQUE when you need a separate list of distinct values or records.
For a one-time cleanup, Remove Duplicates may be quickest, but its column selection deserves care. For a recurring report, a helper column or UNIQUE formula is often safer because the logic is visible and the original source can remain unchanged. If you share the workbook, label the helper column clearly so another reader knows whether TRUE means all copies or only later occurrences.
Common Problems and Checks
Headers get treated as data: Include the header row in the selected range and check the “My data has headers” option in Remove Duplicates or Advanced Filter. If the range starts below the headers, Excel cannot infer their role.
Similar-looking values do not match: Check for leading or trailing spaces, inconsistent punctuation, and different data types. A number stored as text may not behave like a numeric value in every workflow. Cleaning the source or building a normalized helper field can make the intended comparison explicit.
The wrong rows disappear: Reopen the duplicate dialog and review the checked columns. Removing duplicates based on a name alone can discard separate records that happen to share that name. If the data has meaningful identifiers, include them in the comparison or use a helper formula to review the matches before deletion.
Blank cells appear as duplicates: COUNTIF can count empty cells when the criterion points to an empty cell. To avoid flagging blank entries, use an extra condition. With names in A2:A100 and the formula entered in B2, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1), then fill down. This returns FALSE for blank cells and TRUE for nonblank names that occur more than once.
New rows are not included: A formula that refers only to A2:A100 will not inspect data added below row 100. Extend the range, or convert the list to an Excel table so it can expand as rows are added. Review the range before relying on a recurring duplicate check.
Frequently Asked Questions
Can Excel filter duplicates without deleting them?
Yes. Add a COUNTIF or COUNTIFS helper column, fill it down, and filter the helper column for TRUE. Conditional formatting can also highlight repeats without removing any rows.
How do I find only the second and later copies?
Use an expanding COUNTIF range such as =COUNTIF($A$2:A2,A2)>1 with the data in A2:A100 and the formula starting in the corresponding first helper row. The first occurrence returns FALSE; later occurrences return TRUE.
Can I find duplicates across two columns?
Yes. Use COUNTIFS to compare both fields, such as an invoice number and a vendor. In the formula, make the starting part of each range fixed and let its ending row expand as you fill down, so each row is compared with earlier rows.
Does Remove Duplicates keep the first or last copy?
Excel keeps one record from each duplicate group and removes the others. If it matters which record survives, arrange or review the data before running the command, and keep a backup. Do not rely on duplicate removal to combine different details across rows.
Is UNIQUE the same as filtering duplicate rows?
No. UNIQUE creates a list of distinct results; it does not mark the original rows that are repeated. Use a helper column to filter the source records, and use UNIQUE when you want a separate list without repeated values.
Related
- How to Write Subscript in Excel: Shortcut and Steps
- How to Write Superscript in Excel: Shortcut and Steps
- How to Wrap Text in Google Sheets Without Cutting It Off
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel PV Formula for Different PaymentsExcel’s PV function is designed for a fixed payment repeated over a set number of periods, so it…
- Excel Filter Based on Multiple Criteria: How It WorksTo filter Excel data by multiple criteria, use Data > Filter and set a condition in each relevant…
- Concat Excel Strings: Formulas and Practical ExamplesTo concatenate strings in Excel, join cell values with the ampersand operator (&) or use CONCAT; use TEXTJOIN…
- Excel FILTER Function: Find Cells That Contain TextYes. In Excel for Microsoft 365, FILTER can return rows where a cell contains particular text by combining…
- CONCATENATE in Excel: Formula, Examples, and TipsIn Excel, CONCATENATE joins text from multiple cells or typed values into one string. For example, with a…
- Show Formulas in Excel: Steps for Windows, Mac, and WebTo show formulas instead of calculated results in Excel, open the Formulas tab and select Show Formulas in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.