Prevent Duplicate Entries in Excel

When doing data entry work in Excel, you may want to prevent entering duplicate entries in a column or a range of cells.

Thankfully, there is a feature in Excel that allows you to do precisely this – and it’s called Data Validation.

In this article, I will show you how to prevent duplicate entries in Excel using Data Validation. I will also cover a method that instantly highlights duplicate entries using Conditional Formatting.

Prevent Duplicate Entries Using Data Validation

Below, I have a data set where I am entering names in column A, and I want Excel to prevent me from entering a duplicate name in the column:

Below are the steps to do this:

1. Select the range of cells in which you want to prevent the duplicate entry. In this example, I will select the entire column A.

2. Click the Data tab.
3. In the Data Tools group, click on the Data Validation icon. This will open the Data Validation dialog box.

4. In the Settings tab, click the Allow drop-down and select the Custom option.

5. In the formula field, enter the below formula.

=COUNTIF(A:A,A1)=1

In the above formula, I have used A:A as the first argument of the COUNTIF function, but you can use any range on which you want to apply this data validation rule.

6. Click the ok button.

Now, when you are doing a data entry in column A, and you repeat a name (or any text string that has already been entered in column A), it will show you an error prompt, as shown below.

How does this work?

Whenever you enter anything in any cell in column A, it checks whether it satisfies the formula we used in data validation.

The formula =COUNTIF(A:A,A1)=1 checks the content of the cell and counts whether the same also appears in any other cell or not.

If the cell content is unique, our formula would return TRUE, and Data Validation would allow you to make that entry in the cell. If more than one instance of that name appears in the column, our formula will return FALSE, and Data Validation will not allow us to enter that value by showing us the Error Prompt.

Customizing the Error Alert Dialog Box

You can customize the error dialog box to make it more useful for the user.

By default, the title of the dialog box says Microsoft Excel, but you can change it and make it something more meaningful such as ‘Duplicate Names Not Allowed’.

Here is how to do this:

1. Open the Data Validation dialog box (Data tab –> Data Tools Group –> Data Validation).
2. Set the Data Validation rule as covered in the previous section.
3. Click the Error Alert tab

4. Enter the customized title and error message that you want to show on the Error Alert Dialog Box.

Now, when you make a duplicate entry in the column, you will see a dialog box, as shown below.

Note: If you copy a cell and paste it over the cells where data validation rules have been applied, it will remove the data validation rules. So this method would only work if you are manually entering the data.

Also read: How to Create a Data Entry Form in Excel

Prevent Duplicate Entries By Highlighting Them

Another method that can be helpful in preventing duplicate entries is by highlighting the duplicate entry in a different color as soon as it is entered.

This can easily be done using Conditional Formatting.

Let me show you how it works.

Below, I have a dataset where I’m entering names and column A, and I want to prevent duplicate name entries in the column.

Here are the steps to highlight duplicate entries as soon as you type it:

1. Select the column or range of cells in which you want to prevent duplicate entries. In this example, I’m going to select the entire column A.
2. Click the Home tab.
3. Click on the Conditional Formatting option.

4. Go to the Highlight Cell Rules option, and in the additional options that show up, select the Duplicate Values option.

5. This opens the Duplicate Values dialog box, where you can specify the formatting to be applied to cells that contain duplicate values. In this example, I will go with the default Light Red Fill with Dark Red Text. You can choose from any other pre-set formatting or create your own by clicking on the drop-down and then selecting Custom Format.

6. Click OK

That’s it!

Now, as soon as you enter a name that is already there in column A, it will instantly highlight the cell in the specified format.

Also read: How to Highlight Blank Cells in Excel

So, these are two methods you can use to prevent duplicate entries in a column in Excel.

You can use Data Validation to stop the user from entering a duplicate entry by showing an error prompt or use Conditional Formatting that allows you to enter a duplicate value but highlights it so that you know it is a repeat of an existing value.

I hope you found this article helpful. If you have any feedback or suggestions for me, do let me know in the comments section.

Other Excel articles you may also like:

• Remove Formulas (but Keep Data) in Excel
• Select Till End of Data in a Column in Excel
• Conditional Data Entry using Data Validation
• Enable Data Entry in a Cell in Excel only if a Dependent Cell is Filled
• Quickly Enter Data in Excel in a Specific Order in Non-Contiguous Cells