Use the Data Validation + Countif function. It Prevents the user from entering Duplicate data.

To do this:

1. Select a range of cells, for example, A2:A20.

2. On the Ribbon click <Data> à <Data Validation>

3. Select the [Settings] tab.

4. From the Allow dropdown list, select Custom.

5. In the Formula box, enter the following formula:


6. Type an appropriate error message in the [Error Alert] Tab

More Under Finance

Posted Under

Category : Finance (3612)
Type : Articles (15942) Featured (4130)
Tags : Excel Tips (35)

0 responses to “Excel Tips – How to Prevent Duplicates while entering data”

  1. Brij Mohan says:

    Prevent duplicates entry data fomula/excel tips is very useful and other excel tips is also very useful for me.

    Thanks/Regards Brijmohan

  2. Pankaj says:

    while copying and paste the duplicate entries in the same row. the same is permisable. so it’s not useful.

Leave a Reply

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