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.
- You can use data validation in Excel to avoid duplicates in a specific cell or range of cells.
- Here are the steps to set up data validation to prevent duplicates.
- Select the cell or range of cells where you want to avoid duplicates.
- Click on the “Data” tab in the ribbon, then select “Data Validation” from the “Data Tools” group.
- In the “Data Validation” dialog box, select “Custom” from the “Allow” drop-down list.
- 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.)
- Click on the “Error Alert” tab and enter a message to appear when a user tries to enter a duplicate value.
- Click “OK” to save the data validation settings.
- 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.
- Create a drop-down list using data validation that only includes unique values.
- This will prevent users from entering duplicate values in the cell or range of cells.
- 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.
- Create a conditional formatting rule that highlights duplicates in the cell or range of cells.
- This will make it easier for users to identify and avoid entering duplicate values.
- Select the range of cells where you want to avoid duplicates.
- Click on the “Home” tab in the ribbon, then select “Conditional Formatting” button from the “Styles” group.
- Select the “Highlight Cells Rules” from the drop-down menu, then select “Duplicate Values” from the sub-menu.
- It will popup the “Duplicate Values” dialog box.
- 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“.
- Any cells in the selected range that contain duplicate values will now be highlighted with the color that you selected.
- By using this conditional formatting rule, you can easily identify and avoid duplicates in your Excel worksheet.
- 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.