# How to stack all non blank cells from a column?

Here is the same question and its answer for Excel. I want this method for Google Sheets.

https://superuser.com/questions/1623756/how-can-i-stack-all-non-blank-cells-from-a-column/1830491#1830491

I am looking for a formula that will take all non-blank values in one column and stack them in another column.

Please see example below. Essentially I am trying to go from column A to B using a formula:

A B
1 Apple Apple
2 Orange
3 Orange Lemon
4 Lemon Pear
5 Banana
6
7 Pear
8
9 Banana
I have tried the following formula, but it seems to only replicate column A.

`=IFERROR(INDEX(\$A\$1:\$A\$9,SMALL(IF(\$A\$1:\$A\$9<>"",ROW(\$A\$1:\$A\$9)-ROW(\$A\$1)+1),ROWS(\$A\$1:\$A1))),"")`

Any ideas on how to tackle this?

Thank you.

Using `TOCOL()` function in Google Sheets

``````=TOCOL(A1:A,1)
``````

Or, its better to use `FILTER()` function:

``````=IFNA(FILTER(A1:A,A1:A<>""),"")
``````

Edit:

As suggested by Blindspots Sir, few short alternative versions, which takes care of the errors as well, when there is no data, and will return `#REF!` error using `TOCOL()` therefore using the following which will not occur:

Using `TOCOL()`

``````={TOCOL(A:A,1);IFNA(#N/A)}
``````

Or, Using with `FILTER()`

``````=IFNA(FILTER(A:A,LEN(A:A)),)
``````

• Sir, I am not familiar and well versed with Google Sheets, also, I found while using `TOCOL()` if there is no reference and using `IFERROR()` it was still showing `#REF!` error but using `FILTER()` it wasnt so I have said. Commented Feb 17 at 9:15
• @Blindspots the answer has a point. When the column is blank, `tocol(A1:A, 1)` will error out, and that error cannot be caught the normal way. To catch the error, you need to use `filter()` or friends. In my opinion, `A1:A` is preferable to `A:A`, because that way nothing will break if you later insert a header row. Commented Feb 17 at 13:41
• @Blindspots Sir, no it will still return `#REF!` error . Here is a screenshot , I came across this problem while giving a solution in Stackoverflow, for Excel tag, where upon OP, after accepting the answer said the formula is not working and they are using Google Sheets. Even wrapping within `IFERROR()` it doesn't go away. it still exisit. Commented Feb 17 at 20:47
• You are correct. I tested `=TOCOL(A:A, 2)` successfully didnt expect `=TOCOL(A:A, 3)` to reintroduce it. So, `=TOCOL(A:A, 1)` isn't a good option if there may be absolutely no values in the column and a `#REF! ` error is undesirable. I find the behavior intriguing and will look into it further. `=IFNA(FILTER(A:A,A:A<>""),)` is then pretty short. I did find the following works as well and shaves a few more characters off and makes it more peculuar: `={TOCOL(A:A,1);IFNA(#N/A)}` Commented Feb 17 at 22:18
• @Blindspots re "making a statement about what is more efficient" — for the record, `filter(A1:A, A1:A <> "")` is about twice as fast as `tocol(A1:A, 1)`, and `filter(A1:A, isnumber(A1:A))` is another 50% faster still. Be aware that `{TOCOL(A:A,1);IFNA(#N/A)}` has a side-effect: it appends a blank value at the bottom. The question specifies "take all non-blank values" so the formula should not be adding one. The reason why null arrays throw a reference error is that Google Sheet formulas are expected to return either a single value or a 2D array, and the concept of `[[]]` is not supported. Commented Feb 17 at 23:20