Recently a client was complaining that the drop down list in Excel was not showing on a file we sent them. After some investigation we discovered that they had simply not scrolled up on the list. This is an easy error to make given how Excel handles drop down lists when you have blanks in them.
In the YouTube video we also cover why there is no drop down list at all.
YouTube Drop down list not showing or above
Dropdown list starts at the bottom of the list
In the below image you will see that we have set up a drop down list and included some spaces (blue cells) for other names. However, when you click on the drop down cell it automatically goes to the first blank as that is what is initially shown in the cell. Notice that there is space in the scroll bar to scroll up. So the names are all in the drop down but they are higher up.
To avoid this problem there are two possibilities. The most correct would be to set up the drop down list in a Table format that will automatically grow as you add more names (so you do not need the blank cells shown above).
If you can’t/ don’t want to use the table (and there can be some valid reasons why), you can make sure that the first item in the drop down list is a blank cell.
Notice that now we have a blank cell in the first item and when we click on the drop down list it shows the first blank which is in the first position so that you can see the other options.
Dropdown list completely missing
If there is nothing in the dropdown list, it is most likely that it has been accidentally deleted or is on another spreadsheet. Check in the Data Validation settings to see where Excel is looking for the list.