To remove duplicates in Excel for Microsoft 365 on Windows, select your data, go to Data > Remove Duplicates, choose the columns that define a duplicate, and select OK. Excel keeps the first matching record and removes later matching rows, so make a copy of important data before you run the command.
What Excel means by a duplicate
A duplicate is defined by the column or combination of columns you choose in the Remove Duplicates dialog. If you select an entire contact list and mark only Email, Excel treats records with the same email address as duplicates even if their names or phone numbers differ. If you mark Name and Email, both values must match for a row to count as a duplicate. This distinction is important: choosing too few columns can remove records you meant to keep.
For example, consider two rows for Jordan Lee, one with an old phone number and one with a new phone number. If you define duplicates by name alone, Excel keeps the first Jordan Lee row and deletes the second. If you define them by name and phone number, both rows remain unless both fields match. Excel does not decide which record is newer or more complete; it applies the matching rule you give it.
When matching text, Excel’s duplicate-removal process is not case-sensitive, so capitalization alone does not make entries distinct. Formatting is not a reliable way to distinguish records either. Values that look alike can still differ because of leading or trailing spaces, hidden characters, or a number stored as text. If the result seems surprising, inspect and standardize the underlying data before deleting rows.
Remove duplicates step by step
Use the following process on a normal worksheet range or an Excel table. Selecting the full dataset helps ensure that Excel removes complete records, rather than leaving related information behind.
- Save the workbook or copy the source data to another sheet. This gives you a fallback if the wrong rows are removed.
- Click a cell in the dataset. If the data is not one continuous block, select the full range you want to clean, including all columns that belong to each record.
- Open Data > Remove Duplicates on the ribbon.
- In the dialog, confirm whether the first row contains column headings. Check My data has headers when the first row contains labels such as Customer ID or Email.
- Check only the columns that together define a duplicate. Use Select All if every column must match; clear columns that should not affect the comparison.
- Select OK. Excel reports how many duplicate values it removed and how many unique values remain. Review the remaining data before continuing with other edits.
The command changes the selected data in place. It does not create a separate cleaned list or keep a permanent record of the removed rows. If preserving the original is important, duplicate the worksheet first or copy the data to a new sheet and clean that copy.
Choose the columns that define a match
Think of the selected columns as the identity rule for a record. Select every field that must match for two rows to be considered the same, and leave out fields that are allowed to vary. The right choice depends on what one row represents and how the data will be used.
- One unique identifier: For a customer list, selecting Customer ID alone is often appropriate if each customer should appear once. Selecting a name alone can be risky because different people can share a name.
- A combination of fields: For sales records, Order ID plus Product ID may identify a line item. Selecting Order ID alone could incorrectly remove separate products from the same order.
- Every column: Select all columns when you want to remove only rows that are identical across the entire record. A difference in any selected field means the rows are retained.
- Fields that change over time: Leave out fields such as a last-updated date if a newer version of the same record should replace an older one. First sort the data so the version you want to keep appears first.
Excel keeps the first occurrence it encounters in the selected range. If you want to retain the most recent or most complete record, sort by the relevant date or quality measure before removing duplicates. Check the sort order and the selected range carefully: a mistaken sort or incomplete selection can produce a technically valid but unwanted result.
Check and prepare your data first
Duplicate removal is straightforward when the data is consistent. A short cleanup can prevent false non-matches, in which records that should be duplicates remain because their text differs slightly.
- Look for extra spaces at the start or end of names, addresses, and codes. A value with an invisible trailing space may not match the clean version.
- Check for inconsistent punctuation, abbreviations, and spelling. For example, St. and Street may refer to the same road but are different text entries.
- Confirm that identifiers use a consistent data type. A numeric-looking code stored as text may not behave like the same code stored as a number.
- Review blank cells in the columns you plan to compare. If several records have blanks in those fields, they may match on the selected columns even though other, unselected fields differ.
- Make sure the range includes all relevant records and fields. A title row, a total row, or a note included by mistake can affect the operation.
Do not remove spaces or normalize codes blindly. Leading zeroes can be meaningful in product codes, postal codes, and account identifiers. Decide which differences are accidental and which carry information before changing the source values.
Remove, highlight, or create a separate list
Remove Duplicates is best when you are ready to delete repeated records from the chosen range. If you want to inspect matches first, use highlighting instead: select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This marks repeated values visually without deleting rows. The rule checks the selected cells, so it is not automatically the same as checking whether a whole multi-column record is duplicated.
Another option is to create a separate list with the UNIQUE function, leaving the source untouched. With values in A2:A8, enter =UNIQUE(A2:A8) in an empty cell to return the distinct values from that range. For example, if A2:A8 contains North, South, North, East, South, West, and East, the result is North, South, East, and West, in first-seen order. UNIQUE is available in Microsoft 365 and Excel 2021 or later, and its results spill into cells below the formula. Leave enough empty space for the results to appear.
To return distinct complete rows rather than distinct entries from one column, with records in A2:C100 enter =UNIQUE(A2:C100) in an empty area. Excel compares each row across the selected columns; rows that differ in any of those columns remain. A formula-generated list updates when its source changes, while Remove Duplicates makes a one-time change to the selected data. If you need a fixed copy of a formula result, copy it and use Paste Special > Values in the destination.
Common problems and how to avoid them
Excel removed records that were not identical across the whole row. Check the column boxes in the dialog. If only one or two fields were checked, Excel treated those fields as the complete definition of a duplicate. Undo immediately with Ctrl+Z on Windows if the result is wrong, then run the command again with the intended columns.
Duplicates remain after the command. Inspect the cells for extra spaces, nonprinting characters, inconsistent spelling, and differences between text and numeric values. You can clean a copy of the data first, but avoid overwriting the original until you have checked how the cleanup affects identifiers and other important fields.
The wrong version of a record survived. Excel retains the first match, not necessarily the newest one. Undo, sort the full dataset so the desired version comes first, and repeat the operation using the correct duplicate-defining columns. Sort the complete rows together; sorting just one column can disconnect values from their records.
Related columns no longer line up. This can happen when only part of a dataset was selected or when a cleanup was performed on a single column rather than the full records. Undo if possible and repeat on the complete range. A backup sheet is especially useful when working with imported lists or business records.
Undo is useful immediately after an operation, but it is not a substitute for a backup. Later edits can make it harder to return to the exact earlier state, and a saved or closed workbook may not preserve a convenient undo history. Excel for Mac uses Command+Z to undo; the Windows shortcut is Ctrl+Z.
Excel for Mac and Excel for the web
In current desktop versions of Excel for Mac, the duplicate-removal command is available from the Data tab, and the essential choices are the same: indicate whether the range has headers and select the columns used to identify duplicates. The exact ribbon layout can differ slightly by version. Use Command+Z to undo on Mac rather than the Windows Ctrl+Z shortcut.
Excel for the web also provides data-cleanup tools, but the available ribbon layout and dialog can vary as Microsoft updates the service. If you cannot find the command in the web interface, open the workbook in the desktop app and use Data > Remove Duplicates. In any version, verify the selected range and comparison columns before confirming, because the key risk is the matching rule rather than the platform.
Frequently Asked Questions
Does Remove Duplicates delete the first occurrence? No. Excel keeps the first occurrence in the selected range and removes later rows that match the checked columns. Sort first if you need a particular version to appear first.
Can I remove duplicates based on just one column? Yes. Check only that column in the dialog. Excel then treats rows with the same value in that field as duplicates, even if other columns contain different information.
Can I remove duplicates without deleting my original data? Yes. Copy the data to another worksheet or workbook and run Remove Duplicates on the copy. You can also use UNIQUE in Microsoft 365 or Excel 2021 and later to create a separate, formula-driven list.
Why does Excel leave some apparent duplicates behind? The cells may contain different underlying text or values, such as extra spaces, inconsistent characters, or a number stored as text. Clean and compare a copy of the data, then repeat the operation.
Can I highlight duplicates before removing them? Yes. Select the cells and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Highlighting helps review repeated values, but it does not delete them and may not identify duplicate multi-column records as a single unit.
Related
- Excel Protect Sheet: How to Lock Cells and Set Permissions
- Excel Protect Sheet: Allow Editing in Selected Cells
- Excel Heat Maps: How to Create, Format, and Read Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DATEDIF Excel Function: Examples and Common IssuesExcel’s DATEDIF function calculates the time between two dates in years, months, or days. For example, with a…
- EDATE in Excel: Move Dates Forward by One Month EasilyTo move a date forward one calendar month in Excel, put the original date in A2 and enter…
- Due Date Calculation in Excel: Formulas and Examples GuideFor a due date a set number of calendar days after a start date, put the start date…
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- INDIRECT in Excel: How to Drag Formulas to New CellsIf you want an INDIRECT formula to follow different cells as you drag it, build its address from…
- Duplicate Filter in Excel: Find and Review Repeated ValuesTo filter duplicate values in Excel, add a helper column with a COUNTIF formula, then turn on the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.