Blank Option in Data Validation

[excelbytes]

I have a blank cell in the list of cells for my data validation list. However it's not showing up when I click on the drop down arrow. I unchecked "Ignore blank". Still doesn't work. I am on Microsoft 365 Office Insider, don't know if that makes a different. See attached images.

 

[MDCurry]

That seems strange.
I tried to replicate your issue and had the top cell (L1) display as blank but the bottom cell L6 was not displayed.
Even weirder, I then shaded L1:L6 yellow and then checked again and both blanks magically were displayed in the drop-down.
I went back and removed the yellow fill and both blanks were still displaying.
Not sure why this worked but perhaps it works for you.
Note: I left the "Ignore blanks" checked

 

[evxret]

I have a blank cell in the list of cells for my data validation list. However it's not showing up when I click on the drop down arrow. I unchecked "Ignore blank". Still doesn't work. I am on Microsoft 365 Office Insider, don't know if that makes a different. See attached images.

I always face this issue. Usually what I do is create a unique list of that column which also filters out blanks at the same time.
Example Below:

Once you have this unique list, you can refer to cell C1 when selecting your list and add a "#" sign to the end of it and it will auto-update your drop downs as items are added/removed.

Example Below:

 

[excelbytes]

I always face this issue. Usually what I do is create a unique list of that column which also filters out blanks at the same time.
Example Below:
View attachment 62257
Once you have this unique list, you can refer to cell C1 when selecting your list and add a "#" sign to the end of it and it will auto-update your drop downs as items are added/removed.

Example Below:
View attachment 62258

I'm not sure if I understand what you indicated, but I do want a blank in the drop down list but Excel won't allow it to be displayed.

 

[excelbytes]

That seems strange.
I tried to replicate your issue and had the top cell (L1) display as blank but the bottom cell L6 was not displayed.
Even weirder, I then shaded L1:L6 yellow and then checked again and both blanks magically were displayed in the drop-down.
I went back and removed the yellow fill and both blanks were still displaying.
Not sure why this worked but perhaps it works for you.
Note: I left the "Ignore blanks" checked

No, that didn't work for me.

 

[evxret]

I'm not sure if I understand what you indicated, but I do want a blank in the drop down list but Excel won't allow it to be displayed.

Gotcha. Sorry about that, I totally misunderstood your question. I thought you were trying to do the opposite and make sure blanks weren't displayed. I apologize.

After doing a bit of experimenting, I am seeing that blank cells display on the drop-down as long as there are values sandwiching them. For example, if you have values in A1 + A3, but A2 is blank, it will display the blank in between them.

See Image Below:

As far as displaying blank cells that ARENT sandwiched by data, I'm not sure & Im sorry but I cannot help you on that, I cruised around google looking for the answer, but to no prevail.

 

[jtakw]

Hi,

As I don't have the same issue as you, and can not replicate your problem, so I'm not sure if this might work.
If you want a Blank at the Top of your drop down list, try putting this:

=""

in the 1st cell of your drop down range.

 

[evxret]

I have a blank cell in the list of cells for my data validation list. However it's not showing up when I click on the drop down arrow. I unchecked "Ignore blank". Still doesn't work. I am on Microsoft 365 Office Insider, don't know if that makes a different. See attached images.

After doing a bit of experimenting, I am seeing that blank cells display on the drop-down as long as there are values sandwiching them. For ex

Hi,

As I don't have the same issue as you, and can not replicate your problem, so I'm not sure if this might work.
If you want a Blank at the Top of your drop down list, try putting this:

=""

in the 1st cell of your drop down range.

Exactly what I was going to recommend. You need to place fake "blank" values that aren't actually blank but contain values that will display it as blank. I know this is just a work around, but if you have your blank cell displaying at the top, any other blank values should be displayed as well, given they have info after them. You'd have to do the same thing for any cells at the end of a dataset that are blank aswell.

 

[excelbytes]

Exactly what I was going to recommend. You need to place fake "blank" values that aren't actually blank but contain values that will display it as blank. I know this is just a work around, but if you have your blank cell displaying at the top, any other blank values should be displayed as well, given they have info after them. You'd have to do the same thing for any cells at the end of a dataset that are blank aswell.

I don't know what's going on, but that doesn't work for me either. See image.