Excel TOROW and TOCOL in 2026: Reshape Any Array or Range in Seconds

Data almost never arrives in the shape you need it. You get a grid when you need a list, a list when you need a row, or a multi-column range when your formula expects a single vector. Before 2024, reshaping data in Excel meant writing workarounds with INDEX, OFFSET, or complex array formulas. In 2026, two elegant functions — TOROW and TOCOL — do this job instantly, with one line and zero complexity.

This guide covers both functions in depth: what they do, how their arguments work, and how to apply them to real-world data transformation challenges.

The Problem TOROW and TOCOL Solve

Imagine you have survey results spread across a 5-column, 20-row grid. You want to run SORT or UNIQUE across all the values at once. Both functions expect a single row or column — not a rectangular range. Previously, you had to either restructure your source data or write a clever workaround. Now you use TOROW or TOCOL to flatten the range first.

Or imagine you get a monthly sales file where all figures are in a single long column (120 rows for 12 months across 10 products). You want to reshape it into a 10-row by 12-column table. WRAPCOLS or WRAPROWS does this — but you first need TOCOL to ensure the source is clean before reshaping.

TOROW: Flatten a Range into a Single Row

Syntax

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

TOROW converts a multi-row, multi-column array into a single horizontal row.

Arguments

array: The range or array you want to flatten into a row.

ignore (optional): Controls what to skip. 0 = keep all values (default); 1 = ignore blanks; 2 = ignore errors; 3 = ignore both blanks and errors.

scan_by_column (optional): FALSE (default) reads left-to-right across each row before moving down. TRUE reads top-to-bottom down each column before moving right.

Basic Example

You have a 3×3 grid in A1:C3 containing the numbers 1 through 9. =TOROW(A1:C3) returns a single row: 1, 2, 3, 4, 5, 6, 7, 8, 9 — reading left to right across each row.

=TOROW(A1:C3, 0, TRUE) returns: 1, 4, 7, 2, 5, 8, 3, 6, 9 — reading top to bottom down each column first.

Practical Use: Combining Multiple Lists for Filtering

You have product categories in columns A, B, and C — each column holds a different region's product list. You want to get all unique products across all regions:

=UNIQUE(TOROW(A2:C20, 1))

The ignore argument of 1 skips blanks (important when lists have different lengths), and UNIQUE removes duplicates from the flattened result.

TOCOL: Flatten a Range into a Single Column

Syntax

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

TOCOL is the vertical counterpart to TOROW. It takes a multi-row, multi-column range and returns a single column.

The arguments are identical to TOROW:

array: The source range or array.

ignore: 0 (all), 1 (no blanks), 2 (no errors), 3 (neither blanks nor errors).

scan_by_column: FALSE reads row by row; TRUE reads column by column.

Basic Example

The same 3×3 grid in A1:C3. =TOCOL(A1:C3) returns a 9-row column: 1 through 9 in reading order (left-to-right across rows, then down).

=TOCOL(A1:C3, 0, TRUE) reads down each column first: 1, 4, 7, 2, 5, 8, 3, 6, 9 — now stacked vertically.

Practical Use: Consolidating Multi-Column Data for PivotBy

You have a sales dataset where monthly figures are spread across 12 columns (Jan through Dec) and 50 product rows. You want to use PIVOTBY or create a chart, but your formula needs a clean single column of values. TOCOL delivers:

=TOCOL(B2:M51, 1)

This collapses 600 cells into one clean column, skipping any blanks, ready for aggregation.

Combining TOROW and TOCOL with Other Functions

TOROW + SORT: Sort Across an Entire Grid

=SORT(TOROW(A1:E10, 1)) returns a sorted row of all non-blank values from the source grid. Add UNIQUE to also deduplicate: =UNIQUE(SORT(TOROW(A1:E10, 1))).

TOCOL + VSTACK: Merge Multiple Tables into One Column

If you have three separate product lists in different sheets and want one master column:

=TOCOL(VSTACK(Sheet1!A2:A20, Sheet2!A2:A20, Sheet3!A2:A20), 1)

VSTACK merges the three ranges vertically first; TOCOL then cleans up any gaps.

TOROW + WRAPROWS: Reshape a List into a Grid

If you have 60 values in a single column and want to reshape them into a 6-column grid:

=WRAPROWS(TOCOL(A1:A60, 1), 6)

This reads the column top to bottom, then wraps every 6 values into a new row. Combine with the ignore argument to handle sparse source data cleanly.

Nested LET + TOROW: Cleaner Multi-Step Transformations

Use LET to name each transformation step when the logic gets complex:

=LET(Raw, A1:E20, Flat, TOROW(Raw, 1), Sorted, SORT(Flat), UNIQUE(Sorted))

Each named step is clear, the transformations chain cleanly, and the result is a unique, sorted row of all non-blank values from the source grid.

TOROW vs TOCOL: Which One to Use?

Use TOROW when: your downstream formula expects a horizontal array (like filling a header row, or feeding TEXTJOIN for a comma-separated list).

Use TOCOL when: your downstream formula expects a vertical range (like SORT, UNIQUE, AVERAGEIF, or chart data sources that expect column vectors).

Direction matters: the scan_by_column argument changes the ORDER of values, not just the orientation. Think carefully about whether you want row-first or column-first reading order for your data.

Handling Errors in Source Data

Both functions accept errors in the source array by default (ignore = 0 passes errors through). If your source data might contain #N/A, #VALUE!, or other errors that would contaminate your output, use ignore = 2 or 3:

=TOROW(A1:F10, 2) — returns all values but drops error cells

=TOROW(A1:F10, 3) — returns only actual values, dropping both blanks and errors

This is particularly useful when flattening ranges that include VLOOKUP or XLOOKUP results, which may return #N/A for unmatched rows.

Availability and Compatibility

TOROW and TOCOL are available in Excel for Microsoft 365 (Windows and Mac), Excel on the web, and the Excel mobile apps on iOS and Android. They are not available in Excel 2019 or earlier perpetual license versions. If you share files with colleagues on older Excel versions, the functions will display as errors — consider using IFERROR wrappers or providing a static-value alternative for compatibility.

Conclusion: Two Functions That Belong in Every Data Workflow

TOROW and TOCOL are the kind of functions that seem small until you actually need them — and then they become indispensable. Wherever you work with data that arrives in the wrong shape, these two functions give you a one-line solution that would previously have required a workaround, a helper column, or a manual reshape.

In 2026, with Copilot in Excel generating formulas from natural language, you can even ask Copilot to write TOROW or TOCOL expressions for you: just describe the transformation you need in plain English and let it handle the formula. But understanding what these functions do — and the ignore and scan_by_column arguments — will help you verify and refine whatever Copilot suggests.

Start using TOROW and TOCOL in your next data project and share what you build in the comments. For more Excel function deep dives, explore the officelearner.net formula archive.