Restrict duplicate entries while entering new values in Excel

Sometime you don’t want duplicate entries in your list which you prepare in Excel. There is easy fix to restrict duplicate entries while entering new values. Just follow up below process and you will see error occurred when you try to enter duplicate values in range.

Lets Start -

First Go to Data tab and select first option Data Validation from Data validation menu.

Then below Window will open.

In Allow option select dropdown and choose Custom and enter CountIf formula in Formula Field.

Countif formula should be like as per above. It should include Range where you want to enter data with fixed Reference by putting $ sign in Cell Reference. Shortcut for lock Range is select Range and press F4 Key. Then also put “ =1 ” to enter value in Range only once.

Then click Ok.

After that Drag that Cell till Last Cell in Range. Here I have dragged D5 till D20.

Now you can see below error when you try to enter same value again in Range.

Note – Data Validation will not work if you copy paste value from other location.

You can customize above error message. To customize your own error message go to Data Validation and select Error Alert tab.

Enter Title and Error message what you want to display while entering duplicate values.

Hope you understand how to restrict duplicate entries in Range.

Thank you

Watch Video -

Have a nice day….Enjoy Excelling…