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.
1. Select the entire column where you need to avoid duplicates. Click on the first cell with data keeping the Shift keyboard button pressed and then select the last cell. Or simply use the combination of Ctrl + Shift + End. It is important to select the 1st data cell first.
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
Go to Excel “Data” tab and click on the Data Validation icon to open the dialog box.
3. On the Settings tab, choose “Custom” from the Allow drop down list and enter=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.
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.
4. Switch to the “Error alert” tab, and enter your text into the fields Title and Error message. Excel will show you this text as soon as you try to enter a duplicate entry into the column. Try to type the details that will be accurate and clear for you or your colleagues. Otherwise, in a month or so you can forget what it means.
Title: “Duplicate email entry”
Message: “You have entered an email address that already exists in this column. Only unique emails are allowed.”