Skip to content

How to Prevent Duplicate Entries in Excel

In this article, I will show you how to set up data validation on a specific range to prevent duplicate entries.

Step 1 – Select the Range

Start by selecting the range where you want to prevent duplicate entries.

Step 2 – Apply Data Validation

In the ribbon, go to Data Data Validation.

Under Allow, select Custom.

In the Formula Box, enter the following formula.

=COUNTIF(dataRange, topLeftCell)=1
=COUNTIF($A$2:$A$8, A2)=1

Note that the first reference is an absolute reference (contains dollar signs to lock the reference).

Finally, click on Ok. Now any duplicate entries will trigger Excel to throw an error.

Formula Explanation

The COUNTIF function counts the number of times the selected cell (2nd argument) appears in the selected range (1st argument). The formula then checks if this value is equal to one.

And so Excel will prevent the user from entering the value if the count is not equal to one.

Now the formula you entered for the data validation is applied to all cells in the selected range.

You want the 1st argument to stay the same for each cell in the selected range, so you make this an absolute reference. However, you want the 2nd argument to change. Therefore, this argument is a relative reference (without dollar signs).

So the formula for cell A2 is this.

=COUNTIF($A$2:$A$8, A2)=1

And the formula for cell A3 is  this.

=COUNTIF($A$2:$A$8, A3)=1

The 1st argument stays the same, and the 2nd argument changes.

So Excel is always checking the current cell against the selected range.

And that is how you prevent duplicate entries in Microsoft Excel.

This Post Has 2 Comments

  1. I think I asked in your Google Sheets post about the capabilities of Excel with this. I’m so glad you wrote a post about Excel too! This is really going to make my life a whole lot better. Can you change the data validation to a warning instead of preventing the entry?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top