Suppose you have a spreadsheet where you are entering a list of unique values, such as invoice numbers, customer names, etc. You don’t want to duplicate any of the data, as this would cause problems down the road, but at the same time the list might be too large for you to manually verify that no duplicate values have been entered.
You can use Data Validation to ensure that no duplicate values can be entered into your list. If a user accidentally enters a duplicate, an error message will pop-up telling them that they have entered an invalid value.
To do this, follow the steps below.
- Select the column where you want your list of unique values to appear (for illustration purposes we’ll assume it is column A).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: Custom.
- In the Formula box, type the following formula: =MATCH(A1,$A:$A,0)=ROW(A1) (if you’re using this in a different column than column A, change the “A1” and “$A”‘s in the formula to whichever column you’re using)
- Click OK.
Now begin to type your list of unique values. If you should happen to accidentally enter one twice, you’ll see the following error message pop-up:
You can click “Retry” to re-enter a value in the same cell, or “Cancel” to delete the duplicate value.
**UPDATE**
To have a “one click” availability to this tip, use the following macro code. Again, like the formula above, change the “A1” and “$A”‘s to whichever column you’re using.