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.

 

 

 

 

  • “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 am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

    - Janet

  • “Excellent Regulatory Review Course. The self-study format (E-Book) provided an excellent overview of the regulatory requirements for California CPAs. I will be using the E-Book as  a reference material. Thank you for providing a very comprehensive yet affordable option for this required CPE.”

    - Constance