Preventing Duplicates in Excel

Preventing Duplicates in Excel

Preventing duplicates in Excel spreadsheets is a valuable skill to have. It will help you organize massive amounts of information and avoid entries that are inputted more than once. This process is best utilized when entering information of employees, group members, consumers, clients, and any large collection of people.

How to prevent duplicates in Excel

Download our example to use as a practice sheet and follow along with the instructions.

Once you’ve opened the spreadsheet, select the range that you want to check for duplicates. In this sheet, choose the first column, rows A3 through A12.

Next, move to the “Data” tab at the top of the program. Click the “Data Validation” option, and select Data Validation in the dropdown menu.

The “Settings” tab in the box has the option to enter the following:

Allow: Custom

Formula: countif($a$3,$a$12, a3)=1

It will look like this:

Prevent Duplicates in Excel 1


Move over to the “Error Alert” tab within this same box and do the following:

  • Check mark “Show error alert after invalid data is entered”
  • Choose Style: Stop
  • Title: Duplicate Value
  • Error Message: This value has already been entered in this sheet.

Confirm with the OK button. To ensure that your duplicate-prevention process worked, go to cell A12 and enter “1151”. If the error message pops up, then you’ve completed your task.

Related Templates:

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.