Stop accidental duplicates in your Excel lists or Tables

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.

  1. 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.
  2. 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($A:$A, A2)=1  

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.

America has two feet … does Excel?
What are Excel Tables and why you should use them
Excel Table Cards for smartphones
Solved – the problem with Excel Tables and Transpose

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.
Invalid email address