Remove Duplicates in Excel Filter: A Step-by-Step Guide

To remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Remove Duplicates, select the columns Excel should compare, and click OK. Excel keeps the first matching row and deletes later matches, so make a copy first if you may need the original data. If you want to display or copy unique records without deleting anything, use Advanced Filter instead.

What You’ll Need

An Excel workbook with a rectangular list of data: one header row followed by records, with no completely blank row or column splitting the list.
A clear idea of what counts as a duplicate. Decide whether every column must match or whether matching values in one or more particular columns are enough.
A backup copy of the workbook or data if you want the option to restore deleted rows. Excel’s Remove Duplicates action cannot be reversed after you close and reopen the workbook.

Step-by-Step

Review the data and decide what to compare. Confirm that the first row contains useful column headings, such as Customer ID, Name, and Email. Decide whether a duplicate means the entire row is repeated or whether matching one field, such as Customer ID, is sufficient. This choice determines which boxes you select later. For example, two records with the same name but different customer IDs may be different people, while two records with the same ID may represent the same customer even if another field differs.
Make a backup before deleting records. For a quick in-workbook copy, select the data, press Ctrl+C, click an empty worksheet, and press Ctrl+V. You can also use File > Save As to create a separate workbook copy. On a Mac, use Command+C and Command+V instead of Ctrl+C and Ctrl+V. Keeping a separate copy is safer than relying on Undo, particularly if you perform other actions or close the workbook.
Select the list to check. Click any cell inside the data. Excel will usually recognize the contiguous block of records automatically. If it does not, drag to select the complete range, including the header row and all columns that should stay together. You can also convert the list to a table first by selecting it and choosing Insert > Table; confirm whether the table has headers. Do not select only one column if you need to delete whole duplicate records across multiple columns.
Open the Remove Duplicates dialog. On the Windows ribbon, choose Data > Remove Duplicates. If Excel prompts you to expand the selection, choose Expand the selection when the records have related information in adjacent columns that must remain together. Choosing Continue with the current selection can remove repeated cells from only one column while leaving the other columns unchanged, which may scramble the relationships between fields.
Choose whether the first row is a header. In the dialog, select My data has headers if the first selected row contains labels rather than a record. If your list has no header row, clear that option. When Excel recognizes the headings correctly, the column checklist uses their names; otherwise it may display generic labels such as Column1. Confirm this setting before continuing, because treating a real record as a header can leave it out of the comparison.
Check the columns that define a duplicate. Select all columns to remove only rows that match across the entire record. Select just a unique identifier column, such as Order ID, to keep the first record for each ID and remove later records with that ID, even if other fields differ. You can select a combination of columns when the duplicate rule depends on several fields, such as the same email address and region. Clear any column that should not affect the comparison.
Run the removal and inspect the result. Click OK. Excel reports how many duplicate values were removed and how many unique values remain. Read the message, then scan the resulting list to confirm that the intended records remain and that the row count makes sense. The first occurrence in the selected range is kept; Excel does not automatically keep the newest date or the record with the most complete information. If a different record should survive, sort the data into the desired order before removing duplicates, or use a separate review process.
Use Advanced Filter when you want unique records without deleting the source. Select the list, including its headers, and choose Data > Advanced in the Sort & Filter group. In the dialog, choose Filter the list, in-place to hide repeated records in the displayed list, or Copy to another location to create a separate result. Set the List range and, if copying, the Copy to destination. Select Unique records only and click OK. Advanced Filter compares records using the columns in the list range: select only the relevant column if you want unique values from that field alone. You can clear an in-place filter to show the full list again.

Common Mistakes

Selecting the wrong comparison columns: Selecting every column means Excel removes only rows where all selected values match. It will not treat two rows with the same email address as duplicates if their other selected fields differ. Conversely, selecting only Email means Excel may remove a later row that has the same email but contains a corrected name or address. Match the selection to your real-world rule before clicking OK.

Removing duplicates from a single column by mistake: If your intention is to remove complete records, include the full table or choose Expand the selection when prompted. Working on only one column can leave the rest of the row in place and attach the wrong information to a remaining value.

Assuming Excel keeps the best or latest entry: The command keeps the first occurrence in the current order. It does not compare dates, completeness, or data quality to decide which row is preferable. Sort deliberately before removal if the first row should be the one retained, and verify the sort order before proceeding.

Expecting a filter dropdown to delete duplicate rows: The checkboxes in a column’s filter menu let you show or hide values. They do not permanently remove repeated records. Use Remove Duplicates to delete matching rows, or Advanced Filter to show or copy unique records.

