Instead of FLATTEN, we can now use the TOCOL function to transform an array/range of cells into a single column in Google Sheets.
You can use TOCOL(range) or FLATTEN(range) to transform an array into a column.
The result will be the same. Both formulas will scan the values by row.
But the TOCOL function has arguments that help users to remove blanks & error values in the range while flattening. Also, it can scan by column.
We usually use the FLATTEN to unpivot a table. If you use the TOCOL for the same purpose, you can control the order of the labels in the unpivoted range.
We will go to that in the later part of this walk-through guide.
Syntax and Arguments
Syntax: TOCOL(array_or_range, [ignore], [scan_by_column])
Where;
array_or_range: The array or cell range to return as a column.
ignore: No values by default. You can specify 0 (no values), 1 (blanks), 2 (errors), and 3 (blanks and errors).
scan_by_column: It determines whether the array is scanned by column (TRUE) or row (FALSE). The default is FALSE.
TOCOL Function Examples in Google Sheets
Basic Formulas
The following TOCOL formula in cell B9 transforms the cell range B2:D5 into a single column, scanning the values by column.
=tocol(B2:D5,0,true)
The E9 formula does the same, but by scanning the values by row.
In both results, you can see a blank cell and an error value.
You can remove either one or both by replacing 0 with 1, 2, or 3.
For example, to remove both, you can use the following formula.
=tocol(B2:D5,3,false)TOCOL Function for Unpivot a Table in Google Sheets
You may be familiar with using the FLATTEN function to unpivot a dataset in Google Sheets.
TOCOL can also work in that case.
In the following example, we will use the FLATTEN and TOCOL functions to unpivot a dataset in Google Sheets.
I’ve used =ArrayFormula(split(flatten(A2:C2&"|"&A3:C5),"|")) in cell A9 to unpivot the array or range A2:C5.
We will get the same result if we replace FLATTEN with TOCOL in that cell.
=ArrayFormula(split(tocol(A2:C2&"|"&A3:C5),"|"))As I have mentioned, the default arguments in the TOCOL function will result in a FLATTENed output.
The following D9 formula unpivots the data and returns the result by field label order.
=ArrayFormula(split(tocol(A2:C2&"|"&A3:C5,0,true),"|"))When you want the unpivoted data sorted by field labels, use the TOCOL function with the scan_by_colulmn argument set to TRUE.
TOCOL and WRAPCOLS to Insert a Blank Row after Every Row
There are many options to insert a blank row after every other row in Google Sheets.
I’ve already posted a tutorial on this topic when the above two functions (TOCOL and WRAPCOLS) weren’t available in Sheets.
Related: How to Insert Blank Rows Using a Formula in Google Sheets.
Here is the TOCOL and WRAPCOLS combination formula to meet the same.
=ArrayFormula(let(col,tocol(B2:D5,0,true),iferror(wrapcols(tocol({col,col/0},,false),rows(B2:D5)*2))))Note: You should replace B2:D5 with your actual data range.
How does this new formula work?
The new (2023) TOCOL function transforms the array B2:D5 into a single column.
tocol(B2:D5,0,true)I’ve used the LET function to name this formula, and the name is col. Wherever you use col in the formula, you must read it as the above formula.
col/0 returns that many error cells.
The third TOCOL transforms these ranges into a single column.
tocol({col,col/0}The WRAPCOLS function wraps the output back to the data range.
