Prevent Duplicate Values in Excel

Add to Favorites
Author:

I show you how to prevent duplicate values being entered into Excel using Data Validation.

Let's say we have a list of part numbers and we want to make sure that there are no duplicates, that a part number is not put into the list more than once.

First, select the range where you will be entering the list:

Make sure to select enough rows so that all future parts will be within this list.  If you are in a version of Excel greater than 2007, you can simply select the entire column if you want.

Next, we need to create a formula to ensure no duplicates are allowed.

Here is the formula we need:

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

There are two things you will need to change here to suite your needs, the reference to the list $A$2:$A$15 and the reference to the first cell in the list A2

$A$2:$A$15 is the location of the list where we don't want to allow duplicates.  You MUST include the dollar signs $ in front of the column and row references for this to work correctly, this ensures this is an absolute cell reference, which means that it won't change.

A2 is the first cell of your list and it must NEVER include the dollar signs $ because it must always remain a relative cell reference, which means that it can update when copied to other cells.

Tip: When creating the formula, do it in the worksheet so you can make sure everything was typed correctly.  When you put the formula into the Data Validation window, it will not check it for errors like Excel does when you create it in the worksheet.

Once you create your formula, you can then input it into Data Validation to prevent duplicate entries.

Remember to have the list where you don't want duplicates selected, then go to the Data tab and click the Data Validation button:

A window will open and you should select Custom from the Allow field:

Now, we can input our formula into the Formula field that will appear.

Now, hit the OK button and you are all done!

Let's test it out.  When I try to enter asc-3 into cell A5, I get this error message:

I am now not able to enter a duplicate value into this list!

Notes

It's very important to select the entire range where you don't want duplicates before you apply Data Validation and it's very important that you leave the second range reference, in the case above A2, a relative cell reference, which just means to NOT put dollar signs in it.

When we now go to, for instance, cell A3 and click the Data Validation button, we see this:

Notice that everything in the formula has stayed the same EXCEPT that A2 has now become A3.  This is why that cell reference being relative is so important and this is what allows us to prevent duplicate values in the list.

Also, if you want a custom error message or hint to tell a user not to enter duplicate values, you can edit the options on the Input Message and Error Alert tabs, both of which are quite self-explanatory.

As you can see, Data Validation in Excel allows you to do some pretty powerful things and it will go a long way to helping you keep your data from being corrupted.

Don't forget to download the accompanying Excel spreadsheet so you can see this feature in action and check out our other Data Validation tutorials to learn more about what you can do with it in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Easily Compare Duplicate Values in Excel
Tutorial: Here, I'll show you a simple technique to quickly and easily compare large lists of duplic...
Highlight Duplicate Values in Excel
Tutorial: How to highlight duplicate values in a list. Also, how to arrange those values next to ea...
Prevent Duplicate Values in a Range in Excel
Tutorial: Ensure that your lists and data sets are full of only unique values by preventing a user ...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel. This includes Ex...
Compare Values in Excel - Beginner to Advanced
Tutorial: How to compare text, numbers, and dates in Excel - including case sensitive text comparis...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course