Preventing Duplicates While Entering Data in Excel

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
=COUNTIF($A$2:$A$10,A2)=1

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.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

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 info@exceltip.com

shri
July 21, 2015 at 6:40 am

I would like to solve the following condition.

While entering the value to each cell in perticular column, I want to make sure that the new entry is not duplicate and if it is duplicate, then error message should pop up. How can I solve this problem?

Reply ↓