Suppose, we have records containing duplicate values. And we want to find records that can repeat many times in records in Excel. So, In MS Excel tools provide following way to find Duplicate value in Excel or Find Duplicate in Excel.
- Find Duplicate through Simple Process in Excel
- Find Duplicate with Custom Color or Style in Excel
- Find Duplicate through Function in Excel
- Find Duplicate Row in Excel Using Formula
So, Lets start with every option in given above with following steps:
(1). Find Duplicate through Simple Process in Excel :
For Simple Process, you have to follow the steps: 1.
Select All Rows And Columns in Which you want to find duplicate items. 2.
Find “Conditional Formatting” option from “Home” Tab.
3.
Click on “Conditional Formatting”, it will show popup List Containing many options. 4.
Go to “Highlight Cells Rules”. It also shows a list of Option. Click on “Duplicate Values..” option. 5.
This will show “Duplicate Vales” window. Contains options and Color indicator for duplicate value. 6.
After Selecting desire color. Click on “OK” button. This will highlight duplicate values or items with chosen color.
(2). Find Duplicate with Custom Color or Style in Excel :
If you want to change Duplicate Item’s cell’s color, border or font then, you have to follow steps: 1.
First Select All Rows and Columns in which you want to indicate duplicate values or items. 2.
In “Conditional Formatting” click on “New Rule…” from it. 3.
This will display “New Formatting Rule” dialog. 4.
Select “Format only unique or duplicate values”. After this option selection, press “Format…” button. This will show “Format Cells” dialog. 5.
If you want to Change Font Name, Size or Font Color then go to “Font” Tab. If you want to change border Style then go to “Border” Tab. If you want To change cell’s background color then go to “Fill” Tab. 6.
After that press “OK” button from “Format Cells” dialog. Then press “OK” button from the dialog of “New Formatting Rule”. 
This way you can Highlight Duplicate in Excel.
(3). Find Duplicate through Function in Excel :
Now if you want to find duplicate items through custom rules applying with formula. Then we have to use “COUNTIF” function like following steps: 1.
Same as above Go to “Conditional Formatting” then click on “New Rule…”. 2.
This will show “New Formatting Rule” dialog. Here we select “Use a formula to determine which cells to format”. After selecting this, “Format values where this formula is true:” Formula Box will appear. 3.
The duplicate formula function Syntax is :=COUNTIF (range, criteria)>1
Details:
=COUNTIF(Selection Of Rows and Columns, Selection For Which cell will be count) > 1
Example:
=COUNTIF($A$2:$B$11, A2:B11) >1
4.
Then click on “Format…” button for color and style of duplicate items. Then click “OK” button.
(4). Find Duplicate Row in Excel Using Formula :
Suppose, you want to check duplicate Row in Excel not duplicate cell. Then you have to use same process as describe above but using different formula “COUNTIFS” with following steps: 1.
Same as above Go to “Conditional Formatting” then Click on “New Rule…”. 2.
This will show “New Formatting Rule” dialog. Now go to the option “Use a formula to determine which cells to format”. After Selecting this, “Format values where this formula is true:” Formula Box will appear. 3.
Write formula as describe below:=COUNTIFS($A$2:$A$11, $A1, $C$2:$C$11, $C1)>1
This will show Duplicate Row in Excel.
So, In this way we can find the duplicate items in Excel and Duplicate rows in Excel and find Duplicate in Excel with easy tech tips.
0 comments: