ad

FIND DUPLICATES IN EXCEL

FIND DUPLICATES IN EXCEL
FIND DUPLICATES IN EXCEL

1. Select the range A1:C10.
Find Duplicates in Excel

2. On the Home tab, click Conditional Formatting, Highlight Cells Rules, Duplicate Values...
Click Highlight Cells Rules, Duplicate Values
3. Select a formatting style and click OK.
Select a Formatting Style
Result. Excel highlights the duplicate names.
Duplicates
Note: select Unique from the first drop-down list to highlight the unique names.
As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.
4. First, clear the previous conditional formatting rule.
5. Select the range A1:C10.
6. On the Home tab, click Conditional Formatting, New Rule...
New Rule
7. Select "Use a formula to determine which cells to format".
8. Enter the formula =COUNTIF($A$1:$C$10,A1)=3
9. Select a formatting style and click OK.
New Formatting Rule
Result. Excel highlights the triplicate names.
Triplicates
Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell. Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.

Note: you can use any formula you like. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight the names that occur more than 3 times.


find duplicate values using Conditional formatting:-

  Enjoy,,...