How to Transform and Flatten 2D Ranges in Microsoft Excel Using TOCOL and TOROW

Tabular datasets in Microsoft Excel are frequently presented in two-dimensional matrices—such as monthly attendance grids, matrix rosters, or multi-column inventory tables. When preparing data for pivot tables, database imports, or lookup arrays, converting these multi-row and multi-column ranges into a single continuous vertical or horizontal list traditionally required complex VBA macros, cumbersome Power Query transformations, or nested INDEX and SEQUENCE formulas.

Microsoft solved this dimensional transformation problem with the TOCOL and TOROW dynamic array functions. These functions take any two-dimensional matrix or array and flatten it into a single vector, offering built-in controls to ignore blank cells, suppress error values, and define whether the transformation scans across rows or down columns.

Syntax and Parameters of TOCOL and TOROW

Both functions share identical arguments that dictate how input arrays are parsed:

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

• array: The multi-cell range or dynamic array expression to be transformed into a single dimension.
• ignore (optional): Dictates whether empty cells and errors are preserved or discarded:
  • 0 (default): Keep all values (including blanks and errors).
  • 1: Ignore blank cells.
  • 2: Ignore errors (such as #N/A, #VALUE!).
  • 3: Ignore both blanks and errors simultaneously.
• scan_by_column (optional): Dictates parsing order:
  • FALSE (default): Scans horizontally across rows (reads Row 1 left-to-right, then Row 2, etc.).
  • TRUE: Scans vertically down columns (reads Column A top-to-bottom, then Column B, etc.).

Transforming a Multi-Column Ledger into a Single Column with TOCOL

Consider a schedule where project tasks are spread across columns B2:F10:

1. Select the destination cell where the unified list should begin (for example, H2).
2. Enter the basic formula:

=TOCOL(B2:F10)

Excel scans row-by-row, spilling every task sequentially down column H.

3. To remove empty cells from incomplete schedules so that no blank rows appear in the output vector:

=TOCOL(B2:F10, 1)

4. To clean up imported datasets that contain missing cell entries and calculation errors (such as #DIV/0!):

=TOCOL(B2:F10, 3)

Scanning by Column Instead of by Row

When tracking time-series data or category columns where each column represents an independent sequence, scanning across rows can scramble temporal order:

1. To parse column-by-column, set the third argument to TRUE:

=TOCOL(B2:F10, 1, TRUE)

Excel reads all rows in Column B first, then advances to Column C, preserving chronological flow.

Flattening Data Horizontally into a Single Row with TOROW

When preparing header labels or horizontal inputs for chart categories, use TOROW to produce a continuous horizontal vector:

1. Select an empty row beneath your data (for example, A15).
2. Enter the formula to flatten matrix B2:D8 into a single row, ignoring blanks:

=TOROW(B2:D8, 1)

Combining TOCOL with UNIQUE and SORT for Clean Master Lists

The true power of TOCOL emerges when combined with other modern dynamic array functions:

1. To extract a sorted, deduplicated master list of product categories from a fragmented multi-column inventory matrix:

=SORT(UNIQUE(TOCOL(B2:E50, 1)))

This single formula flattens the range, eliminates duplicates, removes blank cells, and sorts the final list alphabetically, updating dynamically whenever source cells are modified.