What is the formula to find duplicates in excel

Answer

To find duplicates in Excel, you can use the COUNTIF function. The COUNTIF function counts how many times a value appears in a range. If the count is greater than 1, the value is a duplicate.

Formula to identify duplicates in a column

Suppose your data is in column A, starting from A2. Enter the following formula in B2:

=IF(COUNTIF(A:A, A2)>1, "Duplicate", "Unique")

Explanation:

1. COUNTIF(A:A, A2) counts the number of times the value in A2 appears in column A.

2. >1 checks whether the count is more than 1.

3. IF(..., "Duplicate", "Unique") labels the value as "Duplicate" if it occurs more than once, or "Unique" if it occurs only once.

Formula to highlight duplicates with TRUE/FALSE

If you prefer a simpler TRUE/FALSE output, use:

=COUNTIF(A:A, A2)>1

• Returns TRUE for duplicates.

• Returns FALSE for unique values.

Conditional Formatting for duplicates

1. Select the range you want to check (e.g., A2:A100).

2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

3. Choose a formatting style and click OK.

This method visually highlights all duplicates without adding extra columns.

Notes for Windows vs Mac

• The formulas work the same on both Windows and Mac versions of Excel.

• Conditional Formatting menu might appear slightly different on Mac, but the path remains Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

This approach works efficiently for large datasets and ensures duplicates are clearly identified.The 

Yes · 0 No · 0 Add Comment · 0

Add ·

Report