Prevent Duplicate Entries in Excel

You can use the data validation tool to prevent a user from entering duplicate values in Excel. To prevent duplicate entries in an Excel spreadsheet, follow the steps below.

1. Select the cell range B3:B15.

2. From the Data tab, click “Data Validation”.

3. From the Settings tab of the Data Validation dialog box, select Custom and then type in a function in the formula box “=COUNTIF($B$3:$B$15,B3)=1”.

This function finds if there is any duplicate entry each time a user enters a number.

4. Click the Error Alert tab. . Type in the title as “Duplicate Employee ID” and the Error message as “This employee ID has already been entered. Please try another ID”.

5. Click OK.

6. Select cell B10. Try to enter a value that has already been entered. For instance, we enter 115 in cell B10.

7. Press Enter.

Result: Excel shows the warning message for the user as you have set.

Note: To remove all the data validation rules, select the cell range and then click Data tab -> Data Validation -> Clear All -> OK.

4 of 8 finished! Recommending more on Data Validation: Next Example >>

<< Previous Example | Skip to Next Chapter 09: Using Templates