DATA QUALITY

Find Duplicates

Detect repeated IDs and understand when duplicates are valid or problematic.

After this lessonUse COUNTIF logic to flag repeated keys.

How it works

A duplicate is not automatically an error. The important question is whether the column is supposed to be unique. Use a fixed reference range and count each row key to flag repeats.

For very large work files, WorkTools Duplicate Finder can group repeated keys and export them without writing formulas.

REAL WORK

Check repeated HU numbers, employee IDs, invoice numbers or master-data keys.

SYNTAX

The pattern

=COUNTIF($A$2:$A$100,A2)>1

Example

=COUNTIF(A2:A6,A2)

TRY IT

Test the formula

The sample sheet is local to this page. Change the formula and see the result immediately.

Result

QUICK CHECK

A duplicate key is always wrong.

Progress is stored only in this browser. No account required.