How to Highlight Duplicate Values in Excel Sheet?

Highlight Duplicates in Excel

There are many scenarios wherein you have a list of certain data in an excel sheet and you want to find duplicate entries or highlight duplicate values in that spreadsheet.

For your information, it will be a very easy task for you once you know the following steps or trick.

If you are reading this article, we are sure you that you must have a long list of data in excel in which you want to find duplicate values in it.

This process will help you find and remove duplicate data values and filter your excel sheet.

Find Duplicate Entries in Excel

Hereby, follow the steps given below to check the duplicate entries in an excel sheet.

[Read: Download Full Version of MS Office Professional Plus 2019]

Note: Backup your original sheet somewhere in case you are planning to delete duplicate entries.

For instance, we have created a sample excel sheet which contains duplicate data values. Screenshot shown below belongs to the sample excel.

Step 1. Open an Excel Sheet that contains all the data.

Step 2. Select the data you want to highlight to find duplicate values.

identify duplicates in excel sheets

Step 3. Click on Home Tab and follow the path below:

Conditional Formatting > Highlight Cells Rules > Duplicate values

highlight duplicate values in excel

Step 4. Duplicate Values window will open on your screen. You can choose the color option from the drop-down list to highlight duplicate data values.

We are using default one “Light Red Fill with Dark Red Text”, but you can choose any of the colors as per your desire.

how to find duplicates in large excel file

Once you are done. Click OK button to save the settings.

Step 5. Finally, the moment you click on duplicate values as on Step 3 above, you will notice that the duplicate values are highlighted in your excel spreadsheet.

Once you are done with Step 4, the settings are saved and the data is shown in the image below:

highlight duplicate values in excel sheet

We are done here to find duplicate data values. This is how you can easily highlight the duplicates in an excel sheet and analyze your data.

Highlight Duplicate Values in Excel Share on X

Share this post to help others!!

Leave a Reply