Prevent entering duplicate values in Excel
For example, while preparing a list of 30 invitees for a function, you want to avoid entering the same name more than one time.
To do it in Excel, here is the answer:
a) Select the range where duplicate entries have to be avoided. Under "Data" tab, click Data Validation -> Data Validation.
![excel prevent entering duplicate values](images/faq_images/prevent-entering-duplicate-values-1.png)
b) Select "Custom" for allow field and enter =COUNTIF($AM$2:$AM$31,AM2)=1 in Formula field where $AM$2:$AM$31 corresponds to the range where duplicate entries have to be avoided.
![excel prevent entering duplicate values](images/faq_images/prevent-entering-duplicate-values-2.png)
c) Click on "Error Alert" tab. Check "Show error alert after invalid data is entered" option if it is not checked already. Change the Title and Error Message fields as appropriate.
![excel prevent entering duplicate values](images/faq_images/prevent-entering-duplicate-values-3.png)
c) When user enters a name (John Miller in the example below) that already exists in the range, error message appears asking user to retry or cancel.
![excel prevent entering duplicate values](images/faq_images/prevent-entering-duplicate-values-4.png)
You can find similar Excel Questions and Answer hereunder
1) Is there a way to get a log value of a number using Excel?
2) How can I prevent users from seeing / accessing my macro code?
3) How can I add and link a Chart Title to a cell value?
4) How can I ensure that user enters only certain acceptable values in an input cell?
5) I am not able to use VLOOKUP as the lookup value column is to the right of the column that has the information I need. Is there an alternative option?
6) How do I find the median salary of employees with the same skillset in my Organization?
7) How can I find the slab in which a given value fits?
8) In a Table , how can I remove duplicate records?
9) Is there a way I can easily identify the duplicate values in a dataset?
10) Given a raw data Table, how can I find the value of a field for a specific value of another field?