Highlight Duplicate Values in Excel

Add to Favorites

How to highlight duplicate values in a list. Also, how to arrange those values next to each other so they are easy to see.

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Easily Compare Duplicate Values in Excel
Tutorial: Here, I'll show you a simple technique to quickly and easily compare large lists of duplic...
Prevent Duplicate Values in Excel
Tutorial: I show you how to prevent duplicate values being entered into Excel using Data Validation...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel.  This includes Ex...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Error Values in Excel - Full Explanation
Tutorial: Here, I'll teach you what the errors in Excel mean.  There are many errors that you can ...
MODE() - Find Most Repeated Value in a List in Excel
Tutorial: The MODE() function in Excel allows you to quickly find the most repeated or frequently oc...