If you are maintaining a data, you may require to ensure a field having unique entries only. Unique Entries, in excel terms, are those entries which are not repeated in a given field; i.e., they have got no duplicates. Today, we will learn, how to restrict duplicate entry in a data field in Excel

One option is to highlight the cells containing duplicate values, and this can be done very easily using conditional formatting. For this select the field data and go to:

HOME > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values

In the opened Pop-up window, choose the predefined colors to fill duplicate cells (you may go for custom format and can select the format of your choice). By doing this, though you will get the information as to whether duplicate values exist in a particular field or not, you won’t be able to stop user to make/fill duplicate entries

To restrict any given user from entering duplicate value, you may use “Data Validation” feature of Excel. To apply “Data Validation” first select the range of the field, where you want to apply validation. Suppose, cell “B1” contains the name of the field and we have to apply “Data Validation” in range “B2:B26”. First of all select the range “B2:B26” with “B2” as active cell then go to:

DATA > Data Tools > Data Validation > Data Validation….

A new “Data Validation” window will open as shown below:

Select “Custom” from the drop-down in “Allow” window and update below formula in the space provided for formula:

=COUNTIF($B$2:B2,B2)=1

You can also update the customized help message in “Input Message” tab, which will appear whenever cells with “Data Validation” are selected

In “Error Alert” tab you have option to customize the alert message to be displayed, if someone enter duplicate value. If you don’t update Excel will display default error message

You can see, Excel’s default error message in below picture as user is trying to update duplicate entry. A customized help message for the users updating data is also appearing

Both the tricks explained above have a major glitch, if a user copy the data from some other part of worksheet, or from other workbook, where you have not applied conditional formatting (or data validation), and paste the same to area with conditional formatting (or data validation), the tricks will not work. System will accept duplicate value

Simplest solution is not to allow the user to paste format, allow him/her to paste values only. You can also track the action of user, and if s/he tries to paste the data in “Data Validation” range, undo the same with a message to him/her. However, to implement these solutions you have to use VBA (Macros) but covering the same here is not possible as the same is the subject of some other article