How to use the TOCOL function

Author: Oscar Cronquist Article last updated on January 31, 2023

TOCOL function example 1

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

Dynamic array formula in cell E3:

=TOCOL(B2:E4)

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

TOCOL Function Syntax
TOCOL Function Arguments
TOCOL Function example
TOCOL Function example - by column
TOCOL Function - blanks and errors
TOCOL Function alternative
TOCOL Function - multiple cell ranges as source
Extract unique distinct values from a cell range
Extract unique distinct values from multiple cell ranges

1. TOCOL Function Syntax

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

2. TOCOL Function 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. TOCOL Function example

TOCOL function example1

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:

=TOCOL(B2:E4)

Note, if any of the values in the source ranges change the same values in the result from the TOCOL function change as well.

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}

4. TOCOL Function example - by column

TOCOL function example2

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:

=TOCOL(B2:E4, , TRUE)

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}

5. TOCOL Function - blanks and errors

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:

=TOCOL(B2:E4)

The image below shows how to deal with blanks and error values.

TOCOL function blanks and errors1

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:

=TOCOL(B2:E4, 3)

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:

7. TOCOL function - multiple cell ranges as source

TOCOL function multiple source cell ranges

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:

=TOCOL(VSTACK(B2:C3, E2:F3, H2:I3))

Update! The TOCOL function accepts multiple references in the array argument. There is no need for the VSTACK function. Here is how:

=TOCOL((B2:C3, E2:F3, H2:I3))

The parentheses and a delimiting comma let you use multiple non-adjacent cell ranges.

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}

8. Extract unique distinct values from a multi-column cell range

TOCOL function Unique distinct values from a 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:

=UNIQUE(TOCOL(B2:G5))

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"}.

9. Extract unique distinct values from multiple cell ranges

TOCOL function 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:

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

Update!

No need to use the HSTACK function. Use the comma as a union operator, it combines multiple cell ranges.

=UNIQUE(TOCOL((B2:C5, E2:F5, H2:I5)))

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

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

and returns

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

'TOCOL' function examples

The following 14 articles contain the TOCOL function.

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

Extract a unique distinct list across multiple columns and rows sorted based on frequency
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]

Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Find empty hours in a weekly schedule
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]

Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]

How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]

List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

Rearrange values in a cell range to a single column
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

Functions in this article

TOCOL TRUE VSTACK UNIQUE HSTACK

Functions in 'Array manipulation' category

The TOCOL function function is one of many functions in the 'Array manipulation' category.

CHOOSECOLS function

Returns given columns from a cell range or array.

CHOOSEROWS function

Returns given rows from a cell range or array.

DROP function

Removes a given number of rows or columns from a 2D cell range or array.

EXPAND function

Increases a cell range or array by a specified number of columns and rows.

HSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

TAKE function

Returns a given number of rows or columns from a 2D cell range or array.

TOCOL function

Rearranges values in 2D cell ranges to a single column.

TOROW function

Rearranges values from a 2D cell range to a single row.

VSTACK function

Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

WRAPCOLS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

WRAPROWS function

Rearranges values from a single row to a 2D cell range based on a given number of values per column.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the TOCOL function

Table of Contents

1. TOCOL Function Syntax

2. TOCOL Function Arguments

3. TOCOL Function example

3.1 Explaining formula

Step 1 - TOCOL function

Step 2 - Populate arguments

Step 3 - Evaluate function

4. TOCOL Function example - by column

4.1 Explaining formula

Step 1 - TOCOL function

Step 2 - Populate arguments

Step 3 - Evaluate function

5. TOCOL Function - blanks and errors

5.1 Explaining formula

6. TOCOL alternative

7. TOCOL function - multiple cell ranges as source

7.1 Explaining formula

Step 1 - Stack values from multiple sources

Step 2 - Rearrange values into one column

8. Extract unique distinct values from a multi-column cell range

8.1 Explaining formula

Step 1 - Rearrange values

Step 2 - Extract unique distinct values

9. Extract unique distinct values from multiple cell ranges

9.1 Explaining formula

Step 1 - Rearrange values

Step 2 - Rearrange values to one column

Step 3 - Extract unique distinct values

Excel '365' functions

'TOCOL' function examples

Functions in this article

Functions in 'Array manipulation' category

Excel function categories

Excel categories

Leave a Reply

How to comment