Highlight Duplicates (Table of Contents)
- Highlight Duplicates in Excel
- How to Highlight Duplicate Values in Excel?
- Conditional Formatting- Duplicate Values Rule
- Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)
Highlight Duplicates in Excel
When you are working on a large dataset then it might have duplicate entries also. You need to find those duplicate values in excel and take action accordingly. So when you are dealing with duplicate entries then you need to be very cautious as it will impact your dataset, if missed anything. After finding duplicate values in excel, we will highlight them so that we can take care of those in our dataset.
There are various ways to find duplicate values in excel. They are:
- Conditional Formatting – Using Duplicate Values Rule
- Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)
How to Highlight Duplicate Values in Excel?
Highlighting Duplicate Values in Excel is very simple and easy. Let’s understand the working of finding and highlighting duplicate values in excel by using two methods.
Conditional Formatting – Duplicate Values Rule
Here we will find the duplicate values in excel using the conditional formatting feature and will highlight those values. Let’s take an example to understand this process.
Example #1
We have given the below dataset.
For highlighting the duplicate values in the above dataset, follow below steps:
- Select the entire dataset.
- Go to the HOME tab.
- Click on Conditional Formatting option under the Styles section as shown in below screenshot.
- It will open a drop-down list of formatting options as shown below.
- Click on Highlight Cells Rules here and it will again display a list of rules here. Choose Duplicate Values option here.
- It will open a dialog box of Duplicate Values as shown in below screenshot.
- Select the color from the color palette for highlighting the cells.
- It will highlight all the duplicate values in the given data set. The result is shown below.
With the highlighted duplicate values, we can take action accordingly.
In the upper section, we highlighted the cells with conditional formatting inbuilt feature. We can also do this method by using an Excel function.
Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)
We will use here COUNTIF function. Let’s take an example to understand this method.
Example #2
Let’s again take the same dataset values for finding the duplicate values in excel.
For highlighting the duplicate values here, we will use COUNTIF function here that returns TRUE, if a value appears more than once in the list.
The COUNTIF function, we will use like shown below:
=COUNTIF(Cell range, Starting cell address)>1
Follow the below steps to do this.
- Select the whole dataset.
- Go to the HOME tab and click on Conditional Formatting option.
- It will open a drop-down list of formatting options as shown below.
- Click on New Rule option here. Refer below screenshot.
- It will open a dialog box for creating new custom rule here as we can see below.
- Select the last option “Use a formula determining which cells to format” under Select a Rule Type section.
- It will display a formula window as shown below.
- Enter the formula as =countif($A$3:$F$12,A3)>1 then click on the Format tab.
- Choose the Fill color from the color palette for highlighting the cells and then click on OK.
- This will highlight all the cells having duplicate values in the dataset. The result is shown below:
Things to Remember about Excel Highlight Duplicate Values
- Finding and highlighting the duplicate values in excel often comes in use while managing attendance sheet, address directories or other related documents.
- After highlighting duplicate values, if you are deleting those records then be extra cautious as it will impact your entire dataset.
Recommended Articles
This has been a guide to Highlight Duplicates in Excel. Here we discuss how to highlight duplicate values in excel by using two methods along with practical examples and downloadable excel template. You can also go through our other suggested articles –
