See all How-To Articles

How to Prevent Duplicate Entries in Excel & Google Sheets

This tutorial demonstrates how to prevent duplicate entries in Excel and Google Sheets.

 

prevent duplicate entries data validation final

 

Prevent Duplicate Entries

If you want to allow only unique values in a column, use the data validation functionality in Excel. This way, you can prevent a user from entering duplicate values. Say you have the following list of names in Column B.

 

prevent duplicate entries initial data

 

To prevent duplicate entries in a range, follow these steps:

  1. Select the range for data validation (e.g., B2:B10), and in the Ribbon, go to Data > Data Validation.

 

prevent duplicate entries data validation

 

  1. In the Data Validation window, (1) select Custom as the validation criteria under Allow, and (2) enter the COUNTIF formula:
=COUNTIF($B$2:$B$10,B2)=1

This formula counts – for each entry – how many times that value appears in the range B2:B10. If the result is 1 (meaning the entry appears only once in the range) then it is allowed. Otherwise, the data validation rule prevents a user from entering that value, since it is already in the range.
Then (3) go to the Input Message tab.

 

prevent duplicate entries data validation 1

 

  1. In the Input Message tab, enter (1) the Title of the message you’re adding and (2) the Input message. This message appears when a user selects a cell in the data validation range to provide information about the data validation rule.
    Then (3) go to the Error Alert tab.

 

prevent duplicate entries data validation 2

 

  1. In the Error Alert tab, enter (1) the Title of the error message and (2) the Error message. This message appears if a user enters an existing value and prevent them from entering duplicate values.
    Then (3) click OK.

 

prevent duplicate entries data validation 3

 

Now selecting an empty cell from the data validation range (for example, B9) prompts an input message about data validation.

 

prevent duplicate entries entry warning

 

If you try to enter an existing value in cell B9 (e.g., Michael), you get the error message pictured below, stopping you from entering a duplicate value.

 

prevent duplicate entries data validation final

 

Note: To remove duplicate values or duplicate rows that were already present in the data, see How to Remove Duplicate Cells or this VBA tutorial.

Prevent Duplicate Entries in Google Sheets

To prevent duplicate entries in Google Sheets, follow these steps.

  1. Select the range for data validation (e.g., B2:B10), and in the Menu, go to Data > Data validation.

 

google sheets prevent duplicate entries data validation

 

  1. In the Data validation window, (1) enter Custom formula is as the Criteria, and (2) enter the formula:
=COUNTIF($B$2:$B$10,B2)=1

Then (3) select Reject input, (4) check Show validation help text, and (5) enter an error message. (6) Click Save.

 

google sheets prevent duplicate entries data validation 1

 

Now, if you try to enter a value in cell B9 that already exists in the range, you get the error message you just set.

 

google sheets prevent duplicate entries data validation 2