Delete duplicates

If multiple users have worked on a table, or if it was created from multiple tables, it is very likely that it contains repetitive data. These data can be removed from the table automatically using the Remove duplicates command.

The search for duplicates in a table or a specified range is performed line by line. An example is shown in the figure: the rows that will be deleted if one column (left) or two columns (right) are selected for the search are highlighted in red.

When deleting duplicates, only the first variant of the found matches is saved, the rest are deleted.

Duplicate search and removal is not performed when:

•The selected range contains:

oArray formula

oCells of a pivot table

o"Smart table" or its part. If the range contains only smart table cells, duplicates are searched for and replaced.

oMerged cells: To find duplicates, each cell in the range must occupy the same number of rows and columns.

•There is a "gap" between the selected cells/rows/columns/ranges. For example, columns A and C are selected, but column B is not selected.

If the selected range contains hidden or filtered rows or columns, the values in them are ignored when removing duplicates. After removing duplicates, hidden rows and columns remain hidden. In the cells of hidden rows, the values may change because the cell data is shifted upward after the duplicates are removed.

To remove duplicates, follow the steps below:

1.Select a range to search for and remove duplicates.

2.Open the Remove duplicates window in the following way:

•In the Command menu, select Data > Delete duplicates.

3.In the Remove duplicates window, check the With header row box if the selected range contains a row with column names. This row will be excluded from the selected range.

4.Check the Expand automatically check box if you want to include data adjacent to the selected range in the search range that meets the conditions for duplicate search and removal (see restrictions above).

5.In the Columns area, if necessary, uncheck the columns that should be excluded from the selected range.

6.Click OK.

When duplicates are successfully deleted, a pop-up message will be displayed: "n duplicate found and removed. m unique value remains."

If there are no duplicates in the selected range, the pop-up message "No duplicates found" will be displayed.