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.


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



  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 *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.