In this article, I will show you how to use data validation to prevent users from entering duplicate values in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to prevent duplicate values in Excel
1. Select the range A2:A20.
2. On the Data tab, in the Data Tools group, click Data Validation.
3. In Allow, click Custom.
4. In Formula, enter the formula shown below and click OK.
Explain:
- The COUNTIF function takes two arguments. =COUNTIF ($A$2:$A$20, A2) counts the number of values in the range A2:A20 that are equal to the value in cell A2.
- This value can only appear once (=1) because I don’t want duplicate entries.
- Because I selected the range A2:A20 before clicking Data Validation, Excel automatically copies the formula to other cells.
5. To check, select cell A3 and click Data Validation.
6. Enter a duplicate number.
Result:
To change the alert text, go to the Input Message and Error Alert tabs.
In addition, you can also view many other excel articles here.
