Don't display empty values in Data Validation


Hello everyone.


I have attached the sheet where I have the problem I am about to present to you.


In cell "G1" I entered a "Data Validation" list of the range "$D$2:$D$500".

However, from cell "D9" to cell "D500" the value of these is empty text value (that is: ""); therefore, when I click on the arrow of the data validation drop-down menu, I also see the cells with the value "", and therefore empty (see blue line in the drop-down menu).


What can I do, without using Macros and VBA, to not include all those cells with value "" in the data validation?


Thank you!



10 Replies


That's only to wait for the update. Excel for web ignores empty cells in data validation list, hope it comes to desktop version in a while.

Thanks for the reply.
I solved.


Good to know. It will be great if you share with the community how did you solve.

I solved the problem by inserting the following formula in the Data Validation:

Thanks for the reply, your type was great. Have fun Tmas
You could also have just used a named list to only include the values for the validation list and this can be extended easily when new values are to be included for validation




An alternative could be this formula.

You're welcome
Thanks for this alternative
Nice formula, thank you!