How to Prevent Duplicate Entries in Excel

This guide will discuss how to prevent duplicate entries in Excel using the data validation feature

Excel is a popular tool for many tasks such as making reports, calculations, and inputting data. When we input a large amount of data in Excel, there can be times when our data would be duplicated. And it would be difficult to spot or identify duplicated data entries, especially in a large data set. 

Although duplicated data entries can be fine in other situations, most of the time these duplicated values can mess up or ruin our data set. But, worry not! It is quite easy to prevent duplicate entries in Excel using the data validation tool. 

Furthermore, we can also easily spot or identify duplicate values using conditional formatting. And we can quickly remove duplicate values in Excel if they are already present. 

Let’s take an example wherein we need to prevent duplicate entries in our data set.

Suppose you are encoding the employee ID for each employee in the directory. Because you are manually assigning a series of numbers to serve as an employee ID, there can be times you miscalculate and input a duplicate series. 

To prevent this, you can utilize the data validation tool in Excel to provide a warning message that you have already inputted a specific series of numbers. 

Great! Now let’s move on and discuss a real example of how to prevent duplicate entries in Excel using the data validation tool.

A Real Example of Preventing Duplicate Entries in Excel

Let’s say we manually assign a series of numbers to each employee to serve as their employee ID in the company directory. So our initial data set would look something like this:

Initial data set

 

Since we would need to create multiple employee IDs, it would be difficult to keep track of what series of numbers we have already given. So to prevent multiple entries of the employee ID, we will utilize the data validation tool. 

Furthermore, we can also create a customized warning message whenever we input a duplicate entry or a series of numbers that were already given.  

But, what if we are working with a data set that already has duplicate entries? 

Worry not! Excel makes it easy for us to identify and remove duplicated values. In this case, we can utilize conditional formatting to highlight the duplicate values in the data set. Afterward, we can remove the highlighted cells by using the remove duplicates feature in the data tab of Excel.

Finally, we can input our entries without worrying about duplicate values. So we would get a message like this if we ever input a duplicate value:

Error Alert

 

You can make your own copy of the spreadsheet above using the link attached below. 

Awesome! Now we can move on and learn the steps of how to prevent duplicate entries in Excel using the data validation tool.

How to Prevent Duplicate Entries in Excel

In this section, we will explain the step-by-step process of how to prevent duplicate entries in Excel using the data validation tool. Additionally, we will also explain how to identify and remove duplicate entries already present.

1. Firstly, we must select the column or range where we will input the data validation. In this case, we will select the range A5:A10 because this is where we will input the employee ID. Then, go to the Data tab and select Data Validation.

Going to the Data Validation tool

 

2. Secondly, the Data Validation window will open. In the Settings tab, select Custom in the Allow dropdown menu. Next, input the formula “=COUNTIF($A$5:$A$10,A5)=1”. Lastly, click OK to apply the data validation.

Essentially, the custom formula will count each data inputted in the column and ensure that each entry only appears once. And now, Excel will display an error alert whenever we input a duplicate entry.

Prevent Duplicate Entries in Excel

 

3. Thirdly, we can add a warning message and error alert that Excel will show. To do this, simply go back to the Data tab and select Data Validation.

Data Validation tool

 

4. So, let’s first add the warning message. Then, go to the Input Message tab in the Data Validation window. In this case, let’s input “Warning!” as the Title. And we can write a message that says “This is a duplicate value.” in the Input message.

Prevent Duplicate Entries in Excel

 

5. Next, let’s add an error alert. So, go to the Error Alert tab. Then, we can input any title and error message we want. In this case, let’s type in “Duplicate Entry” in the Title

And we will input “This is a duplicate entry. Please input a new value.” in the Error message. Additionally, we can choose the type of error alert. In this case, we will choose Blocking. Lastly, click OK to apply the changes.

Prevent Duplicate Entries in Excel

 

6. And tada! We have successfully prevented duplicate entries in Excel. Now, whenever we input a duplicate entry, Excel will display a warning message and error alerts.

Prevent Duplicate Entries in Excel

 

7. Additionally, let’s learn how to spot and remove duplicate entries if they are already present in the data set. Firstly, select the cells where we will check for duplicates. Then, go to the Home tab and select Conditional Formatting. Next, select Highlight Cells Rules and click Duplicate Values.

Prevent Duplicate Entries in Excel

 

8. Once the duplicate entries are highlighted, let’s proceed to remove them. So go to the Data tab and click on Remove Duplicates.

Prevent Duplicate Entries in Excel

 

9. Then, the Remove Duplicates window will appear. Since we only have one column and the duplicate entries are in that column, check Employee ID. And check My data has headers. Lastly, click OK to remove the duplicates.

Prevent Duplicate Entries in Excel

 

10. And tada! We have successfully removed duplicate entries in Excel.

Prevent Duplicate Entries in Excel

 

And that’s pretty much it! We have discussed how to prevent duplicate entries in Excel using the data validation tool. Furthermore, we explained how to add a warning message and error alert to prevent duplicate entries. 

And we discussed how to identify and remove duplicate values using conditional formatting. Now you can apply this in your work.  

Are you interested in learning more about what Excel can do? You can now use the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

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

You May Also Like