Delete duplicate rows based on specified columns in Excel
This is a feature that can be used in Microsoft 365, the desktop version of Excel 2024/2021/2019/2016, and Excel for the web. Decide on a match column and try it on the copy to keep the first matching row and delete subsequent rows.
Published · Updated · FaultNote editorial policy
Who this guide is for and what to prepare
- People who want to organize duplicate lines in rosters and order lists
- Someone who can decide which columns should match to be considered duplicates.
What you need
- Copy the original sheet and prepare a table containing name, email, and department as an example.
- Sort by update date, etc. so that the row you want to keep is at the top.
Identify duplicate candidates before deleting them
Select a data cell in the email column you want to check, excluding the header. Choose [Home] > [Conditional Formatting] > [Highlight Cells Rules] > [Duplicate Values]. For example, if two rows contain [email protected], both cells are highlighted. This only marks duplicates; it does not delete them.
- Select data cells in email column excluding headings
- Color duplicate values
- Check if the line to leave is the first
Specify and delete columns to match
Select the entire table and open Data > Remove Duplicates. If the first row is a heading, turn on the corresponding item, then [Deselect All], check only "Mail", and press [OK]. If the selected columns are the same, the entire row will be deleted, including the unselected department columns.
- Select entire table
- Open Remove Duplicates from Data
- Check only the matching column and press OK
Check the number of items and remaining rows
Record a result such as "1 duplicate value was found and removed, leaving 4 unique values." Verify in the filter that the coloring has disappeared and the intended first row of [email protected] remains. Whitespace or extra whitespace characters may produce different results.
- Check the number of result dialogs
- Filter by matching value
- Check if the necessary information in other columns remains
Bring back lines that were accidentally deleted
Use [Undo] or Ctrl+Z before closing the workbook. Undo history will not be deleted just by saving it. If you close the workbook and lose your undo history, restore it from the previously copied sheet. Since duplicate deletion cannot be performed in a range where there are outlines or subtotals, paste the values into the copy without destroying the original structure.
- Cancel with Ctrl+Z before closing the workbook
- See copy after closing
- Correct the collation columns and rerun
Limitations and requirements
- Removing duplicates is data deletion, not hiding.
- Columns that are not selected will also be removed from rows that are determined to be duplicates.
Frequently asked questions
How can I avoid deleting someone with the same name?
In addition to names, email and employee numbers are also used as matching columns, and only rows with matching combinations are considered duplicates.
Are full-width/half-width characters and trailing spaces treated the same?
Even if the display is similar, the value may be different due to blank spaces, etc. Make a judgment after creating a confirmation column formatted with TRIM etc.
Official sources and verification date
Sources checked: . Check the official sources below for changes to supported systems, plans and menus.