Stop accidental duplicates in your Excel lists or Tables

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

image 133 - Stop accidental duplicates in your Excel lists or Tables

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.
image 134 - Stop accidental duplicates in your Excel lists or Tables
  • 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.
image 136 426x296 - Stop accidental duplicates in your Excel lists or Tables
  • 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).

image 137 473x283 - Stop accidental duplicates in your Excel lists or Tables

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.

image 138 - Stop accidental duplicates in your Excel lists or Tables

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.

image 139 431x296 - Stop accidental duplicates in your Excel lists or Tables

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.

image 140 473x198 - Stop accidental duplicates in your Excel lists or Tables

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

subs profile e1563205311409 - Stop accidental duplicates in your Excel lists or Tables
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address