Custom Validation in Excel

Have you ever tried to create a worksheet where you dont want the user to create duplicate entries...??

Its quite possible in a fairly simple way in MS Excel..!

Say you are typing a list of names, where you don't want any name to be repeated once:

Step 1:

Step 2:

Step 3:

The formula is a relative formula where it counts the values between the first cell of the column till the current cell to check if the value entered in the current cell is not repeated.

Step 4:

You may define custom error messages too!

Step 5:

Copy the first cell to the desired number of cells in the list in order to apply the validation to all the cells in the list.

Now try entering few names in the column.

Result:

So... isnt it easy? Try it out yourself.. and do let me know if this works!!