Now, let me tell you what you need to change in the above formula so that it will work with your data set. Once you put this formula into cell B2 using Ctrl + Shift + Enter, copy the formula down the column and you will get this result:Īnd BAM now we have a pretty list without any blanks! That is because this is an Array Formula, which just means that it is very powerful/a pain in the butt. You MUST enter the formula into cell B2 in this way, instead of just hitting the Enter key, or it will not work. We put this formula into cell B2 and use Ctrl + Shift + Enter to put it into the cell. Then we need to use the below formula to generate the new list: This is the most complex and annoying way to create the list without blanks but it allows you to not have to touch the original list (although you could also achieve that by copy/pasting the original list for the previous two examples). Now that all blanks are gone, we can create the Data Validation dropdown list without any blanks.

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