Ways to avoid Excel repeating data entry

The employee ID number should be unique when making the employee roster, and we can prevent duplicate input through "Data validation":

Select the range of cells (such as D2 to D101) where you want to enter your ID number. Execute the data → effectivity command, open the Data Validation dialog box, under the Settings tab, press the Drop-down button to the right of allow, and then, on the shortcut menu that pops up, select the Custom option, and then in the following formula Enter formula in box: =countif (D:D,D2) = 1, OK return.

When you enter a duplicate ID number in the cell above, a prompt dialog box (Figure 1) pops up and rejects the entered number.

Figure 1

Note: To allow the input ID number to be displayed correctly, set the cell to text format.