by Alexander Frolov, updated on
Today I'll tell you how to prevent duplicates from appearing in a column of your Excel worksheet. This tip works in Microsoft Excel 365, 2021, 2019, 2016, and lower.
We covered a similar topic in one of our previous articles. So you should know how to automatically highlight duplicates in Excel once something has been typed.
This article will help you stop duplicates appearing in one or several columns in your Excel worksheet. So you can have only unique data in the 1st column of your table be there invoice numbers, stock keeping units, or dates, each mentioned only once.
Excel has Data Validation - one unfairly forgotten tool. With its help you can avoid errors occurring in your records. We will be sure to devote some future articles to this helpful feature. And now, as a warm-up, you will see a simple example of using this option. :)
Suppose, you have a worksheet named "Customers" that includes such columns as Names, Phone numbers, and Emails you use for sending newsletters. Thus all email addresses must be unique. Follow the steps below to avoid sending the same message to one client twice.
Note: If your data are in a simple Excel range as opposed to a full-fledged Excel table, you need to select all the cells in your column, even the blank ones, from D2 to D1048576
=COUNTIF($D:$D,D2)=1
into the Formula box.
Here $D:$D are the addresses of the first and the last cells in your column. Please pay attention to the dollar signs that are used to indicate absolute reference. D2 is the address of the first selected cell, it is not an absolute reference.
With the help of this formula Excel counts the number of occurrences of the D2 value in the range D1:D1048576. If it is mentioned just once, then everything is fine. When the same value appears several times, Excel will show an alert message with the text you specify on the "Error alert" tab.
Tip: You can compare your column with another column to find duplicates. The second column can be on a different worksheet or event workbook. For example, you can compare the current column with the one that contains the blacklisted emails of customers
you don't won't to work with any longer. :) I will give more details about this Data Validation option in one of my future posts.
For example:
Title: "Duplicate email entry"
Message: "You have entered an email address that already exists in this column. Only unique emails are allowed."
Now when you try to paste an address that already exists in the column, you will see an error message with your text. The rule will work both if you enter a new address into an empty cell for a new customer and if you try to replace an email for the existing client:
On the fourth step choose Warning or Information from the Style menu list. The alert message behavior will change correspondingly:
Warning: The buttons on the dialog will turn as Yes / No / Cancel. If you click Yes, the value you enter will be added. Press No or Cancel to get back to editing the cell. No is the default button.
Information: The buttons on the alert message will be Ok and Cancel. If you click Ok (the default one), a duplicate will be added. Cancel will take you back to the editing mode.
Note: I'd like to pay your attention again to the fact that the alert about a duplicate entry will appear only when you try to enter a value into a cell. Excel will not find existing duplicates when you configure the Data Validation tool. It will not happen even if there are more than 150 dupes in your column. :).
Table of contents