Select all columns A- Menu-Format-Conditional Format

Formula-= countif (a: a, a 1)> 1- format-mode-red -OK-OK.

Any duplicate items will be filled in red.

If you want to filter duplicates, you need an auxiliary column. Suppose column b, enter in b 1:

=if(countif(a:a，a 1)> 1，a 1，"")

Formula pull-down

Filter non-empty cells in column B.

note:

Excel file, select the data range to be processed, and then click Menu Start-Conditional Format-Highlight Cell Rule-Copy Values respectively.

Confirm that duplicate values are highlighted in the default format of Excel. After that, duplicate data will be highlighted in pink.

Select the header row of the data table (the first row in the figure), and then click Start-Sort and Filter-Filter respectively to add the filter row to the data table. If the data table has no header row, please insert an empty row in front and select it as the filter row.

In the column where duplicate data needs to be filtered (as shown in the score column), click the downward black triangle, select Filter by Color, and select "Pink" in it.

Duplicate data will be displayed after filtering. If you need this data, please select the data range and copy and paste it into other worksheets. (If you want to cancel the filtering, press Ctrl +Z)