This example will teach you how to find duplicates in Excel using conditional formatting. Follow this link to learn how to remove duplicates.
- Highlight a range A1: C10.
- On the Advanced tab Home (Home) press Conditional Formatting > Cell selection rules (Conditional Formatting > Highlight Cells Rules) and select Duplicate values (Duplicate Values).
- Define the formatting style and click OK.Result: Excel highlighted duplicate names.
Note: If in the first drop-down list you select instead of Recurring (duplicate) item Уникальные (Unique), then Excel will highlight only unique names.
As you can see, Excel highlights duplicates (Juliet, Delta), values that occur three times (Sierra), four times (if any), and so on. Follow the instructions below to highlight only those values that occur three times:
- First remove the previous conditional formatting rule.
- Highlight a range A1: C10.
- On the Advanced tab Home (Home) select a team Conditional Formatting > Create Rule (Conditional Formatting > New Rule).
- Click on Use a formula to determine which cells to format (Use a formula to determine which cells to format).
- Enter the following formula:
=COUNTIF($A$1:$C$10,A1)=3=СЧЕТЕСЛИ($A$1:$C$10;A1)=3 - Select a formatting style and click OK.Result: Excel highlighted values that occur three times.
Explanation:
- The expression COUNTIF($A$1:$C$10,A1) counts the number of values in the range A1: C10, which are equal to the value in cell A1.
- If COUNTIF($A$1:$C$10,A1)=3, Excel formats the cell.
- Because before pressing the button Conditional Formatting (Conditional Formatting), we chose the range A1: C10, Excel will automatically copy the formulas to the rest of the cells. So the cell A2 contains the formula:=COUNTIF($A$1:$C$10,A2)=3,cell A3:
=COUNTIF($A$1:$C$10,A3)=3 etc.
- Note that we have created an absolute link − $A$1:$C$10.
Note: You can use any formula you like. For example, to highlight values that occur more than 3 times, use this formula:
=COUNTIF($A$1:$C$10,A1)>3
=СЧЕТЕСЛИ($A$1:$C$10;A1)>3