# TOCOL function in Excel

> TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column.

Source: https://spreadsheetpractice.com/functions/tocol · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column.
- **Syntax:** `=TOCOL(array, [ignore], [scan_by_column])`
- **Most common use:** `=TOCOL(A2:C3,1)` returns Ana (Row by row, blanks skipped)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** A one-column array that spills down.
- **Google Sheets:** Available in Google Sheets.

## What it does

TOCOL turns a range or array into a single column, reading across each row unless you ask for column by column. Its ignore argument can skip blanks, errors or both. Use it to stack a grid of names into one list and then feed that list to UNIQUE, SORT or COUNTA.

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to flatten. |
| `ignore` | Optional | Optional: 0 keeps everything (default), 1 skips blanks, 2 skips errors, 3 skips both. |
| `scan_by_column` | Optional | Optional: TRUE reads down each column first; FALSE (default) reads across each row. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | `=TOCOL(A2:C3,1)` | Ana (spills 4 × 1) | Row by row, blanks skipped |
| F2 | `=TOCOL(A2:C3,1,TRUE)` | Ana (spills 4 × 1) | Column by column instead |
| G2 | `=COUNTA(UNIQUE(TOCOL(A2:C3,1)))` | 3 | 3 different people on the rota |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Mon | Tue | Wed |  |  |  |  |
| 2 | Ana | Raj |  |  | Ana | Ana | 3 |
| 3 | Lee |  | Ana |  | Raj | Lee |  |
| 4 |  |  |  |  | Lee | Raj |  |
| 5 |  |  |  |  | Ana | Ana |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| Zeros where blanks were | Blank cells come through as 0. Set ignore to 1 to skip them. |
| #SPILL! | The cells below aren't empty. |
| The order is wrong | TOCOL reads across each row by default. Set scan_by_column to TRUE to read down each column. |

## Tips

- =UNIQUE(TOCOL(A2:F20,1)) lists every distinct entry in a grid.

## Related functions

- [TOROW](https://spreadsheetpractice.com/functions/torow): TOROW turns a range or array into a single row, reading across each row unless you ask for column by column.
- [UNIQUE](https://spreadsheetpractice.com/functions/unique): UNIQUE returns each distinct value in a range once, in the order it first appears.
- [TRANSPOSE](https://spreadsheetpractice.com/functions/transpose): TRANSPOSE flips a range so rows become columns and columns become rows.

## Questions

### How do I turn a range into one column in Excel?

=TOCOL(A2:D10) stacks every row into one column. Add 1 as the second argument to skip blanks.

### How is TOCOL different from TRANSPOSE?

TRANSPOSE swaps rows and columns but keeps the grid; TOCOL turns any shape into a single column.

## Practice

A graded TOCOL exercise ("One list of names") is on [the TOCOL page](https://spreadsheetpractice.com/functions/tocol). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.