Drop Down List Defaults to Middle or Bottom?

One of the quirks in Excel that we have come across is when creating a Drop Down Data Validation List ("Drop Down DV List") the listing does not start at the top but can start in the middle or even at the end of the list.

This occurs when there are blank lines included in the list, whether they have been included to separate out different parts of the list or have been included to allow new names to be added in the future.

The problem occurs even when the Ignore blank check box is ticked because Excel tries to match the contents of the cell you are using the Drop Down DV List from with the equivalent contents in the list itself.

More often than not the selection will be made from a blank cell in your spreadsheet and hence the problem when there are blank cells in the list itself.

The two lists below include certain Sage 50 account names and to assist the user the most commonly used names have been listed first with a space to separate the account names in the list.

To workaround the probblem all you have to do is set up the list with a 'space' included on the blank line, therefore Excel won't see the row as blank (as it has a space in it!) and the list starts at the top.

This list is exactly the same, however without a 'space' included in the blank line. When the list is selected Excel defaults to the same contents that are in the cell the list was selected from, in this case a blank.

Therefore, it's as simple as just adding a space to a blank cell in your list that enables you to separate out lists and ensures that when you select the Drop Down DV List that it defaults to the start of the list.