How to exclude empty cells from a list?

I know the discussion is a bit old, but an explanation might help others.
In fact, Excel considers that if a cell has any formatting, it is no longer "empty." Basically, the "ignore if empty" only works for empty cells without formatting (which, I admit, is a bit stupid of Excel, but oh well...). So, it's not really a solution, but if you remove all formatting from the content range, the dropdown list won't contain blanks.

Your explanation seemed questionable to me.

I still checked: it obviously doesn't work!

<i>Isn't there a simple way to remove empty cells?</i><b> NO!</b> <br /> <br />You will need a VBA macro solution, <br />or a formula combining the OFFSET, MATCH, INDEX, ROW functions... <br />so not simple!

It's just what I indicated to you in message #3: "You need to copy the list to another location, then sort it before selecting the interesting range"
Jacques BOISGONTIER did not directly use column A as the source for his dropdown list, but rather the range created in E using a formula that sorts by eliminating unchecked cells (manually) in B; it is therefore the file user who does the work of selecting the rows, not the computer.
The whole thing is therefore even more complicated than what I had proposed! Especially if your file has a few hundred lines...

Hello,

I would like to propose a simple method:
If it's a column from which you want to remove blanks, you select it and click "remove duplicates" in the "Data" menu.

If it's in a row, it's trickier:
1) You copy your row of data from which you want to remove empty cells.
2) In a new tab, for example, you do a "special paste" > Values + "Transpose"
3) Your row will then be in a column, you remove the duplicates
4) you copy your column
5) in the initial tab you paste your row which is now without empty cells
6) You redo the formatting.

There you go... just for your information, it's a solution.

try and you will understand

Hello forum

A link: List without empties

Mytå