This article shows you how to prevent a user in Excel from entering the same thing more than once within a range of cells. This allows you to ensure that a range of values is full of completely unique values.
In order to do this, you will need to use data validation along with a custom Excel formula. Data validation is a feature of Excel that allows you to limit what a user can and can’t enter into a cell in Excel. This also allows you to output a custom warning message and a custom input message that tells the user what to input into the cell; these aspects of Data Validation will be covered in another article.
Before you use the Data Validation feature, let’s make sure that the custom formula is correct. This formula will require the COUNTIF() function. This function allows you to count how many times an item occurs within a range of cells. This is what will tell Data validation if there is a duplicate value in the range. This is a pretty basic function; let’s assume the range for which we want only unique values is A1:A10. To count the duplicates, we input this function:
=COUNTIF($A$1:$A$10,A1)
Note that the first argument for this function is just the range of cells that you want to contain only unique values and that the second argument is the first cell in that range. Also, make sure to set the range with absolute cell references (the dollar signs) and to set the second argument as relative (no dollar signs).
This function will tell you how many times the value in cell A1 appears in the range A1:A10. But, in order to use this in Data Validation, we need the formula to output a TRUE or a FALSE value. In this case, we want to prevent duplicates; this means that a value can appear no more than once within the list. Therefore, we want to make sure that the COUNTIF() function does not return a value greater than one, or, if it does, we want Excel to prevent the user from entering that value. To do this, we need to make the formula a logical test, like this:
=COUNTIF($A$1:$A$10,A1)<=1
The only difference between this formula and the formula above it is the addition of “<=1” to the end of the COUNTIF() function, which stands for “less than or equal to 1.” This will force the formula to output a TRUE if the COUNTIF() function returns a value equal to or less than 1, otherwise, it will return FALSE.
Now, we can add this formula to the Data Validation in order to prevent duplicate entries into the range A1:A10. Select the range A1:A10, hit Alt + D + L to get to the Data Validation window; go to the settings tab, select “Custom” from the Allow field, and enter the custom formula into the field that appears. Hit OK. Now, everything should work and you should see an error message if you try to input the same value twice into the range A1:A10 within the Excel spreadsheet.
