This post explains how you can prevent of duplicate entries in Microsoft Excel and how it can be used to avoid redundancy of data in the worksheet.
How to Prevent Duplicate Entries in Microsoft Excel ?
In Microsoft Excel, you can validate data to allow unique entries and disallow duplicates using the custom data validation feature.
- Select the range of cells that should not contain any duplicates.
- Select Data Tab -> Data Tools -> Data Validation.
- Click Custom from Allow section and enter the formula in the formula box.
The formula used to identify duplicates :
=COUNTIF(range, topmost_cell)<=1
For example, if you want a unique entry of values in column A2 to A10, then create a custom rule with this data validation formula:
=COUNTIF($A$2:$A$10, A2)<=1
So here excel allows only unique values in the specified range and when redundancy occurs it shows an error.
