Does TOCOL modify the original data, or does it create a new array?

TOCOL creates a new dynamic array; the source range is not changed. It reads values from the input and outputs them reorganized as a single column. The original data remains intact.

What's the practical difference between scan_by_column=FALSE and TRUE?

FALSE reads left-to-right across each row before moving to the next row. TRUE reads top-to-bottom down each column before moving to the next column. Choose TRUE when you want values grouped by column (e.g., all rep names first, then all units), and FALSE when you want them grouped by row.

Can I use TOCOL to extract a single column from a 2D table?

No; TOCOL flattens the entire array. To extract a specific column, use CHOOSECOLS(array, column_index) first, then pipe to TOCOL if you need to guarantee column output format for downstream formulas.

Why use TOCOL on data that's already a column?

In complex nested formulas, intermediate results may return either 1D or 2D arrays depending on inputs. TOCOL ensures consistent column-only output format, preventing errors in downstream functions that expect single-column data.

TOCOL function

Flattens an array into a single column, optionally ignoring blanks or errors and reading row-by-row or column-by-column.

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

Generate a TOCOL formula

Describe what you need. The generator will reach for TOCOL where TOCOL is the right tool, and tell you when it is not.

Arguments

How TOCOL reads its arguments

Argument	Required	Description
array	Required	Required. The array or range to convert to column orientation; can be any shape (1D or 2D).
ignore	Optional	Optional. 0 (default) includes all values, 1 ignores blanks, 2 ignores errors, 3 ignores both blanks and errors.
scan_by_column	Optional	Optional. FALSE (default) scans row-by-row (left-to-right, top-to-bottom); TRUE scans column-by-column (top-to-bottom, left-to-right).

Returns

A single-column dynamic array containing all values from the input array in flattened form.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Flatten an entire sales data range into a single column

Region	Rep	Units	Unit Price	Order Date
East	Sarah	5	29.99	2024-01-15
West	Mike	3	49.99	2024-01-16
South	Lisa	8	19.99	2024-01-17
North	Jake	2	79.99	2024-01-18

=TOCOL(B2:F5)

Result: EastSarah529.992024-01-15WestMike349.992024-01-16SouthLisa819.992024-01-17NorthJake279.992024-01-18

TOCOL converts the 4×5 range into a single column of 20 values by reading row-by-row (the default). Each row's values are appended to the column in sequence from left to right, then the next row begins.

2. Flatten sales data while skipping blank cells

Region	Rep	Units	Unit Price	Order Date
East	Sarah	5	29.99	2024-01-15
West		3	49.99	2024-01-16
South	Lisa	8	19.99	2024-01-17
North	Jake	2	79.99	2024-01-18

=TOCOL(B2:F5, 1)

Result: EastSarah529.992024-01-15West349.992024-01-16SouthLisa819.992024-01-17NorthJake279.992024-01-18

The ignore parameter set to 1 filters out blank cells during flattening. The missing rep name in row 3 is skipped, producing 19 values instead of 20. Useful for cleaning sparse datasets before further processing.

3. Flatten by reading down columns first instead of across rows

Rep	Units
Sarah	5
Mike	3
Lisa	8
Jake	2

=TOCOL(C2:D5,, TRUE)

Result: SarahMikeLisaJake5382

With scan_by_column set to TRUE, TOCOL reads the first column (Rep) top-to-bottom, then the second column (Units) top-to-bottom. This produces a different order than row-by-row scanning, which would interleave the values.

Common errors

Which TOCOL error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The array argument is invalid, contains an incompatible data type, or an ignore/scan_by_column parameter is outside its valid range.	Verify the array is a valid range reference (e.g., A1:C10) or dynamic array. Ensure ignore is 0–3 and scan_by_column is TRUE or FALSE.
#SPILL!	The output column would extend beyond the worksheet boundaries, or existing data blocks the spill range below the formula.	Delete or move blocking data, or place the formula closer to the end of the spreadsheet. Ensure there are enough empty rows for the flattened column.
#REF!	The array argument references a range that has been deleted, moved to another workbook, or contains an invalid reference.	Update the array reference to point to a valid, existing range within the current workbook.

Tips and when to use something else

Combine TOCOL with UNIQUE to both flatten and deduplicate in a single formula: =UNIQUE(TOCOL(...)).
Use ignore=3 to filter blanks and errors simultaneously—efficient for messy, real-world datasets.
Need the opposite? Use TOROW() to convert arrays to row format instead of column format.
TOCOL is essential after VSTACK or HSTACK when downstream functions require a guaranteed single-column result, not a 2D array.

Related functions

TOROWTOROW converts a multi-dimensional array into a single horizontal row, arranging all elements left-to-right, top-to-bottom by default.UNIQUEReturns all distinct values from an array, removing duplicates by row or column; use it to extract clean, unique item lists from repetitive data.VSTACKVSTACK combines multiple arrays or ranges by stacking them vertically into a single array, placing each input one below the other.HSTACKHSTACK combines multiple arrays horizontally (side by side) into a single rectangular array, expanding shorter arrays with blanks to create a unified output.CHOOSECOLSCHOOSECOLS returns the selected columns from a given array, preserving order and data types, and spills the result into adjacent cells.

Frequently asked questions

Does TOCOL modify the original data, or does it create a new array?: TOCOL creates a new dynamic array; the source range is not changed. It reads values from the input and outputs them reorganized as a single column. The original data remains intact.
What's the practical difference between scan_by_column=FALSE and TRUE?: FALSE reads left-to-right across each row before moving to the next row. TRUE reads top-to-bottom down each column before moving to the next column. Choose TRUE when you want values grouped by column (e.g., all rep names first, then all units), and FALSE when you want them grouped by row.
Can I use TOCOL to extract a single column from a 2D table?: No; TOCOL flattens the entire array. To extract a specific column, use CHOOSECOLS(array, column_index) first, then pipe to TOCOL if you need to guarantee column output format for downstream formulas.
Why use TOCOL on data that's already a column?: In complex nested formulas, intermediate results may return either 1D or 2D arrays depending on inputs. TOCOL ensures consistent column-only output format, preventing errors in downstream functions that expect single-column data.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17