How to Use the TOCOL and TOROW Functions in Microsoft Excel

Flattening a multi-column, multi-row matrix of data into a single continuous list is a routine requirement in data analysis, reporting, and dashboard preparation. In older versions of Microsoft Excel, transforming a 2D table into a 1D column required cumbersome combinations of INDEX, INT, MOD, and helper columns. In modern Excel, the dynamic array functions TOCOL and TOROW provide native, single-formula solutions to reshape grids of data into clean single-column or single-row vectors.

Syntax and Behaviour of TOCOL and TOROW

Both functions follow an identical, straightforward syntax:

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

Parameters include:

• array: The two-dimensional range, table, or array to flatten.
• [ignore]: Dictates which values to omit during flattening:
  • 0 (default): Keep all values.
  • 1: Ignore blanks.
  • 2: Ignore errors (e.g., #N/A, #VALUE!).
  • 3: Ignore both blanks and errors.
• [scan_by_column]: A boolean flag controlling scan direction:
  • FALSE (default): Reads row by row (left to right, top to bottom).
  • TRUE: Reads column by column (top to bottom, left to right).

Flattening a Table into a Single Column with TOCOL

Suppose you have a shift roster or regional sales table spanning columns B through D across rows 2 to 10 (range B2:D10), containing blank cells where shifts are unassigned:

1. Click in an empty cell on your worksheet (e.g., cell F2).
2. Enter the following formula to flatten the grid and ignore all empty cells:

   =TOCOL(B2:D10, 1)

3. Press Enter. Excel extracts every populated cell across the entire table and spills a single vertical list downwards.
4. To scan column by column instead of row by row, include the third argument:

   =TOCOL(B2:D10, 1, TRUE)

Flattening into a Single Row with TOROW

If your reporting model requires a horizontal stream of values instead of a vertical list, use TOROW:

# Flatten a table into a horizontal row, ignoring blanks and errors:
=TOROW(B2:D10, 3)

Nesting with SORT, UNIQUE, and FILTER

Combining TOCOL with other dynamic array functions creates automated data transformation pipelines. For example, to extract a sorted, unique list of all employees across multiple departments while discarding errors and blanks:

=SORT(UNIQUE(TOCOL(B2:E20, 3)))

In this single formula:

• TOCOL(B2:E20, 3) flattens the multi-column table into a single column while stripping out blank cells and errors.
• UNIQUE(...) eliminates duplicate employee names.
• SORT(...) arranges the resulting names in alphabetical order.

Mastering TOCOL and TOROW streamlines data normalization in Microsoft Excel, replacing complex matrix formulas with clean, high-performance dynamic arrays.