Excel TOCOL function


The Excel TOCOL function is designed to transform an array or range into a single column. It offers flexibility in how values are scanned and can optionally skip blanks and errors. TOCOL is particularly useful for reorganizing data from a two-dimensional layout into a single column, which can be essential for certain types of data analysis or presentation.
					=TOCOL(array, [ignore], [scan_by_column])
  • array: The array or range to transform.
  • [ignore]: [Optional] Setting to control whether to ignore blanks and/or errors. Use 0 to keep all values (default), 1 to ignore blanks, 2 to ignore errors, and 3 to ignore both blanks and errors.
  • [scan_by_column]: [Optional] Boolean value to control scanning order. TRUE to scan by column, FALSE to scan by row (default).
Return value
A single column of values from the transformed array.

How to use

Use TOCOL by specifying the array to transform. Optionally, set the ignore parameter to control whether to skip blanks or errors, and set the scan_by_column parameter to control the order in which values are read from the array.


Simple TOCOL

Transforming a 2D Array into a Single Column: To convert a two-dimensional range into a single column:

This formula transforms the 2D array in B2:D4 into a single column, scanning row by row from left to right.
TOCOL with Ignored Blanks

Ignoring Blanks in Array Transformation: To transform an array into a column while ignoring blank cells:

					=TOCOL(B2:D4, 1)
This formula converts B2:D4 into a single column and skips any blank cells in the range.
TOCOL with Scanning by Column
Scanning Values by Column in Transformation: To read values from an array by column instead of by row:
					=TOCOL(B2:D4, 0, TRUE)
This transforms B2:D4 into a single column, ignoring any cells that contain errors.
TOCOL with Ignored Errors
Excluding Errors from Transformed Array: To exclude error values when transforming a range:
					=TOCOL(B2:D4, 2)
This formula extracts the first 3 rows and the first 2 columns from the range A1:D10.
Advanced TOCOL for Data Processing
Complex Data Transformation with TOCOL: To transform a range into a single column, ignoring both blanks and errors:
					=TOCOL(B2:D4, 3)
This formula converts the range B2:D4 into a single column while ignoring both blank cells and error values.

Additional Notes

  • TOCOL is particularly useful when you need to process or analyze data in a linear format.
  • The function’s ability to ignore blanks and errors makes it versatile for handling real-world data scenarios.
  • TOCOL, combined with other array functions, can significantly streamline data manipulation tasks.

Related Functions

Excel TOROW function

The Excel TOROW function transforms an array into a single row, with flexibility in scanning and options to omit blanks and errors.

Excel WRAPROWS function

The Excel WRAPROWS function transforms a linear array into a two-dimensional format by wrapping values into rows, useful for structured data presentation.

Excel WRAPCOLS function

The Excel WRAPCOLS function restructures a linear array into a two-dimensional format by wrapping values into columns, enhancing data presentation.

Content Navigation