How To Avoid Duplicates Using Data Validation Tool, Drop-Down List, or Conditional Formatting Rule In Excel

In this article, I will tell you how to use the excel data validation tool,  or a drop-down list, or a conditional formatting rule to avoid duplicates data entry.

1. How To Avoid Duplicates Using Data Validation Tool, Drop-Down List, or Conditional Formatting Rule In Excel.

1.1 Use Excel Data Validation Tool.

1. You can use data validation in Excel to avoid duplicates in a specific cell or range of cells.
2. Here are the steps to set up data validation to prevent duplicates.
3. Select the cell or range of cells where you want to avoid duplicates.
4. Click on the “Data” tab in the ribbon, then select “Data Validation” from the “Data Tools” group.
5. In the “Data Validation” dialog box, select “Custom” from the “Allow” drop-down list.
6. In the “Formula” field, enter the following formula: =COUNTIF($A$1:$A$10,A1)<=1 (Note: Replace $A$1:$A$10 with the range of cells that you want to apply the data validation to.)
7. Click on the “Error Alert” tab and enter a message to appear when a user tries to enter a duplicate value.
8. Click “OK” to save the data validation settings.
9. Now, when a user tries to enter a duplicate value in the cell or range of cells, an error message will appear, and the value will not be accepted.

1.2 Use A Drop-Down List.

1. Create a drop-down list using data validation that only includes unique values.
2. This will prevent users from entering duplicate values in the cell or range of cells.
3. You can refer to the article How To Add A Blank Option In A Drop Down List In Excel.

1.3 Use A Conditional Formatting Rule.

1. Create a conditional formatting rule that highlights duplicates in the cell or range of cells.
2. This will make it easier for users to identify and avoid entering duplicate values.
3. Select the range of cells where you want to avoid duplicates.
4. Click on the “Home” tab in the ribbon, then select “Conditional Formatting” button from the “Styles” group.
5. Select the “Highlight Cells Rules” from the drop-down menu, then select “Duplicate Values” from the sub-menu.
6. It will popup the “Duplicate Values” dialog box.
7. In the “Duplicate Values” dialog box, select the color that you want to use to highlight duplicates in the second drop-down list, then click “OK“.
8. Any cells in the selected range that contain duplicate values will now be highlighted with the color that you selected.
9. By using this conditional formatting rule, you can easily identify and avoid duplicates in your Excel worksheet.
10. Note that this method only highlights duplicates and does not prevent users from entering duplicate values. You can combine this method with data validation to provide additional protection against duplicates.