The Problem with Multi-Dimensional Arrays
When working with large datasets in Google Sheets, you often encounter two-dimensional (2D) arrays—tables consisting of multiple rows and columns. While this format is excellent for human readability, it creates significant problems if you need to feed that data into a drop-down menu, a chart, or a mathematical function like UNIQUE or SORT, which generally expect a single, flat list.
Historically, flattening a 2D table into a single column required complex, nested formulas combining INDEX, ROW, COLUMN, and MOD. Google Sheets has entirely eliminated this complexity with the introduction of the TOCOL function.
Understanding the Syntax
The syntax for the TOCOL function is straightforward but offers powerful optional arguments:
=TOCOL(array_or_range, [ignore], [scan_by_column])
- array_or_range: The 2D range of data you want to flatten (e.g., A1:C5).
- ignore (Optional): A numeric code that tells Google Sheets whether to ignore blanks or errors.
0: Keep all values (Default).1: Ignore blank cells.2: Ignore errors (like#N/Aor#DIV/0!).3: Ignore both blanks and errors.
- scan_by_column (Optional): A TRUE/FALSE boolean that determines the order in which the data is extracted.
FALSE: Reads the data row by row, left to right (Default).TRUE: Reads the data column by column, top to bottom.
Example 1: Flattening a Basic Table
Imagine you have a list of employee names assigned to different shifts. Column A has Morning shift names, Column B has Afternoon shift names, and Column C has Night shift names (range A2:C10).
If you want to create a single master list of all employees to use as a data validation drop-down, simply write:
=TOCOL(A2:C10)
Google Sheets will immediately output a single column. By default, it will list the first Morning person, then the first Afternoon person, then the first Night person, moving row by row.
Example 2: Ignoring Blank Cells
In the previous example, if some shifts have fewer people assigned than others, your range (A2:C10) will contain empty cells. The standard TOCOL function will output these empty cells as ugly “0”s or blank gaps in your final list.
To fix this, utilize the ignore argument by setting it to 1:
=TOCOL(A2:C10, 1)
Now, Google Sheets will seamlessly skip over any empty cells, producing a perfectly clean, continuous vertical list.
Example 3: Sorting a Flattened Array
The true power of TOCOL is how easily it nests inside other dynamic array functions. If you want that master list of employees to be in alphabetical order, and you want to ensure there are no duplicate names (if someone worked a double shift), you can wrap the TOCOL function in SORT and UNIQUE:
=SORT(UNIQUE(TOCOL(A2:C10, 1)))
This single formula takes a messy, 2D schedule, flattens it, removes all blank spaces, deletes any duplicate entries, and alphabetizes the final output. This replaces what would otherwise require a complicated Apps Script macro.