Stack quarters
=TOCOL(B2:E10) lists every cell from four quarter columns into one vertical series.
What is TOCOL in Google Sheets?
TOCOL converts a range into a single column. You can choose whether it reads down each column first or across rows first. It replaces manual paste-special transpose chains when reshaping blocks for UNIQUE or FILTER.
When to use it
Use TOCOL to stack a matrix into one list, prep mail merge columns, or align multi-column selections for downstream formulas.
When to skip it
Skip TOCOL when you must preserve two-dimensional layout for charts. Keep the matrix or use HSTACK/VSTACK instead.
How it works
-
1
Pass the array to reshape as the first argument.
-
2
Set scan_by_column TRUE to read column by column, FALSE for row by row.
-
3
Allow spill space equal to rows times columns of the source.
-
4
Wrap with UNIQUE or FILTER for deduped or conditional lists.
-
5
Pair with TOROW when you need horizontal output instead.
-
6
Verify scan order matches your mental model on a 2x2 test block.
Examples in Google Sheets
By column order
=TOCOL(A1:C3, 1) reads each column top to bottom before moving right.
Feed UNIQUE
=UNIQUE(TOCOL(tags_matrix)) dedupes tags spread across a wide grid.
Build this without starting from a blank cell
Use a Better Sheets tool for TOCOL, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Wrong scan argument producing scrambled order vs expectation.
- TOCOL on entire sheet tabs with thousands of blank cells.
- Confusing TOCOL with TOROW orientation.
- Spilling over formulas that cannot move.
- Including formatted blank cells that look empty but contain spaces.
Frequently asked questions
- TOCOL vs FLATTEN?
- Both make one column. TOCOL offers explicit scan order control.
- TOCOL vs TOROW?
- TOCOL outputs vertical. TOROW outputs horizontal.
- Ignore blanks?
- FILTER after TOCOL or clean source range first.
- TOCOL with IMPORTRANGE?
- Yes on the imported block if dimensions are stable.
- Why wrong order?
- Check the scan_by_column second argument.
- TOCOL spill size?
- Product of rows and columns in the input range.
- TOCOL and HSTACK?
- Sometimes HSTACK then TOCOL reshapes blocks more clearly than one big range.
Related Tutorials
Watch how TOCOL works
Advanced Coding in Google Sheets for Programmers
Related blog posts
Guides that explain TOCOL in more depth.
Explore Coding in Google Sheets
Did you know you can actually code in Google Sheets?
Read post →Google Sheets Locales. What are they?
Hello, Better Sheet members and non-Better Sheet members! This tutorial is about every locale listed in Google Sheets. If you live in a country that is not well represented with blog posts or someone makes a blog post about a Google Sheets issue, they create a Google Sheet formula and then you copy and paste it into your sheet and it has the wrong delimiter. How do you resolve that? I'm going to show you how you can fix that and how you can know if there's a problem. Locales. What are the...
Read post →Related glossary terms
TOROW
TOROW flattens a range into a single row spill. Like TOCOL but horizontal, it helps build header arrays, SPARKLINE source rows, and inputs for functions that expect one horizontal vector.
Read guide →HSTACK
HSTACK appends arrays horizontally so columns sit side by side in one spilled table. It replaces fragile copy-paste when combining QUERY outputs, IMPORTRANGE blocks, or calculated columns with matching row heights.
Read guide →VSTACK
VSTACK stacks arrays on top of each other into one taller table. Combine regional exports, monthly snapshots, or FILTER results with the same columns without manual paste below last row.
Read guide →FLATTEN
FLATTEN converts a two-dimensional range into a single column, reading across rows then down. It is useful after SPLIT or when you need every value from a block in one list for UNIQUE, COUNTIF, or mail merge prep.
Read guide →Done reading about TOCOL?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.