Excel Drop Down Opens At End

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.

dropdownlistblank05

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.

dropdownlistblank06

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.

dropdownlistblank02

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.

dropdownlistblank08

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.

dropdownlistblank06

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.

dropdownlistblank07

In this example, the OFFSET formula is:

=OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)

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.

dropdownlistblank09

Once the dynamic range is created, the blanks are not included in the Products list, and the drop down works correctly.

dropdownlistblank05

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.

______________________

You may also like...

17 Responses

  1. Stan says:

    Debra:
    This will make the list no longer editable in the cell will it? I have the problem that you mention with blank selection in the list, but I need the selection to be editable. Is there any way to deal with this?
    Thanks

  2. jp says:

    David

    I liked the idea of using a Table as the source for a list … but Tables don’t seem to like Array Formulas … which I use to create some of my List Data …. any alternative solutions?

    Cheers
    James

  3. Ajit kumar says:

    Good Advice

  4. mike says:

    Works Perfect.. Thank you very much

  5. Chau Nguyen says:

    1. Create a ONE-COLUMN table
    2. Name the ONLY column of that table
    3. Refer to that NAME in your validation formula

    Results:
    Tada, you have a self-updating validation list (the table automatically expend if you add data to the next empty row adjacent to the table) without any blank cells at the end.

  6. Ray says:

    I know this post is older, but if someone happens to have this issue and reads the comments . . .

    NOTE that you can simply add a space as the first entry in your list and your list will begin with the FIRST match to your field. If the field is blank, then it will start at the top of your list.

    SIMPLE!

  7. BM says:

    If you put any character in the blank cells at the bottom of the list, for example a period or a single space, the list will also display correctly…

  8. David says:

    This works perfect for me! In the past I have had to control the drop down list length using VBA, this is so much better :)
    “=OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)”

  9. Sheila says:

    This worked great for some of my data validation lists. However, I found it isn’t working in one particular situation. In one column, I have a list of equipment (uses data validation drop downs) In another column, I have drop downs for unit numbers based on the value in the first column (i.e. if Pump is selected in the first column, the unit # drop down will show the numbers from the pump list) This is done using an INDIRECT function. This works fine except for the ranges where I used the OFFSET as shown above. I only used it on some of the longer lists since the blanks weren’t a problem for the shorter ones.
    When I used the INDIRECT in another cell, just to see what it was doing, it gives a #REF error.

    Sorry for such a long explanation.

    Any ideas how to fix this? For now I’ll have to go back to the regular lists and change the range when something is added.

  10. Jaron says:

    This has saved me a world of hurt from an overly critical boss – you’re now my new favourite website………..
    Thanks v much!

Leave a Reply to Sheila Cancel reply

Your email address will not be published. Required fields are marked *