Preventing Duplicates While Entering Data in Excel 2010

In this article we will learn about how to prevent duplicates while entering the data in Microsoft Excel 2010. We can use Countif formula to prevent the duplicates entry.
COUNTIF: – By using this formula you can identify the duplicate entries in a data and can capture duplicate entries in a range.

Syntax of “COUNTIF” function:    =COUNTIF (range,criteria)

Example:-

img1

We have table in range A2:A11, in which needs to count of states that how many times states are repeating in Column A.

  • Select the cell B2, and write the formula =Count if (A2:A11, A2) press enter.
  • The Count if Function will return 4, it means “Washington” is repeating 4 times in Column A.

img2

  • Copy formula by pressing the key “CTRL+C” then select the range B3:B11 and paste the formula by pressing the key “CTRL+V”.
  • Select the range B2:B11 by using the key “CTRL + SHIFT + Down Arrow Key” & Copy by pressing the key “CTRL + C”, right click of the mouse select “PASTE SPECIAL”.
  • Paste Special dialog box will appearclick on “Value” then click on ok to convert the formula into text.

Let’s take an example to understand how and where we can prevent the duplicate entries.

We have a data of Mobile nos. In which we want to stop the duplicate entries of mobile numbers.

img3

To preventing duplicates while entering in the data follow below given steps:-

  • Select the data range of Mobile number in the range B2:B10.
  • Go to “Data” tab and click on Data Validation from Data Tools group.
  • “Data Validation” dialog box will appear. In “Settings” tab select “Custom” from the Allow drop down list.
  • The formula tab will get active.
  • Write the formula in “Formula” box =COUNTIF(B:B,B1)=1

img4

  • Go to “Error Alert” tab and type the message in “Alert box” , “Duplicate Entries are not allow” and click on ok.

img5

  • To check the Data Validation is doing proper work or not enter the same mobile number in cell B11 which is in B10 cell.
  • After pressing the Enter key on your keyboard Error Alert message will get appear.

img6

This is the way you can restrict the duplicate entries in a data by using the Data Validation option in Microsoft Excel 2010 and 2013.

Users are saying about us...

  1. How do you sort the column with the duplicate error message? Can the column be sorted in ascending order? I want all the messages with duplicate to be together and same with the unique messages. I’m not getting that.

  2. “I found this tip very useful and I have shared this with all my colleagues.
    Keep up the good work.
    I am an excel enthusiast myself and would like to share my knowledge too.”

  3. “I am running Excel 2001 for the MAC.
    I don’t understand what I am supposed to put in the Error message Box. “” in the Error msg box enter the value you entered already appears in the list”” “

  4. “You need to type the message that is in the following brackets:
    “”The value you entered already appears in the list””
    This is the message that will pop up when a duplicate is found. “

  5. I FOLLOWED ALL THE STEPS AND FOR SOME UNKNOWN REASON IT DOES NOT WORK. I TRIED BOTH FORMULAS =COUNTIF(B:B,B1)=1 and =COUNTIF(B2:B500,B2)=1 THE SYSTEM STILL ALLOWS ME TO ENTER DUPLICATE ENTRIES.

    PLEASE HELP!

    • Hi Trish,

      Make sure that the “Show error alert after invalid data is entered” is checked! If that is not checked, you would be able to enter duplicate entries.

      Also, in case you are trying to copy and paste the value from some other cell or drag the formula to the data validation cell, then too it will not prevent any duplicate entries. For that, we have to first protect the workbook.

      We hope it works for you know and in case if you still see any error, please feel free to write us back.

      Best Regards,
      Site Admin

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>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube