In Excel: use =TOCOL(array, 3) — it flattens a two-dimensional grid into a single column, and the ignore argument decides what is dropped: 0 keeps everything, 1 skips blanks, 2 skips errors, 3 skips both.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
array | required | The range or grid to flatten. |
ignore | optional | 0 keep everything, 1 skip blanks, 2 skip errors, 3 skip both. |
scan_by_column | optional | TRUE reads down columns first; FALSE or omitted reads across rows. |
Related functions
Click a cell with enough empty rows below for the whole flattened result.
Type =TOCOL( and select the grid.
Add a comma and 3 to skip both blanks and errors — the usual choice.
Wrap the result in UNIQUE, SORT or FILTER as the analysis needs.
Need it as an auditable file?
Ships inside the linked template — formula-driven, unlocked, audit-ready.
What this does
TOCOL turns a rectangular range into one column and TOROW into one row. That sounds like a niche reshaping trick until you meet the problem it solves: lookup and aggregation functions want a single column, and data frequently arrives as a grid — a matrix of values by month across, product down. Flattening it makes UNIQUE, FILTER and COUNTIF work on it directly. The ignore argument is what makes the result usable, dropping the blanks and errors that a ragged grid is full of. Excel 365 only. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “tocol function in excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected formula shows up while it is still harmless. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That turns a calculation you can defend to a CFO or an auditor into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Flattening a 12×20 grid into one clean column, blanks and errors removed: =TOCOL(B2:M21, 3). A deduplicated list of every value that appears anywhere in a grid: =UNIQUE(TOCOL(B2:M21, 3)). Counting how many non-blank cells the grid holds: =ROWS(TOCOL(B2:M21, 1)). TOCOL turns a report-shaped grid back into analysable data, which is the step that makes every other array function apply to it. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.
In Google Sheets
Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. If you take one thing from this page on “tocol function in excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.
Common mistakes
- Omitting the ignore argument on a ragged grid, which fills the result with blanks and errors.
- Underestimating the output length — a 12×20 grid flattens to 240 rows and needs the room.
- Using them in Excel 2021 or earlier, where they do not exist.
Frequently asked questions
How do I turn a grid into a single column?
=TOCOL(range, 3) flattens it and skips blanks and errors.
Which order does it read in?
Across rows by default. Pass TRUE as the third argument to read down columns first.
Why do I need this?
Because UNIQUE, FILTER, COUNTIF and the lookup functions all expect a single column, and real data often arrives as a grid.