To make data entry easier, you can create a drop down list of items in a worksheet cell. Then, instead of typing a product name in an order list, you can select a valid product name from the list.
However, in some worksheets, when you click the arrow to open a drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list. That can be annoying if you have to scroll back to the top of a long list, where you have the put the most popular items.
Why It Happens
Why does this happen, and how can you prevent it?
In the example shown above, the drop down list is based on a range named Products. The person who set up the list left a few blank cells at the end, where new items could be added.
This might seem like a good idea when you’re setting up a list, but it causes problems.
When the drop down list opens, it tries to match whatever is currently in the cell. In cell D3, Pencils was previously selected, and the list opens to Pencils.
If there’s a blank cell in the source list, and the cell with the data validation list is blank, the list will open with the matching blank entry selected.
Also, if you have any blank cells in the list, people might be able to enter invalid items in the data validation cells.
Prevent the Problem
To prevent this problem, you can remove the blanks from the source list, by using a dynamic range, which will adjust automatically when items are added or removed.
In this example, the OFFSET formula is:
It counts the items in column B of the Prices sheet, where the Products are listed. The heading, in cell B1, is not included in the list.
Once the dynamic range is created, the blanks are not included in the Products list, and the drop down works correctly.
Download the Sample File
To download the sample file with a dynamic range, please visit the Data Validation Tips & Quirks page, at this link: Drop Down List Opens with Blank Selected
Watch the Dynamic Range Video
To see the steps for setting up a dynamic named range for the product list, please watch this short video tutorial.