The Excel TOCOL function flattens any range or array into a single column. It’s the fastest way to turn a grid — a schedule, a matrix, a multi-column dump — into a clean list you can sort, de-duplicate, count, or feed into FILTER and XLOOKUP.
1 means "ignore blanks" — leave it out and every empty cell becomes a 0 in your list.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to flatten. |
ignore | Optional | What to skip: 0 = keep all values (default) · 1 = ignore blanks · 2 = ignore errors · 3 = ignore blanks and errors. |
scan_by_column | Optional | FALSE = read row by row, left to right (default). TRUE = read column by column, top to bottom. |
The blank-cells-become-zero trap: with the default ignore of 0, every empty cell in the grid lands in your list as a 0. For real-world data, =TOCOL(range, 1) is almost always what you want.
Flatten a grid into one clean list
A weekly staffing grid sits in A1:C4 — names scattered across three day-columns, with one slot unfilled. One formula in E2 turns it into a single roster:
| A | B | C | E | ||
|---|---|---|---|---|---|
| 1 | Mon | Tue | Wed | One column | |
| 2 | Ava | Ben | Cleo | Ava | |
| 3 | Dan | Eli | Ben | ||
| 4 | Fay | Gus | Hana | Cleo | |
| 5 | Dan | ||||
| 6 | Eli | ||||
| 7 | Fay | ||||
| 8 | Gus | ||||
| 9 | Hana |
By default TOCOL reads the grid row by row: Ava, Ben, Cleo, then Dan, Eli… Set the third argument to TRUE to read down each column instead:
Try it: interactive TOCOL demo
Flatten a 3×3 grid that contains a blank cell and a #DIV/0! error. Switch the ignore mode and scan direction and watch the output list change.
The real power: unpivot a grid for analysis
Grids are great for humans and terrible for formulas. COUNTIF, UNIQUE, SORT, and pivot-style analysis all want a flat list — and TOCOL delivers one without touching the original layout:
TOCOL also pairs with WRAPROWS as a reshaping duo: flatten a grid with TOCOL, then re-wrap it to a different width:
Errors & common pitfalls
Pitfall: blanks become zeros. The default ignore of 0 keeps every cell — and empty cells come through as 0. Use ignore 1 (or 3) to drop them.
#SPILL! — no room to land. A big grid makes a long column. Make sure the cells below the formula are empty — or flatten with ignore 1 or 3 to shorten the list.
#NAME? — older Excel. TOCOL requires Excel for Microsoft 365 or Excel 2024+. In older versions the name isn’t recognized.
Pitfall: errors ride along. With ignore 0 or 1, any error cell in the grid appears in your list and can poison downstream formulas like SUM. Use ignore 2 or 3 to drop errors.
Pitfall: wrong reading order. If your flattened list looks shuffled, you probably need the other scan direction — flip scan_by_column between FALSE (row by row) and TRUE (column by column).
Practice workbook
Frequently asked questions
What do the ignore values 0-3 mean in TOCOL?
0 keeps every value (blanks become 0), 1 ignores blanks, 2 ignores errors, 3 ignores both blanks and errors. The default is 0.Why does TOCOL show zeros where my grid has empty cells?
ignore mode 0, which keeps all cells — and an empty cell’s value is 0. Use =TOCOL(range, 1) to skip blanks.What's the difference between TOCOL and TRANSPOSE?
In what order does TOCOL read the grid?
=TOCOL(range, 1, TRUE).Which Excel versions have TOCOL?
#NAME?.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class