How to Use the TOCOL, TOROW, and WRAPROWS Array Shaping Functions in Microsoft Excel

Data imported from legacy databases, PDF tables, or external web scrapes often arrives formatted as irregular two-dimensional matrices, or conversely, as single continuous unformatted text columns. Previously, transforming a multi-row matrix into a single column required complex INDEX, INT, and MOD formula gymnastics, or manual copy-pasting. Microsoft Excel introduced modern array-shaping functions—specifically TOCOL, TOROW, WRAPROWS, and WRAPCOLS—that reshape data dimensions dynamically using single formula expressions.

Understanding the TOCOL Function and Syntax

The TOCOL function transforms an arbitrary two-dimensional array or table of values into a single vertical column:

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

• array: The source range or table (e.g., A2:D10).
• [ignore]: Controls whether blanks and errors are stripped out:
  • 0 (Default): Keep all values.
  • 1: Ignore blank cells.
  • 2: Ignore errors (such as #N/A).
  • 3: Ignore both blanks and errors simultaneously.
• [scan_by_column]: Boolean governing traversal order. Set to FALSE (or omit) to scan left-to-right by row; set to TRUE to scan top-to-bottom by column.

Flattening a Matrix and Removing Empty Cells with TOCOL

Consider a schedule matrix in B2:F20 containing scattered employee shifts and blank vacation days. To consolidate all assigned names into a single clean list without empty cells:

=TOCOL(B2:F20, 1)

This single formula immediately spills a clean, continuous vertical list containing only populated names. Nesting with SORT and UNIQUE creates an instant master roster:

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

Understanding the TOROW Function for Horizontal Vectors

The TOROW function performs the exact mirror operation of TOCOL, transforming any two-dimensional grid into a single horizontal row:

=TOROW(A2:D5, 3)

This flattens the range A2:D5 into a single row, automatically dropping blank cells and error values.

Structuring Flat Columns into Grids with WRAPROWS

When data exports dump product catalogs or transaction streams into a single massive vertical list where every three consecutive cells represent Part Number, Description, and Price, use WRAPROWS to reassemble the data into a three-column table:

=WRAPROWS(vector, wrap_count, [pad_with])

• vector: The single row or column of input data (e.g., A2:A300).
• wrap_count: The maximum number of values in each row before wrapping to the next line (e.g., 3).
• [pad_with]: The value to display in empty cells if the input vector does not divide evenly (e.g., "" or "N/A").

To convert the single column A2:A100 into a clean 4-column matrix:

=WRAPROWS(A2:A100, 4, "")

Excel outputs rows of four items each, automatically advancing to the next row until the entire dataset is structured.

Vertical Table Formatting with WRAPCOLS

Similarly, WRAPCOLS takes a flat vector and wraps it vertically into multiple columns of a designated height (e.g., creating printable multi-column badge sheets or directory listings):

=WRAPCOLS(A2:A60, 15, "")

This generates four side-by-side columns, each containing exactly 15 rows of data.