This post will guide you how to prevent duplicate entries in one or multiple columns in Excel. How do I use Data Validation feature to prevent duplicate values in columns in Excel.

Prevent Duplicate Values

---

If you want to prevent duplicate values when entering data into your cells or columns in Excel, you can use the Excel’s Data Validation feature to reject duplicate entries.

Assuming that you want to prevent duplicate values in range B1:B4 when entering values into cells, you just do the following steps:

#1 select the range of cells in which you want to prevent duplicate values. Such as: B1:B4

#2 go to DATA tab, click Data Validation command under Data Tools group. And select Data Validation menu from the drop down menu list. And the Data Validation dialog will open.

#3 click the Settings tab in the Data Validation dialog, choose Custom from the Allow list box, and then enter the following formula into the formula box. And click Ok button.

=COUNTIF($B$1:$B$4,B1)=1

 

#4 click the Error Alert tab, enter the text “Duplicate Value” in the Title text box. And enter a meaning full description in the Error message text box. Such as: “The value is a duplicate value, and it is not valid”. Click Ok button.

#5 you can enter a duplicate value in range B1:B4, the value will be rejected. And when it happens, just click Cancel button to clear the error message and re-enter a valid value.