How to Highlight Duplicate Values in Excel?
Below are the steps to find or highlight duplicate values in Excel.
- Step 1: Select the list range that contains duplicate values. (Select only the data range, not the entire worksheet)
- Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
The excel shortcut keys to highlight duplicate excel values is:
- Step 3: Once you click on Duplicate Values it will open up the below dialogue box. Here we can select the type of formatting we want. We can highlight both the cell and the text with three different colors, we can only fill the cell, we can highlight only the text values or else we can highlight the duplicate cells borders.
- Step 4: Once the formatting type is selected it will highlight all the duplicate excel values in the range. Here I have selected Light Red Fill with Dark Red Text.
Highlight Duplicate Values in the Entire Worksheet
We can, not only highlight the duplicate excel values in a single column rather we can do this task across the entire worksheet as well.
I have data in the first two columns. I want to give conditional formatting so that in the future if I enter any kind of duplicate values it should highlight the duplicate value for me.
- Step 1: Select the entire invoice number column. The reason we need to select the entire because here I am creating a validation so that if any of the duplicate entries made in the future it should automatically notify me the duplicate value is made.
- Step 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Step 3: Select the color you want to highlight. Here I have selected Green to fill with Dark Text.
- Step 4: Here I have already found four duplicate values.
- Step 5: Now try to enter any of the duplicate invoice numbers it will find and highlight the duplicate excel entry.
Remove Duplicate Values in Excel
Finding and Highlighting the duplicate values is definitely the needful skill at this point in time and you learned that in the previous example. In this section of the article, I will show you how to remove those duplicates from the selection.
- Step 1: Select the duplicate data range.
- Step 2: Go to Data > Remove Duplicates
The shortcut key to Remove Duplicates in Excel is:
- Step 3: Once you have selected the Remove Duplicate Values option it will open up the below dialogue box. Since I have only one column it is showing that single column header here.
Note: Make sure My data has headers checkbox is ticked.
- Step 4: Click on the Ok button. It will instantly remove all the duplicate values from the selection. It will also show you the prompt on how many duplicates are removed and how many unique values are remaining.
Look at the above image it has given dialogue box stating 3 duplicate values found and removed; 16 unique values remain.
Things to Remember
- We can select different formatting styles under the custom formatting option.
- While removing the duplicates it is very important to select the correct column header otherwise there are chances our data goes for a toss.
- Rely on conditional formatting for highlighting future duplicates as well.
- Finding and Highlighting duplicate excel values makes the data more accurate.
- There are many other cool options are residing under conditional formatting.
Recommended Articles
This has been a guide to Find and Highlight Duplicates in Excel. Here we discuss how to find and highlight duplicate values in Excel using the Conditional Formatting Method along with practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Apply Conditional Formatting in VBA Excel
- Add Excel Multiple Rows
- Remove Duplicates from Column in Excel
- Conditional Format for Blank Cells
- Excel Borders
- How to Count Unique Values in Excel?
- Highlight Every Other Excel Row
- Removing Duplicates in Excel
- Using Advanced Filter in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
