In Microsoft Excel we can use data validation to prevent duplicity as & when we enter data. This article will help you stop duplicates appearing in one or multiple columns in the Excel sheet. So you can have only unique data in the 1st column of your table.
Data Validation is a very useful feature in Excel, you can use it to create drop down list in a cell and limit the values user can enter. But sometimes you may want to prevent users from entering duplicate values in a range of worksheet.
The following example shows how to use data validation to prevent users from entering duplicate values in range A2:A10
- Select the range A2:A10
- On the Data tab, click Data Validation or use shortcut keys “Alt + D + L”
- In the Allow list, click Custom
- In Formula box, enter the following
- Click on Error Alert tab
- Under Title enter: Duplicate Invoice Number
- In error message: You are not allowed to enter any duplicate entry in range A2:A10
- We can enter custom error message
- Click on ok button
Let us add few Invoice Numbers in column A to test the above steps.
Formula explanation: COUNTIF function has two arguments (range & criteria)
- =COUNTIF($A$2:$A$10,A2) counts the number of values in the range A2:A10 that are equal to the value in cell A2
- This formula can be tested in column B corresponding to cell A2
- Dragging down the formula, we will get 1; this means there is no duplicate entries matching to the corresponding value
- =COUNTIF($A$2:$A$10,A2)=1 will return TRUE till there is no repeat
Conclusion: In this way we can restrict user from entering duplicate items in a certain range.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org