Prevent Duplicates Using Data Validation

  • Hello all:

    I am putting a list together in excel and I have items in columns A to D.

    If I enter an item in column a and I press enter, is there a way that excel could search column A to see if the number has been previously entered, if it's been previously entered, can I get a prompt saying "number already exists" or something like that?

    Thanks so much for your help.

  • After highlighting your input range, let's say A1:A500. Choose Data>Data Validation from the menu. On the Settings tab: Allow = Custom.

    In the formula box: =COUNTIF(A1,A$1:A$500)=1. (Watch the $ signs.) Type your warning message on the Error Alert tab.


  • Thanks much fifijazz & mhabib

    Your methods worked!

  • Check out for more information regarding Data Validation and Conditional Formatting in OzGrid's Handy Hints for Excel Spreadsheets.

    Barbara - aka The Cat Lady :cat:

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!