Data Validation: Preventing Duplicate Entries in Excel. This article delves into an effective method of preventing duplicate entries within a specified range using Data Validation.
Discover the significance and efficiency of this feature in eliminating typing errors and inaccuracies. Not only does it hinder users from inputting incorrect data, but it also optimizes time usage significantly.
Data Validation: Preventing Duplicate Entries in Excel
Let’s explore an example to understand how to thwart duplicate values within a range:
In the provided image, column C contains various products, and we aim to add more to this range. We desire Excel to immediately alert us upon entering a duplicate entry within this range, displaying a message such as “Duplicate Product” or “This product is already listed.” Essentially, the cell should only allow unique entries within the range.
To achieve this, follow three straightforward steps:
- Select the entire column C.
- Access Data Validation (or use “ALT + D + L”) to open the data validation dialog box.
Choose “Custom” from the “Allow” category to introduce a function in the formula bar that prevents duplicate entries.
Enter the COUNTIF function: =COUNTIF(C:C,C1)=1 
Skip the input message and navigate directly to the “Error Alert” tab.
Set the error style to “STOP.” In the title field, input “Duplicate / Invalid Entry!” Then, in the error message box, convey: “You’ve entered a product already existing in this column. Please input a unique product.” 
Click “Ok.”
Now, observe the existing five products in the range. Upon entering “Product 06” in cell A14, Excel accepts the new entry without errors because “Product 05” doesn’t exist within the range. 
However, when attempting to input “Product 04” in cell A145, an error message surfaces, precisely as designated for duplicate entries. This occurs because “Product 04” already exists in cell A12 within the range. 
This validation rule encompasses the entire range. If a duplicate value is entered in cell C1, it won’t be accepted and will trigger the customized error message.
In essence, any attempt to input a duplicate value within the range will be rejected, accompanied by an error message dialog box. Users can opt to “Retry” or “Cancel,” allowing them to rectify the entry by inputting a unique product code into the list.
By implementing Data Validation in Excel, this method effectively prevents users from entering duplicate entries within a designated range.
