Prevent Duplicate Entries in Excel

In Excel it is possible to prevent duplicate data entry with the help of data validation.   So for example, account numbers are being entered

into a particular column, to ensure no duplicates are entered use the following technique.  

=COUNTIF($B$10:$B$30,B10)=1 

Under the Data menu choose Data Validation.

From the top drop down choose Custom.

Put the Excel formula in the formula bar (there is an example to copy at the top of the page).  Click OK

If you attempt to put a duplicate anywhere in the range B10:B30 a message box like the above will appear.  This Excel solution can be quite useful when duplicates are not wanted in a list.