Excel Drop Down Opens At End

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. Sometimes the Excel drop down opens at end of the list, instead of the top. Here’s how to fix that problem.

Excel Drop Down Opens At End

Here’s a screen shot that shows the problem. Cell D4 has a drop down list, created with Excel Data Validation.

When you click the arrow to open the drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list.

dropdownlistblank06

That can be annoying if you have to scroll all the way back up to the top of a long list, where you have the put the most popular items.

dropdownlistblank05

Why It Happens

Why does this happen, and how can you prevent it?

In this example, 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 with the data validation drop down lists.

Match the Cell Contents

When a data validation 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

  • Cell D4 is blank, so the list finds the first matching entry in the Products list — a blank row at the end of the list.

dropdownlistblank06

Another Problem with Blanks in List

So that’s the first problem caused by blank cells in the data validation source list — 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.

There is another problem too, and it is not just an annoyance. If you have any blank cells in the list, people might be able to enter invalid items in the data validation cells. That could cause serious problems in your workbook, if you’re depending on valid entries in those cells.

Prevent the Problems

To prevent these problems, you can remove the blanks from the source list, by using a dynamic range, which will adjust automatically when items are added or removed.

A dynamic range can be created with a formula, like the OFFSET formula shown below, or by using a named Excel table.

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

Video: Drop Down Opens At End

To see the steps for setting up a dynamic named range for the product list, to fix the problem when drop down opens at end, watch this short video.

______________________

30 thoughts on “Excel Drop Down Opens At End”

  1. 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. 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. 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.

  4. 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!

      1. Debra, Could you give an example of “might be able to enter invalid items”?

  5. 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…

  6. 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)”

  7. 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.

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

  9. Doesn’t work and I figured out why. My named range has formulas to pick up extra data if extra data is available. I need those formulas due to an uncertain number of items that may need to populate the list. The offset/counta formula picks up those cells with formulas even if the formula is dictating the cells to be blank. Don’t want to fix with vba or a blank row at top, but may have to. It seems fairly ridiculous that Excel starts the drop-down box at the first blank row. That is great coding from 1972. This is ridiculously flawed. 🙁

  10. Doesn’t work for me. I keep getting an error message that says, “There’s a problem with this formula. Not trying to type a formula? When the first character is an equal (+) or minus (-) sign, Exel thinks it’s a formula…. To get around this, type an apostrophe (‘) first…”

  11. Does not work if the list array has few blank cells and few non-blank below them ! It does show the first item correctly, but since the array is not limited it picks up all items in that column..

  12. Thanks for this Debra. I’ve been using Excel since 1995 (or was it 94? 93?), but still quite often I learn new things about it. The solution in this video and page provided one of those moments and is so well explained, I just wanted to say thank you.

  13. Hi, wondering, when I have the name list for the drop down in its own sheet. Not sure how to change the formula to also include the OFFSET and the name of the sheet that has it?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.