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 *

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper

  • “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

  • “Thank you for a high quality CPE resource. As a CPA in a small nonprofit, I find your library quite helpful to meet my needs. I appreciate the service.”- 

    - Cheryl