Easily learn how to prevent duplicates in Excel simply by facilitating an existing tool already in Excel. By using this feature, you will quickly be alerted to duplicate entries within a spreadsheet. You can organize these alerts by columns, rows, and even customize the message alert so you know exactly what to change. This is especially useful for large spreadsheets and importing massive amounts of data at once.
How to Prevent Duplicate Entries in Excel
Download the example spreadsheet to follow along and learn the process. The steps don’t take long and only one formula is used.
In the spreadsheet, we’ll be using column A to detect duplicate entries. Highlight cells A3 to A12, which will be examined.
Go to the top of the Excel program and select the tab labeled “Data”. After you’ve selected that tab, choose the “Data Validation” option and select “Data Validation” from the drop down that appears.
In the pop-up from this, you will defaulted to the “Settings” tab. Now, enter the following:
Allow: Custom
Formula: countif($a$3,$a$12, a3)=1
It should look just like this:
Move over to the “Error Alert” tab in the same pop-up and click it. Here, you will enter the following information to trigger your desired error message:
- “Show error alert after invalid data is entered” should be checked
- Choose Style: Stop
- Title: Duplicate Value
- Error Message: This value has already been entered in this sheet
Click OK. and go to cell A12. Enter “1151”. Your error message should pop up, showing you that 1151 already exists in column A’s selected fields.
Tips for Success: Edit your error message to fit whatever you want to be alerted to. This will help you stay organized.
Check this out while you wait!