Highlight Duplicate Values in Excel

Add to Favorites
Author:

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

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Prevent Duplicate Values in a Range in Excel
Tutorial: Ensure that your lists and data sets are full of only unique values by preventing a user ...
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...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course