Data validation must not exist in list

Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a custom validation rule when you want to *prevent* a user from entering a value in a list. To allow only values that do not exist in a list, you can use data validation with a custom formula based on the COUNTIF function. In the example shown, the data validation applied to B5:B9 is: =COUNTIF(list,B5)=0 where "list" is the named range D5:D7.