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

 

img1

 

  • 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

 

img2

 

  • 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

 

img3

 

  • Click on ok button

Let us add few Invoice Numbers in column A to test the above steps.

 

img4

 

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.

 

image 48

 

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

 
 



One thought on “Preventing Duplicates While Entering Data in Excel

  1. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>