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:
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:
- Excel Dropdown Lists Tutorial
- How to Modify Cell Movement
- Filtering and Sorting with Excel
- Split Text into Different Cells
- Highlight Rows in Excel
View this offer while you wait!