How to delete duplicate data in excel so that only one remains

How to delete duplicate data in Excel so that only one remains: first select the cell where the duplicate data is located; then click the [Data] tab at the top; finally click [Delete Duplicates] in the [Data Tools] group option.

Recommended: "Excel Tutorial"

Click any cell in the data range where you want to delete duplicate values grid, and then click "Remove Duplicates" in the "Data Tools" group under the "Data" tab, the "Remove Duplicates" dialog box will pop up. As shown in the figure below:

In the pop-up "Delete Duplicates" dialog box, you can select which column of duplicate values ​​you want to delete. If the column you want to delete duplicate values ​​exceeds If it is 1, it means that the data in these columns are repeated at the same time. At this time, click "OK" and a prompt box indicating that the deletion is completed will pop up, as shown in the following figure:

In addition to using "Delete Duplicates", you can also use the "Data" tab "Advanced" command in the "Sort and Filter" group under Copy to other location", then check "Select unique records" and click "OK", you can filter out "unique values", as shown in the figure below

The above are two methods of deleting duplicate values. The following introduces the method of extracting unique values ​​using formulas. Suppose you want to extract the unique value of the salesperson in column B, you can enter the array formula

=INDEX($B:$B,SMALL(IF(MATCH($B$2:$B$75,$B $2:$B$75,0)=ROW($B$1:$B$74),ROW($B$1:$B$74) 1,65536),ROW(A1)))&""then drag down, as follows As shown in the figure:

If you want to identify duplicate values, you can pass "Home"→"Style"→"Conditional Formatting"→"Highlight Cell Rules"→" Duplicate values" are identified. As shown in the picture below:

#This will delete it.

The above is the detailed content of How to delete duplicate data in excel so that only one remains. For more information, please follow other related articles on the PHP Chinese website!