How to Use the TOCOL and TOROW Functions to Flatten Arrays in Microsoft Excel

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/A or #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:

  1. To flatten a grid from A2:D10 row by row while stripping out all blank entries:
    =TOCOL(A2:D10, 1)
  2. To scan top-to-bottom down columns instead, set scan_by_column to TRUE:
    =TOCOL(A2:D10, 1, TRUE)
  3. 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.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.