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

Restructuring two-dimensional data grids into flat single-column lists or wrapping long vertical tables into multi-column matrices historically required labor-intensive manual copying, transposing, or complex combinations of INDEX, ROW, COLUMN, and MOD calculations. Microsoft Excel includes dedicated array transformation functions: TOCOL (converts 2D grids into a single vertical column), TOROW (converts grids into a single horizontal row), and WRAPROWS (wraps a 1D vector into a multi-column table of fixed width).

Syntax and Argument Breakdown

These dynamic array functions evaluate arrays automatically and spill results into adjacent empty cells:

=TOCOL(array, [ignore], [scan_by_column])
=TOROW(array, [ignore], [scan_by_column])
=WRAPROWS(vector, wrap_count, [pad_with])

Parameter	Function Role	Configuration Options
ignore	Error and blank filtering	0 (Keep all), 1 (Ignore blanks), 2 (Ignore errors), 3 (Ignore blanks and errors).
scan_by_column	Directional traversal order	FALSE (Default: Read left-to-right by row), TRUE (Read top-to-bottom by column).
wrap_count	Target column width	Positive integer specifying the number of values per row.
pad_with	Missing value filler	Text or value used when the final row is incomplete (defaults to #N/A).

Example 1: Flattening a 2D Matrix into a Clean Column with TOCOL

Suppose you have quarterly sales data organized in a 4-column matrix across range B2:E10 containing empty cells and occasional formula errors (e.g., #DIV/0!).

To collapse this matrix into a continuous single vertical list while eliminating blank cells and errors:

=TOCOL(B2:E10, 3)

How it executes:

1. Excel reads through the matrix row by row.
2. Setting ignore = 3 automatically strips out all blank entries and calculation errors.
3. The formula dynamically spills a perfectly clean, consolidated vertical list of numbers.

Example 2: Reading Columns Top-to-Bottom

If your source data is organized chronologically by column rather than by row, set scan_by_column = TRUE:

=TOCOL(B2:E10, 1, TRUE)

This processes Column B completely from top to bottom before advancing to Column C, preserving chronological series order.

Example 3: Reshaping a Single List into a Multi-Column Grid with WRAPROWS

Suppose column A contains a continuous flat list of employee records exported from an HR database: Name, Department, Email, Extension, Name, Department, Email, Extension…

To convert this 1D list into a structured 4-column table where each employee occupies a dedicated row:

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

How it behaves:

1. Excel places the first 4 items into Row 1 across Columns 1 through 4.
2. It wraps the next 4 items into Row 2, repeating until the vector is exhausted.
3. Setting pad_with = "" ensures that if the source list does not divide evenly by 4, remaining cells are filled with clean blank strings rather than unsightly #N/A errors.

Pairing with SORT and UNIQUE for Instant Summary Reports

Because these functions output native dynamic arrays, you can nest them directly inside other array operations without helper cells:

=SORT(UNIQUE(TOCOL(A2:D50, 1)))

This single formula extracts all unique non-blank values across a multi-column range and sorts them alphabetically in ascending order, demonstrating the speed and elegance of modern dynamic Excel formulas.