TOCOL function

The TOCOL function in Excel is used to transform a 2D array or range of cells into a single column. It simplifies the process of converting data from a row-and-column structure into a continuous vertical list (a column).

Syntax

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

Parameters

1. array: The range or array that you want to convert into a column.
2. ignore (optional): Specifies which values to ignore. It can be:
   • 1 to ignore blank cells,
   • 2 to ignore errors,
   • 3 to ignore both blanks and errors.
3. scan_by_column (optional): Specifies whether the array should be read by columns (TRUE or 1) or by rows (FALSE or 0). The default is FALSE, meaning the function will scan by rows.

How It Works

• The array parameter is the source data. This can be a 2D range (such as A1:B4) that you want to flatten into a single column.
• The ignore parameter allows you to remove blank cells, error values, or both from the resulting column.
• The scan_by_column parameter tells Excel whether to process the data by rows (default) or by columns.

Examples

Example 1: Convert 2D Array to Column (Basic Usage)

Suppose you have the following dataset in the range A1:C3:

If you use the TOCOL function like this:

=TOCOL(A1:C3)

The output will be:

Example 2: Ignore Blank Cells

Let’s say your data contains blank cells:

To flatten the data and ignore blanks, use:

=TOCOL(A1:C3, 1)

This will return:

Example 3: Convert and Scan by Column

If you want the data to be read column by column (instead of row by row), set scan_by_column to TRUE:

=TOCOL(A1:C3, , TRUE)

The output will now be:

Example 4: Ignore Errors

If your data contains errors (such as #DIV/0! or #N/A), and you want to remove them, you can use the ignore option. For example, if cell A2 contains an error:

To ignore errors and output the data in a column:

=TOCOL(A1:C3, 2)

This will return:

Key Points

• TOCOL is useful for transforming a 2D range into a single-column format, which is helpful for list creation, data preparation, or data analysis.
• You can ignore blank cells, errors, or both using the ignore parameter.
• It provides flexibility in how data is processed, whether row-by-row or column-by-column.

Summary

The TOCOL function simplifies converting 2D arrays into columns, making it easier to work with complex datasets. It also includes powerful options for handling blanks, errors, and customizing how the data is processed.