Table of Contents
Highlight Duplicate values in Excel
When we are working with huge data especially financial data we may require to find the duplicate values. Duplicate values could be duplicate invoice entry, duplicate indent, duplicate of anything, which causes errors to our reporting purpose.
Duplicate entry not necessarily in a single column it could be in multiple columns, it could be in multiple rows or in the entire data range itself. Finding those duplicate values is not that easy without the help MS Excel in the modern data-driven world. Working with big data set is no surprise these days.
For all our good sake we have a tool in excel which can identify and highlight duplicate values in excel. In excel we have a tool called Conditional Formatting to highlight all the duplicate excel values in the selection range.
Excel goes one step further from identifying the duplicate values to remove them from the selection range if required by the user. In this article, I will explain you the process of identifying, highlighting, and removing duplicate values in excel.
How to Highlight Duplicate Values in Excel?
Below are some of the examples that can help you to identify and remove duplicate values in Excel.
Example #1 – Highlight Duplicate Excel Values using Conditional Formatting
Conditional Formatting is one of the simplest but very powerful built-in tools in excel. It is useful to format the cells based on the conditions we give, that is why it is named as Conditional Formatting.
Mr Badri has a list of few names. In this list, Mr Badri got few duplicate names. Mr Badri needs to identify those duplicate names. I will help him out to identify and remove those duplicate values in the list.
Below is the image of that name list that contains duplicate values.
Step 1: Select the list range that contains duplicate values. (Select only the data range, not the entire worksheet)
4.9 (1,353 ratings)
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.
Example #2 – 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 me 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 entry 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 the any of the duplicate invoice numbers it will find and highlight the duplicate excel entry.
Example #3 – 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 Ok button. It will instantly remove all the duplicate values from the selection. It will also show you the prompt 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 About Highlighting Duplicates in Excel
- We can select different formatting styles under 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 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
