How to use the TOCOL function

The TOCOL function lets you rearrange values in a 2D cell range to a single column.

Dynamic array formula in cell E3:

The TOCOL function is available to Excel 365 users and is in the "Array manipulation" category.

1. TOCOL Function Syntax

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

Back to top

2. TOCOL Function Arguments

 

array	Required. The source cell range or array.
[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.

Back to top

3. TOCOL Function 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.

Dynamic array formula in cell E4:

3.1 Explaining formula

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}

Back to top

4. TOCOL Function 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 - by row.

Dynamic array formula in cell E4:

4.1 Explaining formula

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}

Back to top

5. TOCOL Function - blanks and errors

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:

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}

Back to top

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

Back to top

7. TOCOL function - 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 H2:I3 into a single column.

Dynamic array formula in cell B8:

7.1 Explaining formula

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, H2: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, H2: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}

Back to top

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:

8.1 Explaining formula

Step 1 - Rearrange values

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

TOCOL(B2:G5)

becomes

TOCOL({"Elizabeth","Patricia","Elizabeth","Patricia","Jennifer","William"; "John","John","Robert","Patricia","William","Mary"; "Michael","Elizabeth","Linda","Linda","Michael","Jennifer"; "Linda","William","William","Patricia","Linda","Mary"})

and returns

{"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "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))

becomes

UNIQUE({"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"})

and returns

{"Elizabeth";"Patricia";"Jennifer";"William";"John";"Robert";"Mary";"Michael";"Linda"}.

Back to top

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:

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, H2:I5)

becomes

HSTACK({"Elizabeth", "Patricia"; "John", "John"; "Michael", "Elizabeth"; "Linda", "William"}, {"Elizabeth", "Patricia"; "Robert", "Patricia"; "Linda", "Linda"; "William", "Patricia"}, {"Jennifer", "William"; "William", "Mary"; "Michael", "Jennifer"; "Linda", "Mary"})

and returns

{"Elizabeth", "Patricia", "Elizabeth", "Patricia", "Jennifer", "William"; "John", "John", "Robert", "Patricia", "William", "Mary"; "Michael", "Elizabeth", "Linda", "Linda", "Michael", "Jennifer"; "Linda", "William", "William", "Patricia", "Linda", "Mary"}

Step 2 - Rearrange values to one column

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

TOCOL(HSTACK(B2:C5, E2:F5, H2:I5))

becomes

TOCOL({"Elizabeth", "Patricia", "Elizabeth", "Patricia", "Jennifer", "William"; "John", "John", "Robert", "Patricia", "William", "Mary"; "Michael", "Elizabeth", "Linda", "Linda", "Michael", "Jennifer"; "Linda", "William", "William", "Patricia", "Linda", "Mary"})

and returns

{"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "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, H2:I5)))

becomes

UNIQUE({"Elizabeth"; "Patricia"; "Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "John"; "Robert"; "Patricia"; "William"; "Mary"; "Michael"; "Elizabeth"; "Linda"; "Linda"; "Michael"; "Jennifer"; "Linda"; "William"; "William"; "Patricia"; "Linda"; "Mary"})

and returns

{"Elizabeth"; "Patricia"; "Jennifer"; "William"; "John"; "Robert"; "Mary"; "Michael"; "Linda"}.

Back to top