Overlooking headers, blanks, or hidden rows: A wrong header setting changes which rows are considered records. Blank cells can also be legitimate values, not evidence that two records are duplicates. Clear filters and review hidden rows before deciding the scope, since the removal command works on the selected data rather than serving as a simple visible-row-only filter.

Tips

Standardize data before checking it. Excel may treat entries with extra spaces, inconsistent punctuation, or different spellings as different values. For example, Alex Lee and Alex Lee with a trailing space may not match. Clean or normalize the data first, while retaining a copy of the original columns for reference.
Use a stable identifier when possible. Customer numbers, invoice IDs, and email addresses are often more reliable than names, which can be shared or entered in different formats. If an identifier is not unique by itself, select the fields that together define a record.
Sort before removing if you need to choose which copy survives. If the newest entry should remain, sort the relevant date column newest to oldest, then use Remove Duplicates on the identifier. Check that the sort includes the complete table so values stay on their correct rows.
Consider an Excel table for ongoing lists. Tables make it easier to select a complete data set and keep headings visible. To create one, select the list and choose Insert > Table, then confirm My table has headers. Remove Duplicates can be used on table data, but the removal still changes the data, so keep a backup if needed.
Use Advanced Filter to preserve the original. Copying unique records to another location lets you compare the result with the source before deciding whether to replace anything. Make sure the destination is outside the source range and has room for the output.
Expect an exact comparison, not a fuzzy match. Remove Duplicates is not a tool for deciding that similar names, misspellings, or near-matching addresses refer to the same person. Review those cases separately rather than relying on the command to infer intent.

Troubleshooting

The Remove Duplicates command is unavailable or not visible: Click a cell in the worksheet data and open the Data tab. If the ribbon is simplified or the Excel window is narrow, some commands may be collapsed into a menu. Widen the window or look in the Data tab’s data tools area. If the sheet is protected, you may need permission to unprotect it before changing rows.

Excel says it found no duplicates: Recheck the selected columns and look for small differences such as extra spaces, inconsistent abbreviations, or text stored differently from numbers. A value that looks like 00123 may not match the number 123. Also verify that the whole intended list was selected and that Excel did not treat the first data row as a header. If you only mean to compare one field, select that field rather than all columns.

Too many rows were removed: Stop and use Ctrl+Z immediately on Windows, or Command+Z on Mac, if the removal is still the most recent action and the workbook remains open. If Undo is no longer available, restore the backup or reopen a saved copy. Then repeat the operation with a narrower, carefully checked set of comparison columns. Do not save over your only good copy until you have checked the result.

Advanced Filter returns unexpected results: Confirm that the List range includes the correct header row and all desired records. For Copy to another location, select a destination outside the list. If you want unique values from one field, set the list range to that field and its header; if you want distinct complete records, include all relevant columns. Ensure Unique records only is selected, then clear any existing filter if the displayed output seems incomplete.

The list looks unchanged after filtering: Advanced Filter with Filter the list, in-place hides repeated records rather than deleting them, and the change may be subtle in a short list. Check the visible row numbers and the filter state. To restore the full display, choose Data > Clear where available, or remove the active filter from the column dropdowns. If you need a separate, inspectable result, choose Copy to another location instead.

Frequently Asked Questions

Does Excel keep the first or last duplicate?

Remove Duplicates keeps the first occurrence in the selected range’s current order and removes later matches. It does not automatically keep the last entry. Sort the full data range first if you need a particular date or record to appear first.

Can I remove duplicates based on just one column?

Yes. In the Remove Duplicates dialog, clear the other column checkboxes and leave the target column selected. Excel then keeps the first row for each value in that column and removes later rows with the same value. Back up first, because information in the removed rows will go with them.

Does Remove Duplicates delete the entire row?

It removes duplicate records from the selected range according to the columns you choose. Include the complete set of related columns, or expand the selection if Excel asks. Selecting only one column when the rest of the row should stay connected can produce an incorrect result.

Can I filter duplicates without deleting them?

Yes. Choose Data > Advanced and use Filter the list, in-place with Unique records only to display unique records, or use Copy to another location to make a separate unique list. A normal column filter dropdown only shows or hides selected values; it is not the same as removing duplicates.

Why does Excel still show records that look identical?

The entries may differ in an unselected or selected field, contain extra spaces, use different data types, or have slightly different spelling. Review the comparison columns and inspect the underlying cell contents. Standardize the values before running the command if those differences should count as the same.

Can I undo Remove Duplicates?

Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation if it is still available. Undo may no longer help after other actions or after closing and reopening the workbook. A separate backup is the dependable way to preserve removed records.

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.