Drop Down List in MS Excel





We have already learned about Data Validation in MS Excel. Now You will learn about How to Prevent Duplicate Entries in MS excel with the help of data validation.

How to Prevent Duplication in MS Excel
Let's Learn with Example
Here, in this example we will prevent duplicate entries of service code. 

>> Select the range E2:E11.

>> On the Data tab, Click Data Validation.
>> when you click Data validation, Dialog box will appear.
>> In the Allow Box, click Custom.
>> In the Formula box, Enter the Formula =COUNTIF($E$2:$E$11,E2)=1
** As you have selected the complete Range from E2:E11 in the Formula Box. the formula will apply to all the cells from E2:E11
** We have Applied COUNTIF function because it counts the number of values of selected range (E2:E11) that are equal to value in the cell E2
** We have applied (=1) as we want the value should not be duplicated and occur only once.
>> Your can Enter Input Message And Error Alert
Click Ok. You are done. 
Click on the any cell And enter an old service code, Excel will automatically show the Error Alert.
Let's see with Example how it works:
>> Enter an already entered service code in cell E6.
Excel shows an Error Alert. You have already entered that Service Code.


Must read for you :