To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates; Excel keeps the first occurrence and removes later matching records. To filter duplicates without changing the source list, use a helper column with COUNTIF, or create a separate distinct list with UNIQUE.
What “filter out duplicates” can mean
People use “filter out duplicates” to describe several different tasks. You might want to delete repeated records, hide them temporarily, mark the extra copies for review, or produce a clean list while leaving the original untouched. The right method depends on which result you need.
There is also a key distinction between duplicates in one column and duplicate records across several columns. If a customer name appears twice but the two rows have different order numbers, those rows may be duplicates for a name-only list but not duplicate records. Excel can compare just the chosen column or compare multiple fields together, depending on how you set up the operation.
- Delete repeats: use Remove Duplicates when you want to change the existing data.
- Hide or identify repeats: add a helper column and filter its TRUE/FALSE results.
- Make a separate clean list: use UNIQUE or Advanced Filter, leaving the source intact.
Remove duplicates from a column or table
In Windows Excel, click a cell inside the data, then select Data > Remove Duplicates. In the dialog, check the column or columns that define a duplicate and select OK. Excel reports how many duplicate values or rows it removed and how many unique values remain.
- Make a copy of the worksheet or workbook if the original list matters. Remove Duplicates changes the selected data, and the operation can be difficult to reverse after further edits or saving.
- Click inside the list. If it has headers, confirm that My data has headers is selected so the header is not treated as a record.
- In the dialog, select the fields that should be compared. For a single-column list, check only that column. For whole-record duplicates, check every relevant field.
- Select OK, read Excel’s summary, and inspect the remaining rows.
For example, suppose columns A and B contain Customer and Order ID. If you select only Customer, Excel treats repeated customer names as duplicates and removes later matching rows, even if their order IDs differ. If you select both Customer and Order ID, only rows matching in both fields are duplicates. This choice determines what information is retained, so decide what “same record” means before running the command.
Remove Duplicates keeps the first occurrence in the current order and removes subsequent matches. If you need to retain the newest record, sort by date first so the desired row comes first, or create a review list before deleting anything. The command is not a way to merge records: it does not combine information from repeated rows.
Keep the original and mark repeated entries
A helper column is useful when you want to filter a list to show one occurrence of each value without deleting any data. Assume the values to check are in A2:A8, with a heading in A1. Enter this formula in B2 and fill it down through B8: =COUNTIF($A$2:A2,A2)=1.
The formula counts how many times the current value has appeared from the first data row through the current row. Its first occurrence returns TRUE; later occurrences return FALSE. With the example values Red, Blue, Red, Green, Blue, Green, Yellow in A2:A8, the results are TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE. Turn on filters with Data > Filter, open the dropdown in column B, and select TRUE to display the first occurrence of each value.
This does not remove or conceal data permanently: clearing the filter shows every row again. To flag the later copies instead, use a running count that tests whether the value has appeared more than once so far. Assuming the same A2:A8 layout, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill down. TRUE then marks the second and subsequent occurrences, which you can filter for review or removal.
COUNTIF is not case-sensitive, so entries such as “North” and “north” are treated as matches. A helper formula also follows the current row order: if you sort the list, which occurrence is marked as the first can change. If blanks are present, decide whether a blank should count as a value before filtering; a blank may be marked as the first occurrence of a blank.
Create a separate unique list with UNIQUE
In Microsoft 365, the UNIQUE function returns distinct values in a new area and leaves the source column unchanged. Assume the original values occupy A2:A8 and the destination cell is C2. Enter =UNIQUE(A2:A8) in C2. Excel spills the results into the cells below; for the Red, Blue, Red, Green, Blue, Green, Yellow example, the result is Red, Blue, Green, Yellow.
UNIQUE is available in Microsoft 365 and Excel 2021 or later. The results update when the source values change, provided the spill area is clear. If Excel displays a #SPILL! error, check whether cells below or beside the formula contain data, merged cells, or another obstruction. You can copy the resulting list and use Paste Special > Values if you need a fixed snapshot rather than a formula-driven result.
UNIQUE can also distinguish rows using more than one column. If the records are in A2:B8 and you want distinct Customer-and-Order-ID combinations, enter =UNIQUE(A2:B8) in an empty cell with room for the output. Each pair is returned once; two rows with the same customer but different order IDs remain separate. This makes UNIQUE a practical alternative to deleting records when you need a separate list of distinct records.
Use Advanced Filter to copy unique records
Advanced Filter is an option on desktop Excel when you want to copy unique entries to another location without writing a formula. On Windows, select a cell in the data and choose Data > Sort & Filter > Advanced. In the dialog, choose Copy to another location, confirm or enter the List range, set Copy to to an empty destination, and select Unique records only. Include the header row in the list range when the data has headers.
For a one-column list, set the list range to include its heading and values, then choose an empty destination cell. For a table with several columns, Advanced Filter copies unique rows based on the selected list range. It is a snapshot rather than a live result: if the source changes, run the filter again to refresh the copied list. Advanced Filter is not available in Excel for the web, so use UNIQUE there if your version supports it, or use Remove Duplicates on a copy of the data.
Choose the right method for the job
Use Remove Duplicates when the goal is to clean the existing list and you are confident about which fields define a duplicate. Use a helper column when you need to inspect, filter, or selectively delete repeated entries. Choose UNIQUE for a separate list that should update with the source, and Advanced Filter for a copied list that does not need to update automatically.
Think about the downstream use as well. A one-time mailing list may need just one copy of each email address, while a transaction report usually must preserve multiple purchases from the same customer. In the first case, deduplicating by email may be appropriate. In the second, removing repeated customer names could erase valid transactions. Keep the full records and choose comparison columns that reflect the actual meaning of a duplicate.
Common problems and checks before cleanup
- Unexpected rows disappear: check which columns were selected in Remove Duplicates. Selecting a name field alone can remove records that differ in other fields.
- Entries that look alike stay separate: inspect for leading or trailing spaces, inconsistent punctuation, and values stored differently, such as a number versus text. Clean or standardize the data first, ideally in a helper column, and verify the results before replacing the source.
- The wrong occurrence remains: Excel keeps the first matching row in the current order. Sort deliberately before removal if a particular row should be retained.
- The filter seems to hide data: a filter only hides rows from view; it does not delete them. Clear the filter to show the complete list.
- A dynamic list will not spill: clear the cells in the expected output area and check for merged cells or other obstructions.
It is also a misconception that duplicate removal automatically combines information. If two rows contain the same customer but different phone numbers, Excel does not decide which number is correct or merge the two. Review conflicting records and set a rule for retaining or reconciling them before cleaning the data. For important workbooks, save a separate copy and compare the row count before and after the change.
Frequently Asked Questions
Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first matching entry in the current order and removes later copies. Sort the records first if you need a specific row to appear first.
Can I filter duplicates without deleting them?
Yes. Add a helper column with the COUNTIF formula above, turn on Data > Filter, and filter the helper results. Clear the filter whenever you want to see all rows.
How do I remove duplicates based on one column but keep the whole row?
Select the full data range, open Data > Remove Duplicates, and check only the column that defines a duplicate. Excel removes the later matching rows from the selected data, so save a copy first and confirm that keeping the first row is appropriate.
Does Remove Duplicates update automatically?
No. It edits the current data once. UNIQUE creates a formula-based list that can update as source values change, while Advanced Filter creates a copied result that must be run again to refresh.
Why does Excel still show values that look duplicated?
They may differ because of spaces, punctuation, or how the values are stored. Compare the underlying values and standardize the column before removing duplicates; visually similar text is not always identical data.
Related
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
- Excel Shuffle List of Names: 10 Easy Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
- Excel Day Value: Serial Dates and the DAY FunctionAn Excel day value can mean either the serial number Excel uses to store a date or the…
- Excel: How to Filter a Table Based on a List of ValuesYes. In Excel for Microsoft 365, you can return only the rows in a table whose values appear…
- Google Translate Formula in Excel: Why It Fails and FixesIf a Google Translate formula is not working in Excel, the main issue is that Excel does not…
- Find Greater Than in Excel: Formulas, Filters, and RulesTo find values greater than a number in Excel, use the greater-than operator > in a formula, or…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.