How to Reshape and Flatten Arrays Using TOCOL and TOROW Functions in Microsoft Excel

Data imported into Microsoft Excel frequently arrives structured across multi-column tables, matrices, or scattered grid blocks rather than clean, contiguous database lists. Traditionally, transforming a two-dimensional grid of entries into a single vertical column or horizontal row required complex index arithmetic, cumbersome copy-paste operations, or custom Power Query transforms. Microsoft Excel modernises this array transformation with two dedicated dynamic array functions: TOCOL (To Column) and TOROW (To Row). These native formulas enable analysts to flatten, unpivot, and normalise complex multi-dimensional ranges into streamlined vectors with precise control over blank cells, errors, and scan orientation.

Syntax of TOCOL and TOROW

Both functions share identical parameter architecture, differing only in the directional orientation of their output spill:

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

• array: The source range, multi-column table, or dynamic array to transform.
• [ignore]: An optional integer argument specifying which data values to exclude:
  • 0 (Default): Keep all values, including blank cells and error values.
  • 1: Ignore blank cells.
  • 2: Ignore error values (such as #N/A, #VALUE!).
  • 3: Ignore both blank cells and errors simultaneously.
• [scan_by_column]: An optional boolean determining how Excel traverses the grid:
  • FALSE (Default): Scans row-by-row (left-to-right, then down).
  • TRUE: Scans column-by-column (top-to-bottom, then across).

Flattening a Matrix into a Single Column

Suppose you maintain a weekly team roster arranged across cells A2:E10, where each column represents a day of the week and rows contain staff names. To consolidate all scheduled staff into a unified vertical list for database ingestion:

=TOCOL(A2:E10)

When entered into an empty cell, Excel automatically spills all names downward into a single column. If the schedule contains unscheduled empty shifts that produce unsightly 0 or blank entries in the output, configure the [ignore] parameter:

=TOCOL(A2:E10, 1)

By supplying 1, Excel automatically strips out all empty cells, producing a compact, continuous vertical list containing only assigned staff members.

Controlling Scan Direction: Row-Wise vs Column-Wise

The reading order of multi-column data often conveys chronological or logical hierarchy. Selecting the proper scan mode ensures that records remain properly ordered:

• Row-by-Row Scanning (Default / FALSE): Excel reads cell A2, B2, C2, D2, E2, then drops to A3, B3, and so on. This orientation is ideal when items across each horizontal row belong to the same record or chronological sequence.
• Column-by-Column Scanning (TRUE): If your grid represents continuous time-series data stacked by department columns, you can read vertically down column A before proceeding to column B:

=TOCOL(A2:E10, 1, TRUE)

This processes column A completely before advancing across columns B through E.

Combining TOCOL with UNIQUE and SORT for Dynamic De-Duplication

Because TOCOL outputs a native dynamic array, it chains effortlessly with other modern Excel calculation engines. To extract an alphabetised, de-duplicated list of unique product codes distributed across multiple order forms:

=SORT(UNIQUE(TOCOL(B3:G25, 3)))

In this consolidated formula:

1. TOCOL(B3:G25, 3) collapses the entire 6×23 grid into a single column while stripping both empty cells and formula error values.
2. UNIQUE(...) filters out repeated occurrences, isolating distinct product codes.
3. SORT(...) arranges the final output in ascending alphabetical order, delivering a real-time summary vector that updates automatically whenever the underlying grid changes.