What is the formula to highlight duplicate values

Answer

To highlight duplicate values in Excel, you can use Conditional Formatting with a formula.

1. Select the range of cells you want to check for duplicates.

2. Go to Home → Conditional Formatting → New Rule.

3. Choose Use a formula to determine which cells to format.

4. Enter the following formula:

   =COUNTIF($A$1:$A$100, A1) > 1

• $A$1:$A$100 is the range to check for duplicates. Adjust it to your dataset.

• A1 is the first cell in your selected range.

5. Click Format and choose a highlight color.

6. Click OK to apply.

Explanation

• COUNTIF(range, criteria) counts how many times a value appears in a range.

• The formula checks if the count is greater than 1. If yes, it highlights the duplicate.

Notes

• On Mac, the steps are identical; only the interface may slightly differ visually.

• This method highlights all instances of duplicates, not just the second or later occurrence.

Do you want me to show a formula that highlights only the second and subsequent duplicates?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report