prevent entering duplicate values in Excel

Q41. In Excel, how can I prevent users from entering duplicate values in a range?

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

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 excel prevent entering duplicate values

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 excel excel prevent entering duplicate values

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 excel excel excel prevent entering duplicate values


You can find similar Excel Questions and Answer hereunder

1) How can I prevent other users from adding or deleting Sheets in file?

2) 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?

3) How can I add and link a Chart Title to a cell value?

4) In a Table , how can I remove duplicate records?

5) How to read a value from a cell in vba in Excel

6) Given a raw data Table, how can I find the value of a field for a specific value of another field?

7) How can I ensure that user enters only certain acceptable values in an input cell?

8) Is there a way to get a log value of a number using Excel?

9) I am considering an investment of $500 per month for 3 years @ 6% interest rate. What is the Present Value of investment?

10) How can I find the slab in which a given value fits?

11) Filtering the value field in a pivot table in Excel

12) How can I filter a table to get all records that have less than a particular value in a specific column?

13) Vlookup to return max value from multiple hits in Excel

14) How can I get the last non-zero value in a row?

15) How do I find the median salary of employees with the same skillset in my Organization?