TOCOL

TOCOL(array [,ignore] [,scan_by_column])

Returns the array transformed into a single column.

arrayThe array or reference to return as a column.
ignore(Optional) The number indicating which values to ignore:
0 = Keep all values (default)
1 = Ignore blanks
2 = Ignore errors
3 = Ignore blanks and errors
scan_by_column(Optional) A logical value indicating how the array will be scanned:
False (or 0) = Scanned by Row (default)
True (<> 0) = Scanned by Column

REMARKS
* This function was added in Excel 2024.
* This function can create a Dynamic Array Formula.
* If "ignore" is left blank, then keep all values is used.
* If "scan_by_column" is left blank, then False is used.
* If any of the cells are blank, then they are padded with 0.
* You can use the TOROW function to return the array transformed into a single row.
* You can use the WRAPCOLS function to return the array transformed into multiple columns.
* You can use the WRAPROWS function to return the array transformed into multiple rows.
* This function was first released in March 2022.
* For the Microsoft documentation refer to support.microsoft.com

 ABCD
1=TOCOL(B1:C2) = {1 ; 2 ; 4 ; 5}123
2=TOCOL(B1:C2,0) = {1 ; 2 ; 4 ; 5}456
3=TOCOL(B1:C2,0,False) = {1 ; 2 ; 4 ; 5}   
4=TOCOL(B1:C2,1) = {1 ; 2 ; 4 ; 5}123
5=TOCOL(B1:C2,1,True) = {1 ; 4 ; 2 ; 5}4  
6=TOCOL(B4:D5) = {1 ; 2 ; 3 ; 4 ; 0 ; 0}   
7=TOCOL(B4:D5,0) = {1 ; 2 ; 3 ; 4 ; 0 ; 0}   
8=TOCOL(B4:D5,1) = {1 ; 2 ; 3 ; 4}   

1 - What is the range "C1:D2" transformed into a single column.
2 - What is the range "C1:D2" transformed into a single column, keeping all the values.
3 - What is the range "C1:D2" transformed into a single column, keeping all the values and scanning by row.
4 - What is the range "C1:D2" transformed into a single column, ignoring all blanks.
5 - What is the range "C1:D2" transformed into a single column, ignoring all blanks and scanning by column. The order has changed slightly.
6 - What is the range "C4:E5" transformed into a single column
7 - What is the range "C4:E5" transformed into a single column, keeping all the values.
8 - What is the range "C4:E5" transformed into a single column, ignoring all blanks.

© 2025 Better Solutions Limited. All Rights Reserved. © 2025 Better Solutions Limited Top