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 choose OK. Excel keeps the first matching row and removes later matches. Before you start, save a copy of your workbook: the command changes the selected data, and choosing the wrong columns can remove records you meant to keep.
Use this tool when you want to clean a list in place. If you need to keep the original list untouched, duplicate the worksheet first or use a method that returns a separate list. The instructions below explain how to choose the comparison columns and check the result.
What You’ll Need
- An Excel workbook with the list you want to clean. The steps apply to Microsoft Excel for Microsoft 365 on Windows.
- A clear idea of what counts as a duplicate: identical values in every column, or a match in only certain columns, such as email address.
- A backup copy of the workbook, or a duplicate of the worksheet, if you may need to restore the removed records.
Step-by-Step
- Check the shape of your data. Make sure each record is on one row and each type of information has its own column, such as Name, Email, and City. Remove any completely empty rows or columns inside the list if they make it hard to identify the data range. A single header row is fine; Excel can exclude it in the next steps.
- Save a safety copy. Choose File > Save As to make a separate copy, or right-click the worksheet tab and choose Move or Copy, then select Create a copy. This is especially useful if the data contains customer, inventory, or other records that would be difficult to recreate. You can also use Ctrl+Z immediately after removal to undo it, but a saved copy is safer if you make more changes afterward.
- Select the full list or table. Click a cell within the data. Excel usually detects the connected range automatically. To be certain, select the complete range, including all columns that belong to each record. If your data is formatted as an Excel table, clicking inside it is usually enough. Do not select just one column if you need to preserve the relationship between values in that column and the other fields on each row.
- Open the duplicate-removal tool. On the ribbon, choose Data > Remove Duplicates. If the ribbon is condensed, look on the Data tab for the Remove Duplicates command, which may be grouped with data tools. Excel opens a dialog showing the columns it will use to find matching records.
- Tell Excel whether the first row contains headings. If your first row has labels such as Name or Order ID, leave My data has headers checked. If the first row is actual data, clear that box. Getting this setting wrong can cause Excel to treat a record like a heading or include a heading in the comparison.
- Choose which columns define a duplicate. To remove only rows that match across the entire record, keep all relevant columns checked. For example, with Name, Email, and City selected, Excel removes a later row only when the values match across those selected columns. To keep only one row per email address, clear the other boxes and leave Email checked. This means two rows with the same email count as duplicates even if their names or cities differ.
- Review the choice before confirming. Check the column names and the header setting. Excel does not ask which of two matching records you prefer; it keeps the first occurrence in the current order and removes the later ones. If you want a particular record to remain, sort the data into the order you want before running the command, or make a copy and test the result there first.
- Choose OK and read Excel’s summary. Excel reports how many duplicate values were removed and how many unique values remain. Review the cleaned data, especially the rows immediately around where duplicates appeared. If the numbers or records do not look right, undo at once with Ctrl+Z on Windows and repeat the process with the correct range or comparison columns.
- Save the cleaned workbook. Once you have checked the output, save your changes. If you made a separate backup, keep it until you are confident that the remaining records are correct.
In Excel for Mac, the command is also on the Data tab; choose Remove Duplicates there and review the same column and header options. To undo on Mac, use Command+Z. Excel for the web also provides a Remove Duplicates command on the Data tab, though the ribbon layout can vary with the browser window size.
Common Mistakes
Comparing the wrong columns: If you leave only Email selected, Excel treats matching email addresses as duplicates even when other fields differ. That may be right for a mailing list, but wrong if each row represents a distinct transaction. Decide what makes a record unique before choosing checkboxes.
Selecting only one column from a multi-column list: If Excel asks whether to expand the selection, choose the option that expands it when the other fields belong to the same records. Continuing with only the selected column can affect that column without preserving the alignment of the neighboring information. For reliable row-based cleanup, select the complete dataset and set the comparison columns in the dialog.
Forgetting that the first match stays: Excel keeps the first occurrence in the current order, not necessarily the newest, most complete, or most accurate version. Sort first if the preferred record needs to appear before the others, and verify the sort is based on the right field.
Assuming similar-looking text is identical: A trailing space, a different spelling, or a different number stored as text can make values behave differently from what you expect. Conversely, capitalization alone does not reliably distinguish records in this tool; treat values that differ only by letter case as potential matches when planning your cleanup.
Running the command without a backup: Remove Duplicates deletes matching rows from the data. Although Undo can reverse the change immediately, it is not a substitute for a backup once you continue editing or close the workbook.
Tips
Use a stable identifier when possible. An order number, account ID, or email address may be a better definition of a duplicate than a person’s name. Names can be shared by different people, and one person may appear with small spelling variations. Check what the identifier means in your specific list before relying on it.
Keep all record columns selected when you mean exact duplicate rows. This is the safest starting point when the goal is to remove repeated copies of complete records. A pair of rows with the same name but different order dates, for instance, remains in the list if those dates differ and the date column is included in the comparison.
Make the desired version appear first. If duplicate IDs have different details, sort by a date or status field before removing duplicates so the preferred row comes first. Check that the sort includes the entire dataset; sorting one column by itself can scramble which names, dates, or amounts belong together.
Use a table for a list that will grow. Select the data and choose Insert > Table if it is not already a table, then confirm whether it has headers. Tables make a changing dataset easier to manage, but Remove Duplicates still removes records rather than continuously preventing new duplicates as you type. Run the command again when you need to clean later additions.
Check for spacing and data-entry inconsistencies first. For instance, alex@example.com and alex@example.com look almost the same but may not compare as the same text because the second value has a trailing space. If duplicates remain, inspect the underlying cell contents and standardize the values before repeating the removal.
Use a separate output when you need to preserve the source. Copy the list to another worksheet before removing duplicates, or consider Excel’s UNIQUE function in Microsoft 365 to return distinct values in a different area. That function is useful for a live, formula-based result, while Remove Duplicates edits the selected data directly.
Troubleshooting
Excel removed more rows than expected. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Reopen the dialog and select more comparison columns. For example, if you want to keep separate purchases made by the same person, include the purchase or order ID rather than comparing only the person’s name.
Excel did not remove rows that look identical. Confirm that the relevant columns were checked and that you selected the complete list. Then inspect the cells for leading or trailing spaces, hidden characters, inconsistent punctuation, or values stored in different formats. A date that looks the same on screen may contain a different underlying value or time. Correct the source data, then run Remove Duplicates again.
The header was included in the results. Run the command again and check My data has headers if the first row contains field names. If the first row is a real record, leave the option unchecked. If you already removed data incorrectly, undo first when possible, correct the setting, and retry.
The whole list is not showing in the dialog. Cancel the dialog, select the full range again, and check for blank rows or columns that split the data into separate blocks. If you are working with a table, click inside the table before opening the command. Verify that every field belonging to the records is included.
You cannot restore the removed records with Undo. If you have not closed the workbook, try Ctrl+Z on Windows or Command+Z on Mac. If Undo is no longer available, recover the saved backup, use the workbook’s version history if it is stored in OneDrive or SharePoint, or retrieve the original source file. This is why making a copy before cleanup matters.
The command is unavailable or difficult to find. Make sure you are in the worksheet containing the data and have a cell or range selected. On Windows, look under Data > Remove Duplicates; on Mac and Excel for the web, check the Data tab as well. If your ribbon is collapsed, expand it or widen the Excel window.
Frequently Asked Questions
Does Remove Duplicates delete the whole row?
When you work with a complete range or table, Excel removes the later record rows it identifies as duplicates, based on the columns you selected. Select the full dataset so related values stay together.
Can I remove duplicates based on just one column?
Yes. Select the complete dataset, open Data > Remove Duplicates, and leave only the identifying column checked. Excel then keeps the first row for each value in that column and removes later rows with a matching value.
Which duplicate does Excel keep?
It keeps the first matching row in the current order and removes the later matches. It does not automatically choose the newest or most complete row, so sort the full dataset first if a particular record should remain.
Does Remove Duplicates distinguish uppercase and lowercase?
Do not rely on capitalization alone to keep two otherwise matching entries separate. If letter case is meaningful in your data, review those entries separately or create a deliberate comparison method before deleting records.
Can I highlight duplicates without deleting them?
Yes. Select the values you want to inspect, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights potential duplicates so you can review them before using Remove Duplicates.
Will Excel remove duplicates automatically when I add new rows?
No. The command cleans the data at the time you run it; it does not continuously block or remove duplicates added later. Run it again when needed, or use a suitable data-validation or formula-based workflow if you need ongoing checks.
Related
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Insert a Zigzag Line in Word: Steps and FormattingTo insert a zigzag line in Microsoft Word, go to Insert > Shapes, choose Freeform: Shape in the…
- How to Copy an Excel File to WordTo copy Excel content into Word, select the cells in Excel, press Ctrl+C, switch to Word, and press…
- How to Track Changes in Microsoft Word and Review EditsTo track changes in Microsoft Word, open the document, select Review > Track Changes, then edit as usual.…
- Color Duplicates in Google Sheets: Conditional FormattingTo color duplicate values in Google Sheets, select the cells, open Format > Conditional formatting, choose Custom formula…
- How to Remove a Dropdown List from an Excel ColumnTo remove a dropdown list from an Excel column in Microsoft 365 for Windows, select the cells, choose…
- Remove Page Break Gridlines in Excel: Quick StepsTo remove page-break lines in Excel for Microsoft 365 on Windows, switch to View > Normal, then open…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.