Now, right-click a selected cell, which will be a blank one, and click Delete. You will see only the empty cells have been selected:

In Excel 2007 and later go to the Home tab and look to the right and click the Find & Select button and then you can see this option.įrom the window that opens, select the Blanks option and hit OK. This method will delete all of the blanks in a list and it is very easy to use. Note that I recreated the Data Validation list after the sorting, otherwise there would be two blanks at the bottom of the list. Now, all the blanks are at the bottom and it's easy to create a list without blanks: The sorting method is quite simple and does not require a new list of data.įirst, select the data from which we want to remove blanks: All 3 methods are multi-step, but there is no way around this.ģ ways to Remove Blanks from a Data Validation Dropdown Menu ListĬomplex Formula (Older Excel Versions) (complex)Įxcel 365 Dynamic Array Formula to Remove Blanks (quite easy)

I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Excel.

Remove drop down menu in excel 2016