Preventing Duplicate Entries using Data Validation in Excel

If you are setting up a spreadsheet for someone else to enter data this is a neat trick. You can use the Data Validation and the CountIF feature to check for duplicate entries and then not allow the user to enter the duplicate data.

Let’s assume that the data you want to check is in Column A.
Either select tpreventing duplicate entrieshe entire Column or select the cells in Column A where the data is going to be entered.
Click on the Data tab and select Data Validation from the Data Tools group

  • In Allow: dropdown box, Select Custom
  • In formula type =CountIF(A:A,A1)=1
  • Click OK.
  • Now go to  Column A and enter some data in a few cells and then try to enter a duplicate piece of data. Excel will pop up with a restricted dialog box and tell you to retry.

 

Caveats:
This works great for new data however if you select cells where there is already data, even duplicate data, Excel will just ignore it. Also, if someone copies and pastes data into Column A it will override the data validation.

If you did not want to select all of Column A but instead wanted to select cells A5:A50 then the formula would be =CountIF($A$5:$A$50,A5)=1. If you forget the absolute cell references it will not work properly.

 

 

 

 

One Response

  1. Ravi says:

    Thanks for Sharing Post on Data Validation in Excel

Leave a Reply

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

  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas

  • ” I was able to get a head start on my credit losses research through your CPE self study this past weekend.  Excellent quality format – so much better than some of the other resources I’ve been using lately.”  Dec 12, 2018

    - Andrea Ll.

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue