Category Archives: Data Validation

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.

 

 

 

 

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry

  • Great course! Price was right and the material was very thorough.

    - Alex

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott