How to Prevent Duplicate Entries in Excel With Validation

To prevent duplicate entries in Excel, add a custom data validation rule with COUNTIF: =COUNTIF($A$2:$A$500,A2)=1. Excel then refuses any value that already appears in the column. This guide is for anyone who keeps a list of IDs, invoice numbers, emails or employee codes that must be unique. It goes further than the short rule in our data validation rules guide: how the formula works, which alert to choose, what to do about duplicates already in the list, and where the rule falls short.

How to prevent duplicate entries with data validation#

The rule is a custom formula that returns TRUE when an entry is allowed. Set it up like this:

1. Select the cells to protect, starting from the top, for example A2:A500.
2. Go to Data > Data Validation (Windows shortcut: Alt, A, V, V).
3. Under Allow, choose Custom.
4. In Formula, type =COUNTIF($A$2:$A$500,A2)=1.
5. Open the Error Alert tab, choose Stop, and write a message such as "This invoice number is already in the list."
6. Click OK.

Now type INV-1002 twice. The second time, Excel shows your message and refuses the entry. If you only need a fixed list of allowed values instead, a drop-down list is simpler.

Why the COUNTIF rule works#

COUNTIF counts how many cells in a range match a value. The rule counts how many times the new entry appears in the whole column, including the cell you just typed in. If the count is 1, the value is new. If it is 2 or more, it is a duplicate, the formula returns FALSE, and Excel blocks it.

Two details make it work in every row:

• $A$2:$A$500 is locked. Every cell checks the same full range.
• A2 is relative. You write the rule for the top cell, and Excel shifts it to A3, A4 and so on for the cells below.

If you write A$2 or $A$2 for the second part, every cell checks the value in A2 instead of its own value, and the rule stops making sense. The COUNTIF function guide explains the criteria argument in more detail.

Test the rule in a cell first#

Data validation gives no feedback while you write the formula, so test it next to the data. In C2, type =COUNTIF($A$2:$A$6,A2)=1 and fill down:

Rows 3 and 5 return FALSE, because COUNTIF is not case-sensitive: INV-1002 and inv-1002 count as the same value. Once the results look right, copy the formula into the Custom box.

Choose the right error alert#

The Error Alert style decides whether people can override the rule. There are three:

Use Stop for real keys such as invoice numbers or employee IDs, where a duplicate breaks lookups and totals. Warning suits soft rules, such as a customer name that might legitimately repeat. Write a message that says what to do, for example "Invoice numbers must be unique. Check the list before you add a new one."

You can also add an Input Message, shown when the cell is selected, so people know the rule before they type.

Prevent duplicates across two columns#

Sometimes a value can repeat, but a combination can't. A customer can place many orders, but not two orders with the same order date and amount, for example. Use COUNTIFS with both columns and select A2:B500 before adding the rule:

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

Note the $ before the column letters in $A2 and $B2. The rule applies to two columns, so without them, Excel would shift A2 to B2 for cells in column B and compare the wrong values. The guide to finding duplicates across two columns shows more combinations.

Find duplicates already in the list#

A new rule only checks values typed after you add it. Old duplicates stay where they are. Three ways to find them:

1. Circle Invalid Data. Go to Data > Data Validation (arrow) > Circle Invalid Data. Excel draws a red circle around every cell that breaks the rule, so both copies of each duplicate are circled. Clear Validation Circles removes them.
2. A check column. Keep the test formula from above, or use =COUNTIF($A$2:$A$500,A2)>1 to show TRUE on duplicates. Then filter that column for TRUE to see only the problem rows. The guide to filtering data shows how.
3. Conditional formatting. Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to colour them. See how to highlight duplicates.

To count how many values are repeated, =SUMPRODUCT(--(COUNTIF(A2:A6,A2:A6)>1)) returns 2 on the table above: both cells in the pair. Decide which copy is correct before deleting anything.

Limits of the no-duplicates rule#

Data validation guides people who type; it doesn't lock the data. Know where it falls short:

• Pasting replaces the rule. Pasting a cell over a validated cell brings the pasted cell's validation, or none. Use Paste Special > Values (Windows: Ctrl+Alt+V; Mac: ⌃⌘V), and protect the sheet if the list matters.
• Formulas aren't checked. Only typed values are validated, not results calculated by a formula.
• Wildcards. COUNTIF treats * and ? as wildcards. An entry like INV-10* matches every invoice starting with INV-10, so it can be refused wrongly.
• Long numbers. COUNTIF reads text that looks like a number as a number, and Excel keeps only 15 significant digits. Two different 16-digit card or account IDs can look like duplicates.
• Text over 255 characters. COUNTIF returns #VALUE! for very long criteria, so long entries are refused.
• Case. Case doesn't count. INV-1002 and inv-1002 are one value.

When wildcards, long numbers or case matter, compare the cells directly instead of using COUNTIF:

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

The first ignores wildcards and compares exact text. The second also respects capital letters. Both are slower on very large ranges, so keep the range to the rows you need instead of the whole column.

In an Excel table, the validation rule extends to new rows automatically. The formula can't use table names such as Table1[Invoice no] directly, so keep the plain range with a generous end row.

Practise it#

The rule is one COUNTIF, and spotting the duplicates it misses is a filtering job. Practise COUNTIF in a live spreadsheet, then practise filtering a table to review the rows your check column flags. ExcelReady checks each formula as you type. Start with the invoice test in the exercise on this page.