Data analysts frequently encounter multi-row, multi-column grids—such as monthly departmental expenses, shift schedules, or product catalogues—that must be flattened into a single linear vector for database imports, pivot tables, or validation lists. Historically, transforming a two-dimensional grid into a single column required complex INDEX, SEQUENCE, and MOD calculations. Modern Excel provides TOCOL and TOROW to flatten matrices in a single step.
Understanding TOCOL and TOROW Syntax
Both functions transform arbitrary two-dimensional ranges into one-dimensional vectors:
=TOCOL(array, [ignore], [scan_by_column]): Transforms a matrix into a single continuous vertical column.=TOROW(array, [ignore], [scan_by_column]): Transforms a matrix into a single continuous horizontal row.
The optional [ignore] argument provides powerful filtering capabilities directly within the formula:
0: Keep all values (default).1: Ignore blank cells.2: Ignore errors (such as#N/Aor#VALUE!).3: Ignore both blanks and errors simultaneously.
Flattening a Grid by Rows vs Columns
By default, TOCOL reads data left-to-right across rows before moving to the next line. You can control traversal order using the third argument:
- To flatten a grid from
A2:D10row by row while stripping out all blank entries:=TOCOL(A2:D10, 1) - To scan top-to-bottom down columns instead, set
scan_by_columntoTRUE:=TOCOL(A2:D10, 1, TRUE) - Excel spills the unified single-column vector downwards automatically.
Creating Deduplicated Dynamic Validation Lists
Combining TOCOL with UNIQUE and SORT creates pristine dropdown lists from messy multi-column inputs:
=SORT(UNIQUE(TOCOL(B2:F50, 3)))
This formula flattens the range, ignores all blanks and formula errors, discards duplicate entries, and sorts the final list alphabetically. Mastering TOCOL and TOROW eliminates manual data unpivoting across your Excel workflows.