How to use the TOCOL function
What is the TOCOL function?
The TOCOL function lets you rearrange values in 2D cell ranges to a single column.
What is TOCOL an abbreviation of?
TOCOL stands for to column.
Which Excel version is TOCOL in?
The TOCOL function is available to Excel 365 users
What category is the TOCOL function in?
The TOCOL function is in the "Array manipulation" category.
Table of Contents
1. Syntax
The TOCOL function has three arguments, the first one is required the other two are optional.
TOCOL(array, [ignore], [scan_by_col])
2. Arguments
| array | Required. The source cell range or array. Use parentheses and comma delimiter to add more arrays or cell ranges. See section 7 below for an example. |
| [ignore] | Optional. Ignore specified values. 0 - keep all values (default) 1 - ignore blanks 2 - ignore errors 3- ignore blanks and errors |
| [scan_by_col] | Optional. How the function fetches the values from the source. FALSE - by row (default). TRUE - by column. |
3. Example
The image above demonstrates how the TOCOL function rearranges the values to fit a single column, and that it is fetching values by row in its default state.
This means that the TOCOL function takes the values in cell B2:E4 row by row and transposes them so they fit a single column. For example, the first row is 89, 68, 19, and 37 and they are distributed horizontally in B2:E2,
The TOCOL function rearranges the values so they are distributed vertically like this: 89; 68; 19; 37. It the goes on to the second row and transposes those values below the first row. The blue arrows shows this beahviour in the TOCOL functions default state. You can however change this so it scans the cell range column by column instead of row by row.
Dynamic array formula in cell E4:
The TOCOL function is incredibly useful if you want to for example extract unique distinct values across multiple columns. The UNIQUE function requires an array containing values distributed vertically one by one. If not the UNIQUE function extracts unique distinct rows.
3.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - TOCOL function
TOCOL(array, [ignore], [scan_by_col])
Step 2 - Populate arguments
array - B2:E4
Step 3 - Evaluate function
TOCOL(B2:E4)
becomes
TOCOL({89, 68, 19, 37;
27, 84, 92, 63;
26, 98, 62, 100})
and returns
{89; 68; 19; 37; 27; 84; 92; 63; 26; 98; 62; 100}
4. Example - by column
The image above demonstrates how the TOCOL function rearranges the values to fit a single column. This example shows it fetching values column by column. Default value is FALSE - which means by row.
The blue arrows show that the TOCOL function is also capable of rearranging values column by column. The first column is 89, 27, 26, 68 which is also displayed in the output array in cell E4. The second column is then put below these values, this continues column by column until all columns have been scanned.
Dynamic array formula in cell E4:
The third argument lets you specify the scan state: scan_by_col or scan_by_row. TRUE means scan_by_col and FALSE means scan_by_row which is also the default state.
4.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - TOCOL function
TOCOL(array, [ignore], [scan_by_col])
Step 2 - Populate arguments
array - B2:E4
[scan_by_col]) - TRUE
Step 3 - Evaluate function
TOCOL(B2:E4, , TRUE)
becomes
TOCOL({89, 68, 19, 37;
27, 84, 92, 63;
26, 98, 62, 100})
and returns
{89; 68; 19; 37; 27; 84; 92; 63; 26; 98; 62; 100}
5. TOCOL Function - blanks and errors
The TOCOL function can handle blanks and errors, however, you must specify that in the aruments if you want that functionality. The image above demonstrates what happens when your source data has empty values and errors. The result is an array containing a 0 (zero) located at the empty values and the error values are kept.
Dynamic array formula in cell E4:
The image below shows how to deal with blanks and error values.
You can ignore blank and error values using the second argument.
Here are all valid numbers for the second argument:
0 - keep all values (default)
1 - ignore blanks
2 - ignore errors
3- ignore blanks and errors
Dynamic array formula in cell E4:
There is an instance when this doesn't work. This happens if you use the IF function to filter specific values in the TOCOL function. Here is an example.
=TOCOL(IF(B2:E4<50,"",B2:E4), 3)
The TOCOL function can't handle blanks and errors from the IF function at all, they all show even if you use 3 in the second argument. This is kind of a disappointment, I hope Microsoft software engineers fix this issue in upcoming releases.
5.1 Explaining formula
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:E4, 3)
becomes
TOCOL({89, 68, 19, 37;
27, 0, 92, 63;
26, 98, 62, #DIV/0!}
)
and returns
{89; 68; 19; 37; 27; 92; 63; 26; 98; 62}
6. TOCOL alternative
There are no great alternative formulas for earlier Excel versions. Here are a few links:
- Rearrange values in a cell range to a single column
- Combine cell ranges ignore blank cells
- Merge two columns
- Merge three columns into one list
7. Example - multiple cell ranges as source
You can consolidate values across worksheets if you combine the VSTACK function and the TOCOL function, the formula spills values into a single column.
The image above shows how to combine values from cell ranges B2:C3, E2:F3, and h3:I3 into a single column.
Dynamic array formula in cell B8:
The formula in cell B8 joins the three non-adjacent cell ranges vertically and then scans the resulting array row by row and rearranges the values to a single column. You can try different outcomes based on how you want the values arranged. The HSTACK function joins the cell ranges horizontally, and the third argument in the TOCOL function lets you specify how the function scans and rearranges the values in the array. The options are scan_by_row or scan_by_column.
Update! The TOCOL function accepts multiple references in the array argument. There is no need for the VSTACK function. Here is how:
The parentheses and a delimiting comma let you use multiple non-adjacent cell ranges. Note that this is done in the first argument.
7.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - Stack values from multiple sources
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the bottom of a cell range or array.
VSTACK(array1,[array2],...)
VSTACK(B2:C3, E2:F3, h3:I3)
becomes
VSTACK({89,68;27,84}, {19,37;92,63}, {26,98;62,100})
and returns
{89,68;
27,84;
19,37;
92,63;
26,98;
62,100}
Step 2 - Rearrange values into one column
TOCOL(VSTACK(B2:C3, E2:F3, h3:I3))
becomes
TOCOL({89,68;
27,84;
19,37;
92,63;
26,98;
62,100})
and returns
{89; 68; 27; 84; 19; 37; 92; 63; 26; 98; 62; 100}
8. Extract unique distinct values from a multi-column cell range
The UNIQUE function doesn't let you extract unique distinct values if your source data has multiple columns, it will return unique distinct rows instead.
However, the TOCOL function allows you to rearrange the values to a single column array, and that lets you extract unique distinct values.
Dynamic array formula in cell B8:
The TOCOL function makes it so much easier to extract unique distinct values. The values must be arranged in a single column for the UNIQUE function to work properly.
8.1 Explaining formula
Tip! You can follow the formula calculations step by step by using the "Evaluate" tool located on the Formula tab on the ribbon. This makes it easier to spot errors and understand the formula in greater detail. The following steps shows the calculations in great detail.
Step 1 - Rearrange values
TOCOL(array, [ignore], [scan_by_col])
TOCOL(B2:G5)
returns {"Elizabeth"; "Patricia"; ... ; "Mary"}.
The column delimiting character changed from a comma to a semicolon in the array above. This means that each value is in a new row, in other words, values are rearranged to fit a single column.
Step 2 - Extract unique distinct values
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(B2:G5))
returns {"Elizabeth";"Patricia";"Jennifer";"William";"John";"Robert";"Mary";"Michael";"Linda"}.
9. Extract unique distinct values from multiple cell ranges
You can consolidate values across worksheets, rearrange values so they fit a single column, and then extract unique distinct values.
The image above shows a formula that extracts unique distinct values from three different non-adjacent cell ranges.
Dynamic array formula in cell B8:
Update!
No need to use the HSTACK function. Use the comma as a union operator, it combines multiple cell ranges.
This formula is useful for getting values across worksheets consolidated into one array, it is also dynamic meaning it changes instantly if the source ranges change.
9.1 Explaining formula
Step 1 - Rearrange values
The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell to the right of a cell range or array (horizontal stacking)
HSTACK(array1,[array2],...)
HSTACK(B2:C5, E2:F5, h3:I5)
returns {"Elizabeth", "Patricia", ... , "Mary"}
Step 2 - Rearrange values to one column
TOCOL(array, [ignore], [scan_by_col])
TOCOL(HSTACK(B2:C5, E2:F5, h3:I5))
returns
{"Elizabeth"; "Patricia"; ... ; "Mary"}.
Step 3 - Extract unique distinct values
The UNIQUE function is a new Excel 365 function that returns unique and unique distinct values/rows.
UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(HSTACK(B2:C5, E2:F5, h3:I5)))
returns
{"Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "Robert"; "Mary"; "Michael"; "Linda"}.
Useful resources
TOCOL function - Microsoft support
Excel TOCOL function - convert range to single column
'TOCOL' function examples
This post explains how to lookup a value and return multiple values. No array formula required.
Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Functions in 'Array manipulation' category
The TOCOL function function is one of 11 functions in the 'Array manipulation' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form