When preparing data, working with duplicates is one of the most common things in Excel. With the conditional formatting function, you can quickly highlight the cells that contain duplicates. From there, you can decide how you want to deal with these duplicates. Please follow the steps below:
Step 1: Select the data cells or the data range;
Step 2: Click the "Home" tab from the Ribbon;
Step 3: Click the "Conditional Formatting" command in the "Styles" section;
Step 4: Select "Highlight Cells Rules" in the drop-down list, and click "Duplicate Values" from the extended list;
Step 5: In the "Duplicate Values" window, select "Duplicate" in the first box. You can always change to a different color from the second box;
Step 6: Click "OK" at the bottom.
After you highlight the duplicates, you can refer to sort by font color or cell color to move duplicates to the top of the list.
