To remove duplicates in Excel and keep the right record, sort your data first if the order determines which copy should stay. Select a cell in the range or table, choose Data > Remove Duplicates, select the columns that define a duplicate, and confirm. Excel keeps the first matching record it encounters. Sort the cleaned data afterward if you only need a particular display order. Make a copy of important data before changing it.
What You’ll Need
- An Excel workbook containing a list or table, with one record per row and consistent column headings.
- A decision about what counts as a duplicate: an identical value in one column, a matching combination of columns, or a completely identical row.
- A backup copy if the original records are important. Removing duplicates changes the selected data, and the removal cannot be reversed after you close the workbook.
The steps below are for Excel for Microsoft 365 on Windows. The core commands are also available in current Excel for Mac and Excel for the web, though ribbon placement and dialog details can vary slightly. The instructions use a contact list as an example, but the same approach works for inventory, orders, and other tabular data.
Step-by-Step
- Check the shape of your data. Make sure each row represents one complete record and each column has a heading, such as Name, Email, and Date Added. Remove completely blank rows within the list if they are not meant to be records. Keep related information together in the same row: sorting only a single column can separate values from their associated records. If the data is already an Excel table, click inside it and confirm that its headers are clear.
- Decide which copy should remain. Excel keeps the first record in the current order whenever the selected duplicate columns match. Suppose two rows have the same email address but different Date Added values. If you want to retain the earliest entry, sort Date Added from oldest to newest before removing duplicates. If you want the newest entry, sort newest to oldest instead. If you do not care which copy stays, you can remove duplicates first and sort the results afterward.
- Sort the complete records when order matters. Click a cell in the data and choose Data > Sort to open the Sort dialog. Choose the column to sort by, select the order, and click OK. For a simple alphabetical sort, you can use Data > Sort A to Z or Data > Sort Z to A. If Excel asks whether to expand the selection, choose Expand the selection so every row stays intact. In a table, sort using the heading’s filter arrow or the Data tab; do not select and sort just one column of a multi-column list.
- Select a cell in the list or table. You do not usually need to highlight every cell manually. Click any cell inside the contiguous data range, then open the Data tab and choose Data > Remove Duplicates. If the data is split into separate blocks by blank rows or columns, select the exact block you want to clean first. Check the selection carefully before continuing, especially if the worksheet contains other nearby lists.
- Confirm whether the first row contains headings. In the Remove Duplicates dialog, review the My data has headers checkbox. Leave it checked when the first row contains column names; clear it only when the first row is actual data. If Excel treats a heading as a record, or excludes a real first record, this setting is often the cause.
- Choose the columns that define a duplicate. The dialog lists the headings or column letters. Select only the fields that should match for two records to count as duplicates. For example, select Email alone to keep one row per email address. Select First Name and Last Name to keep one row for each matching name combination. Select every column if you want Excel to remove only rows whose values match across the entire record. The selection does not have to include every column in the table.
- Run the removal and review the result. Click OK. Excel reports how many duplicate values were found and removed, and how many unique values remain. Read that message rather than assuming the operation worked as intended. Check a few records, including any entries that had multiple copies, to verify that the kept row is the one you expected. If the result is wrong, immediately press Ctrl+Z on Windows to undo, then adjust the sort order or selected columns and try again.
- Sort the cleaned list if needed, then save. If you removed duplicates without sorting first, use Data > Sort or the table’s sort controls to arrange the remaining records. Choose Expand the selection if Excel prompts you. Save the workbook after checking the result, and keep the backup until you are confident the cleaned list is correct. In Excel for Mac, use the Data tab’s Remove Duplicates and Sort commands; in Excel for the web, look for the same commands on the Data tab, with some dialog layouts differing from desktop Excel.
Example: Imagine a table with Name, Email, and Date Added. Two rows share alex@example.com, one dated March 4 and one dated March 9. To keep the earlier record, sort Date Added oldest to newest, then remove duplicates using Email only. Excel retains the March 4 row and removes the later match. If you select all three columns in the duplicate dialog, Excel will keep both rows because their dates differ. This is why choosing the duplicate-defining columns matters as much as sorting.
Common Mistakes
Choosing too many columns. If you want one row per email address but select Name, Email, and Date Added, Excel compares all three fields together. A difference in the name or date means the rows are not duplicates by that definition, so both remain. Select only the field or combination that represents identity in your data.
Choosing too few columns. Removing duplicates based only on a common surname, product category, or city could discard valid records that belong to different people or items. Decide what makes a record unique before you run the command. If a customer is identified by both account number and region, select both columns rather than guessing from one field.
Sorting just one column. Sorting one column independently can mismatch names, dates, and other details across a row. Sort the full range, use the table’s sort controls, or choose Expand the selection when prompted. A correct sort preserves each record as a unit.
Expecting Excel to choose the best record automatically. Remove Duplicates does not compare which row is more complete or more recent. It retains the first match in the current order. Sort by the field that expresses your preference before removal, and check ties or missing dates if the preferred row is not obvious.
Overlooking formatting differences or extra spaces. Cells can look identical while containing different text, such as a trailing space after a name. Conversely, formatting differences such as font color do not make otherwise matching cell values unique. Clean inconsistent text before removal if those differences should not create separate records.
Tips
Convert a regularly updated list into an Excel table with Insert > Table, or press Ctrl+T on Windows and confirm that the table has headers. Tables make it easier to identify headings and sort complete records. On Mac, use the Insert tab’s Table command or the available table shortcut for your keyboard layout. Creating a table does not itself remove duplicates, but it can make later review and sorting more manageable.
Use a helper column when the duplicate rule needs a combined identity that is not represented by one existing field. For example, you might decide that a record is identified by both department and employee number. Select those two columns in the Remove Duplicates dialog, or create a clearly labeled helper field that combines them and remove duplicates based on that field. Make sure the combination cannot confuse distinct values; adding a separator between fields helps distinguish them.
For large or important lists, preserve the original data on a separate worksheet before editing. You can copy the sheet, duplicate the table, or save a separate workbook version. This gives you a way to compare the cleaned list with the original and recover details that were removed. Keep a note of the rule you used, such as one row per email address, so another person can understand the result.
Consider highlighting possible duplicates before removing anything if you need to inspect them. Select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values in the selected range; it does not remove rows or decide which record to keep. It is a useful review step, but it may not represent a multi-column duplicate rule by itself.
Remember that duplicate checking is based on cell values, not just what the worksheet appears to show. Two dates displayed in the same format may represent different underlying dates or times, while text with an invisible space can compare differently from clean text. If you are uncertain, inspect the cell contents and standardize the data before deletion. Do not treat a highlighted match as proof that the entire records are duplicates.
Troubleshooting
Excel removes too many rows. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. Then check the selected columns: a broad rule such as matching on last name alone may group unrelated people. Choose a more specific field or combination, such as account number plus region, and run the operation again. If the workbook has already been closed, restore the saved backup or use an available version history rather than trying to reconstruct deleted rows from memory.
Excel removes no rows, but you can see repeats. Review the columns selected in the dialog and confirm that their values really match. Extra spaces, different punctuation, inconsistent abbreviations, or dates with different time values may prevent a match. Check the source cells and standardize only the differences that should be ignored. Also verify that the repeated values are within the selected range, not in a separate block outside it.
The wrong copy remains. Undo, sort the full dataset so the preferred record comes first, and repeat Remove Duplicates using the correct columns. Check the sort direction and the values in the sort field. If multiple records tie on that field, add a second sort level in Data > Sort to set a clear priority, such as completeness or another date column.
Rows no longer line up after sorting. Undo the sort right away. Select a cell inside the full list and use Data > Sort, or choose Expand the selection if prompted. If the data contains blank rows that split it into separate regions, select the intended range explicitly before sorting. Verify that each row’s values still belong together before removing duplicates.
The dialog shows letters instead of useful headings. The header setting may be unchecked, or the range may not include its heading row. Cancel the dialog, confirm that the selected range starts at the correct row, and reopen Remove Duplicates. Check My data has headers when the first row contains labels. If the headings themselves are blank or repeated, give them clear, distinct names first.
Frequently Asked Questions
Does Remove Duplicates sort the data automatically?
No. It removes repeated records according to the selected columns, but it does not arrange the remaining rows into alphabetical or date order. Sort before removal if you need to control which copy stays; sort afterward if you only want to change the final display order.
Which duplicate does Excel keep?
Excel keeps the first matching record in the current order and removes later matches according to the columns you selected. If the first copy is not the one you want, sort by a priority field before using Remove Duplicates.
Can I remove duplicates based on one column and keep the other information?
Yes. Select only the column that defines a duplicate in the dialog. Excel removes the later matching rows from the selected range or table, including their other cell values, while retaining the first complete row.
Can I remove only rows that are identical in every column?
Yes. Select every column in the Remove Duplicates dialog. Excel then removes a row only when the values match across all selected columns. Differences in any selected field mean the rows are treated as distinct.
Can I undo Remove Duplicates?
Usually, yes, with Ctrl+Z on Windows or Command+Z on Mac, as long as the action is still in the undo history. If you have closed the workbook or the undo history is no longer available, restore a backup or a prior workbook version. Saving a separate copy first is the safer option.
Does removing duplicates change the original source file?
It changes the data in the workbook you are editing, not a separate source file unless that source is the workbook itself. If you imported or copied the list from another file, the separate source is normally unchanged. Save the cleaned workbook under a new name if you need to preserve the original workbook too.
Related
- Excel Payment Calendar: Build One That Updates
- Excel Payment Log: How to Track Payments and Balances
- Excel Payment Request: Create, Track, and Send One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Excel Grading: Match Scores to Letter GradesTo assign a letter grade from a numeric score in Excel, put the grade cutoffs in ascending order…
- Basic IF Examples in Excel: How to Write the FormulaIn Excel, an IF formula checks whether a condition is true and returns one value if it is,…
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
- Excel Table Query: How to Filter, Transform, and Refresh DataIn Excel for Microsoft 365 on Windows, the usual way to query an Excel table is to select…
- SUMPRODUCT in Excel: Multiple Criteria ExplainedYes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example,…
- Sort Excel by Column for the Entire WorksheetTo sort a column without separating its rows, click a cell in your data, choose Data > Sort,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.