Excel has some amazing features and as you keep on exploring it, you find it more interesting. Today we are going to talk about how we can use formula to prevent duplicate entries.
Let's see one example:
1. We have entered data as shown in below image. We have Roll no's in Column B. We want to prevent data from duplicate Roll Nos.
2. To do this, Select the whole range, Go to Data-> Data Validation and click on Data Validation.
3. In Settings field-> In Allow-> Select Custom from dropdown list. In formula bar write down the formula as below.
Here the Formula
=COUNTIF(range,cell)=1 means the value may only occur once (=1) since we don't want duplicate entries. So,COUNTIF($B$2:$B$12,B2)=1 means in range B2:B12, cell B2 value i.e 1 should be only once. It should not repeat further. So , if you try to put that, you will get an error message.
Related Link: How to Use COUNT ,COUNTIF, COUNTIFS Function?
4. To provide an Error message, select tab Error Alert and provide appropriate Title and Error message.
5. Now try to enter any repeated value in cell B6 and you will get error message.
Done. Now don't worry about any duplicate entries in you data!!
Explore more, It's Amazing…
3,581 total views, 8 views today