Prevent Duplicate Entries

Excel Formulas › Data Validation

All versionsData ValidationCOUNTIF

To stop someone typing the same ID or email twice, add a Data Validation rule with a COUNTIF formula. Excel rejects the entry the moment it would create a duplicate — cleaner than hunting for dupes after the fact.

Quick formula: select the column, then Data → Data Validation → Allow: Custom, and enter:
=COUNTIF($A$2:$A$1000, A2) <= 1
COUNTIF counts how many times the new value already appears; allowing only ≤ 1 blocks a second one.

Functions used (tap for the full reference guide):

COUNTIF

How it works

Trying to enter a value already in the column is rejected. The check column shows what the rule evaluates.

AB
1Employee IDCOUNTIF
2E-1001 → OK
3E-1011 → OK
4E-1002 → rejected

The formula

The Data Validation Custom formula (with the top cell of the selection as the reference):

=COUNTIF($A$2:$A$1000, A2) <= 1 // more than one match → entry blocked

How it works

The rule allows an entry only if it stays unique:

  1. Select the whole column range first (e.g. A2:A1000) so the rule covers every entry cell.
  2. Data → Data Validation → Allow: Custom, and enter =COUNTIF($A$2:$A$1000, A2) <= 1 — the list range is locked, the test cell A2 is relative.
  3. When you type a value, COUNTIF counts how many times it now appears. A unique value counts 1 (allowed); a duplicate counts 2 (rejected).
  4. Excel blocks the duplicate and shows an error — add your own message on the dialog’s Error Alert tab.

Validation doesn’t catch pasted duplicates. Pasting can bypass the rule. After a paste, run Data → Data Validation → Circle Invalid Data to flag any duplicates that slipped in.

Try it: interactive demo

Live demo

Existing IDs: E-100, E-101. Type one to see if it’s accepted.

Result:

Variations

Case-sensitive uniqueness

COUNTIF ignores case; use SUMPRODUCT/EXACT to enforce it:

=SUMPRODUCT(--EXACT($A$2:$A$1000, A2)) <= 1

Unique combination of two columns

No repeat of the same name+date pair:

=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2) <= 1

Find existing duplicates

Flag dupes already in the data:

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

Pitfalls & errors

Reference the right top cell. The Custom formula must use the active cell of the selection (e.g. A2 if the selection starts at A2). Get this wrong and the rule checks the wrong cell.

Paste bypasses validation. Data Validation only fires on typed entries. Use Circle Invalid Data after bulk pastes.

COUNTIF is case-insensitive. “abc” and “ABC” count as the same dupe. Use the EXACT version for case-sensitive IDs.

Practice workbook

📊
Download the free Prevent Duplicate Entries practice workbook
An ID column with a live Custom validation rule blocking duplicates, plus the case-sensitive and two-column variants and setup steps. No sign-up required.

Frequently asked questions

How do I prevent duplicate entries in Excel?
Select the column, then Data > Data Validation > Allow: Custom, and enter =COUNTIF($A$2:$A$1000, A2)<=1. Excel rejects any value that would appear more than once.
How do I prevent duplicates across two columns?
Use COUNTIFS in the custom rule: =COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2)<=1 blocks repeats of the same pair of values.
Does data validation stop pasted duplicates?
No, pasting can bypass validation. After a paste, use Data > Data Validation > Circle Invalid Data to flag any duplicates that got through.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Create a drop-down list · Flag duplicates · Restrict input to whole numbers

Function references: COUNTIF