Blanks keep disappearing in dropdown

**janschepens** · 10-24-2011, 08:43 AM

I know how to make dropdown menus and how to make the first selection appear as a blank. But I think there is a weird bug because after I do my own thing for a while a come back to change the dropdown, the blank will sometimes just disappear. For example if I have the following cells names Fruit:

[blank]
Apples
Oranges
Grapes

and I enter data validation, make a list with the formula =Fruit
At first the blank will appear, but give it a while and the first entry will be Apples. I can fix it by going to my list and entering something in the [blank] and then deleting it, but it should be unneccessary to "fix" this each time it is acting up.
Either I am doing something wrong, or there is a bug in the program that someone really should have noticed by now. Has anyone else noticed this?

**royUK** · 10-24-2011, 08:50 AM

Why would you want the blank like that?

**janschepens** · 10-24-2011, 08:53 AM

When I put said [blank] I meant that the cell would have nothing in it, I wrote it so the reader would understand that there was a cell with nothing in it in that space. Sorry if it was confusing

**royUK** · 10-24-2011, 09:16 AM

I'm presuming you mean Data Validation, but I still can't see why you need an empty entry

**Pauleyb** · 10-24-2011, 10:22 AM

I believe the goal is to show a blank so that the user knows they need to fill in something. Having a default of 'Apples' may be missed by the user and in some cases may cause some confusion.

I can't explain the blank mystery, but I have seen cases where people use other text, such as
'Select Fruit' or '?????'.

Is your cell completely empty or does it have a space (" ") character in it? If it is empty, would a " " work?

Pauley

**Whizbang** · 10-24-2011, 11:11 AM

There is an option for "ignore blank" in the data validation dialog box. This eliminates the need for a blank option in the list. As for default text, you simply enter your text in the cell before applying the validation (or at least disable errors while you enter the default text/instructions, and then re-enable errors). And if you need instrctions like "Select from the list" then take advantage of the "Input Message" option of data validation. The Input Message pops up, very similar to Comments, as soon as the cell is active, giving helpful, contextual instructions that are not limited to the height and width of the cell.

**janschepens** · 10-24-2011, 11:51 AM

Thanks for the advice. The trick that paulyb mentioned work so if I make the first cell:
=""
it seems to not mess up. And yes the reason I need it to have a blank option is to keep the user from being confused because the rest of the form reacts differently depending on which option the user select or if the user does not select an option. So the blank serves as a kind of "default". Oh and from my understanding the ignore blank just keeps excel from yelling at you if have a blank in the spot.

**Whizbang** · 10-24-2011, 12:00 PM

Right. This makes a blank cell an option. The user simply clears the cell. This has the same effect as selecting an empty option from the drop-down.

But I see your point about the difference between selecting a blank option and not selecting anything. But in that case, wouldn't NA be more appropriate?