Prevent Duplicate Entries in Excel (+ video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

There are times you may want to prevent duplicate entries in Excel. For example, if you’re working with a list of invoice numbers and don’t want to accidentally repeat any of them.

You can limit the number of times a number (or word) is repeated in a column, row, or a selected range of cells by applying a custom Data Validation rule to those cells.

What is Data Validation in Excel?

Data Validation in Excel is a feature used to limit what is entered in a cell(s) to entries that fit specific criteria. For example, say you have a column for state abbreviations, you may want to restrict the number of characters entered to only 2 per cell.

The criteria is set in the form of a Data Validation rule.

When you apply a Data Validation rule to a range of cells, Excel checks what is entered in each cell to see if it matches the requirements of the rule. If it does not, it will prevent the user from making the entry until they correct it to match the rule or alert them that their entry does not match the parameters of the rule. You can choose the type of alert.

Click here to read our article on how you can customize your own Data Validation Alerts to prevent an entry or simply communicate a warning.

You can find Data Validation in the Excel Ribbon. It will be in the Data tab / in the Data Tools section. Click on Data Validation to open it.

Data Validation Custom Rule to Prevent Duplicate Entries

Click here to watch the tutorial

To create a custom Data Validation rule, go to the Settings tab of the Data Validation feature. In the “Allow:” field box select Custom.

Then, in the “Formula:” field box enter:

=COUNTIF(range, criteria)=#

Here’s how –

The COUNTIF function will count a cell if it matches the criteria that you set.

1. “range” will be the range of cells you would like to apply the Data Validation rule to. This can be a column, a row, or a combination of. *Making sure to lock the range of cells by using your F4 key to create an absolute reference.
2. “criteria” will be what the function will evaluate the other entries against. In this example, you will set this to the first cell in your range. This is important to ensure that no cells in your range are missed by your Data Validation rule.
3. Lastly, you will need to include =1 (or other number*) at the end of your COUNTIF function. 

*The 1 represents the number of times you want to allow the number or text to be entered in the selected cells. For example, if you would like for the entry to appear no more than 2 times, you would use =2.

Want to learn more about Data Validation? Here’s how you can apply multiple Data Validation rules to the same cells

---

---