Highlight Duplicate Values in Excel

Sections:

Highlight Duplicates

Arrange Duplicates Next to Each Other

Notes

Highlight Duplicates

1. Select the list of values that may contain duplicate values.
   
2. Go to the Home tab > Conditional Formatting button > Highlight Cells Rules > Duplicate Values
   
3. A small window will open. Select the color that you want to use for the highlight from the drop-down menu on the right or select Custom Format... and choose what you want.
   Make sure the drop-down menu on the left says Duplicate and then hit the OK button.
   
4. That's it! Now all of the duplicate values are highlighted.
   

To remove the highlighting, select the list of cells and go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Arrange Duplicates Next to Each Other

Once you have highlighted your duplicate values, it's nice to have them listed together so you can clearly see them.

1. Select the entire list, including the header cell.
   
2. Go to the Data tab and click Filter.
   
3. Click the drop-down arrow in the first cell and select either:
   Sort by Color and then the color to show on top. This option displays the entire list but with the desired color on top.
   
   Or, go to Filter by Color and select the color to show. This option displays only the desired color and all other cells are hidden.
   
4. Choosing the Filter by Color option gives us this:
   
5. You can also sort the remaining values so they appear in order.
   Click the drop-down arrow and then Sort A to Z or Sort Z to A.
   
6. Here is the final result:
   
   Only duplicate values are visible and they are sorted in alphabetical order.

To remove all of the filters, simply click the button in the first cell of the list and click the Clear Filter From option.

(You will not be able to reverse the sorting unless you hit Ctrl + Z to undo everything.)

Notes

Being able to quickly view duplicate values in a list is a very important thing to do in Excel. Memorize this tutorial and keep it for reference; once you follow these steps a few times, you will be able to do this in less than 10 or 15 seconds with ease.

Download the attached file to work with this example in Excel.