How to Use the TOCOL and WRAPROWS Dynamic Array Functions in Excel to Reshape Data Matrices

Restructuring and reformatting tabular data is one of the most frequent hurdles financial analysts, data managers, and accountants face in Microsoft Excel. Historically, converting a multi-column matrix into a single vertical column required cumbersome copy-pasting, complex INDEX/ROW/COLUMN formulas, or importing data into Power Query to unpivot columns. In Excel 365 and Excel 2024, Microsoft introduced dedicated array reshaping primitives: TOCOL (transform to column) and WRAPROWS. These dynamic array functions enable seamless two-way data transformation that recalculates automatically as raw data updates.

What Does the TOCOL Function Do?

The TOCOL function scans a two-dimensional grid or multi-column table and flattens all values into a single vertical column. The basic syntax is:

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

Parameter Breakdown:

• array: The source range or table to flatten (e.g., A2:D10).
• [ignore]: Specifies which cells to omit:
  • 0: Keep all values (default).
  • 1: Ignore blank cells.
  • 2: Ignore errors (such as #N/A or #DIV/0!).
  • 3: Ignore both blank cells and errors simultaneously.
• [scan_by_column]: Determines scanning direction:
  • FALSE (or omitted): Scans left-to-right across rows (row by row).
  • TRUE: Scans top-to-bottom down columns (column by column).

Practical Example: Flattening a Weekly Schedule into a Single List

Suppose you have a shift schedule where Monday through Friday tasks are spread across columns B2:F20, with several empty shifts and text cells. To extract a clean, single-column task roster that automatically excludes all empty slots:

=TOCOL(B2:F20, 1, FALSE)

Entering this single formula into cell H2 instantaneously spills a single unified column containing all active shifts without gaps.

What Does the WRAPROWS Function Do?

While TOCOL flattens a matrix into a list, WRAPROWS executes the inverse operation: it takes a flat one-dimensional list and wraps it into a multi-column matrix with a designated number of items per row. The syntax is:

=WRAPROWS(vector, wrap_count, [pad_with])

Parameter Breakdown:

• vector: The single row or column of source data.
• wrap_count: The maximum number of values in each row before wrapping down to the next row.
• [pad_with]: The value to display if the final row does not have enough items to complete the wrap count (defaults to #N/A).

Practical Example: Converting E-Commerce Raw Attributes into Structured Rows

Imagine you pasted a continuous stream of product data in column A (where every 3 rows represent SKU, Product Name, and Price). To convert this single 300-row column into a clean 3-column table:

=WRAPROWS(A2:A300, 3, "")

This formula arranges the data into three balanced columns (Column 1 = SKU, Column 2 = Product Name, Column 3 = Price), padding any incomplete trailing cells with an empty string rather than an unsightly #N/A error.

Chaining TOCOL and WRAPROWS with SORT and UNIQUE

The true power of modern dynamic arrays lies in function composability. You can nest TOCOL inside UNIQUE and SORT to generate deduplicated, alphabetized inventories from scattered datasets in one step:

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

This formula flattens the multi-column range A2:E50, filters out all blanks, eliminates duplicate records, and returns an alphabetized, clean single-column directory.