Stop duplicate names, email addresses or product SKU’s from being added to an Excel list or Table. Adding more rows to an Excel Tables can be done while preventing accidental duplicates creeping into the list.
Excel Tables or lists can grow to hundreds or thousands of rows, there’s a risk of someone entering a duplicate entry without bothering to check if the person, event or product is already in the list.
We’ll look at ways to stop duplicates being entered with a spot of real-time data entry validation.
Stop Duplicates in a single column
Let’s start with a simple example with a list of first names.
To make sure there isn’t another person with the first name added to the list follow these steps.
- Select the data cells. Start with the first data cell in the column (A2 in this case) and extend the selection to the end of the table or the entire column if not using a table. Selecting from the first cell is important.
- Go to Data | Data Tools | Data Validation.
- Allow: select ‘Custom’ then Ignore blank.
- Enter the formula =COUNTIF($A:$A,A2)=1 . Change the A references to the column you’re using. We’ll explain that code in a moment.
- Select ‘Apply these changes to all other cells with the same settings’.
- Click OK to apply the data validation.
Now when we enter the next name/row we’ll get an error message for any duplicate. Anyone up on their moon landing history knows there were two Alan’s to step onto the lunar surface in a row (Alan Bean, LMP on Apollo 12 then Alan Shepard, Commander on Apollo 14).
We’ll explain how to add a specific error message below.
Inside the duplicate checking formula
Let’s look at the formula that checks for duplicates. Understanding the formula will let you change it to suit other situations.
CountIF() – counts the number of times a condition or IF test occurs.
$A:$A – the range to search in for a match. In this case, the entire Column A. The $ makes it an absolute reference that doesn’t change.
A2 – the cell to check against the search range (first parameter). This changes for each cell down the column, a relative reference.
= 1 – if CountIF finds a match it returns the number of matches. 0 means no matches. 1 = one match.
Since the list should not have duplicates testing for =1 should be enough since CountIF should never return 2 or more (meaning multiple duplicates).
Custom Error message for duplicates
Excel’s default data validation message isn’t a lot of help.
Better to setup a custom message so the user understands the problem.
Go back to Data | Data Tools | Data Validation and choose the Error Alert tab.
Show error alert after invalid data is entered
Style: Stop – prevents the invalid data from getting into the cell.
Title: a short explanation
Error Message: a longer explanation.
Custom Input message
Data | Data Tools | Data Validation | Input Message lets you add a tooltip box. That can give the user some notice before they hit the error message.