Identify, Mark, and Sort Duplicates in Excel

By Chris Hubbard Jackson, Director of Grants and Research, St. Charles Community College

This tip covers how to identify, mark, and sort duplicates in Excel - all without Excel freezing

1. Open the Excel file in which you would like to identify, mark, and sort the duplicates. Select the columns that include data.

2. On the Home Tab, click Conditional Formatting > Highlight Cell Rules > More Rules.


3. The “New Formatting Rule” Box will appear. Click Format only unique or duplicate values. 
     Note: Duplicate is selected by default, but you can change this to unique value. Whichever you select is what will be marked.


4. Under “Preview,” click Format.

5. Under “Fill,” select the color you want and click OK.
6. The “New Formatting Rule” box should now show a preview of how the duplicate text will look.

Click OK. The duplicate text will now be marked in the color you selected.
8. For ease of seeing the duplicates, you can sort each column separately. On the Home tab, click Sort & Filter > Custom Sort. If you get a Sort Warning, click “Continue with Current selection” > Sort > Sort on Cell Color > Order = Color on Top.
9. Repeat the prior step for each column of data. Here is what the data look like sorted.






To add a comment, Sign In
Total Comments: 1
King posted on 5/3/2018 10:58 PM
You can highlight rows and cells with different colors by groups of duplicates in your Excel sheet without using any formula using Dose for Excel Add-In: