Today I will tell you how to avoid duplicates in a column of data in an Excel sheet. This trick works in Microsoft Excel 2013, 2010, 2007 and earlier.
We have already touched on this topic in one of the articles. Therefore, you may already know how in Excel to make re-entered data automatically highlighted in color during the input process.
In this article, you will learn how to prevent duplicates from appearing in one or more columns in an Excel worksheet. How to make the first column of the table only contain unique data that appears only once, for example, invoice numbers, item records, or dates.
How to Prevent Duplicates in 5 Easy Steps
Excel has a tool that is often undeservedly forgotten – “Data validation“. With it, you can avoid errors that occur when entering. Later we will definitely devote a separate article to this useful tool. In the meantime, to warm up, let’s show his work on a simple example 🙂
Let’s say we have a customer information table that consists of columns with names (Name), phone numbers (Phone), and email addresses (e-mail). We require that email addresses do not repeat. The following steps will help you avoid sending emails to the same address again and again.
- We look through the table and, if necessary, delete all duplicate entries. To do this, first highlight the duplicates with a color, and then, after checking all the values, delete them manually.
- Select the entire column in which we want to avoid duplicates. To do this, click on the first cell with data and, holding down the key Shift pressed, click on the last cell. If this column is the last one in the table, as in our case, then we can use the key combination Ctrl + Shift + End. Most importantly, first select the first cell with data.
Note: If the data is formatted as a regular range, and not as a full-fledged Excel table, then you need to select all the cells of the column, including empty ones. In our example, this will be the range D2: D1048576.
- Click the Data (Data) and click on the icons Data validation (Data Validation) to bring up the dialog box Validation of input values (Data Validation).
- On the Advanced tab Parameters (Settings) in the drop down list Data type (Allow) select Other (Custom) and in a field Formula (Formula) enter the following expression:
=СЧЁТЕСЛИ($D:$D;D2)=1=COUNTIF($D:$D,D2)=1Here $D:$D are the addresses of the first and last cell in the column. Note that we have used the dollar sign to write an absolute reference. D2 is the address of the first selected cell in the column, it is not an absolute reference.
This formula counts the number of repetitions of a cell value D2 in the range D1: D1048576. If this value occurs only once in the given range, then everything is in order. If the value occurs several times, then Excel will show a message, the text of which we will write on the tab Error message (Error Alert).
Tip: We can search for duplicate values written not only in the current column, but also in another column. This column may be on a different sheet, or even in a different workbook. Thus, by entering email addresses in a column, we can compare them with addresses that are blacklisted and with which we decided to stop cooperation. I will talk more about this application of the tool “Data validation” in one of the future articles.
- Open the tab Error message (Error Alert) and fill in the fields Title (Title) и Message (Error message). This is the message that Excel will show when you try to enter a duplicate value in a column. Try to clearly explain the details of the error in your message so that you and your colleagues can understand the reason. Otherwise, after a long time, for example, after a month, you may forget what this message means. For example, like this:Title: Duplicate email.
Message: The email address you entered is already in use in this column. Only unique email addresses are allowed.
- Press OKto close the dialog Validation of input values (Data validation).
Now, when you try to enter an address in the e-mail column that already exists in it, the error message we created will be displayed. This will work either when creating an e-mail entry for a new customer or when trying to change an existing customer’s e-mail:
If our fight against duplicates allows exceptions
On step 4 in the drop down list View (Style) select A warning (Warning) or Message (Information). The behavior of the error message will change as follows:
Warning: The dialog box will offer a set of buttons Yes (Yes) / No (No) / cancellation (Cancel). If you press Yes (Yes), then the entered value will be added to the cell. To return to cell editing, click No (But) or cancellation (Cancel). The button is enabled by default. No (No).
Message: A dialog box will prompt you to click the button. OK or cancellation (Cancel). The button is enabled by default. OK – the entered repeating value will remain in the cell. If you want to change the data in a cell, click cancellation (Cancel) to return to editing.
Note: I would like to once again draw your attention to the fact that a message about the appearance of a duplicate value will be shown only when you try to enter this value into a cell. Tool “Data validation» in Excel will not detect duplicates among already entered records, even if there are hundreds of these duplicates!