Flatten a Range to One Column (TOCOL)

Excel 365

To turn a grid into a single column — flattening a table for a drop-down, a mailing list, or further processing — use TOCOL. It reads the range and stacks every value into one column, and can skip the blanks.

Quick formula: to flatten B2:D10 into one column, ignoring blanks:

=TOCOL(B2:D10, 1)

The second argument 1 means “ignore blanks”; TOCOL reads left-to-right, top-to-bottom into a single column.

Functions used (tap for the full reference guide):

TOCOL TOROW

The example

A 2×2 grid flattened into one column.

	A	B	D
1	Q1	Q2	Flat
2	Apple	Banana	Apple
3	Cherry	Date	Banana
4			Cherry
5			Date

The formula

The grid as one column:

=TOCOL(A2:B3, 1) // Apple, Banana, Cherry, Date

How it works

TOCOL serializes the grid:

It reads the range in reading order — across each row, then down to the next — and stacks the values into one column.
The second argument controls what to skip: 0 keeps everything, 1 ignores blanks, 2 ignores errors, 3 ignores both.
A third argument TRUE scans by column instead of by row.
TOROW does the same but flattens into a single row.

Great for cleanup: TOCOL turns a messy block of names spread across columns into a tidy single-column list — then wrap in UNIQUE/SORT for a clean, deduped, alphabetized result.

Try it: interactive demo

Live demo

Toggle ignoring blanks for the grid {Apple, "", Banana} / {"", Cherry, Date}.

Ignore blanks

Column:

Variations

Flatten into a row

TOROW for a horizontal result:

=TOROW(B2:D10, 1)

Scan by column

Read down columns first:

=TOCOL(B2:D10, 1, TRUE)

Flatten, dedupe, sort

A clean single-column list:

=SORT(UNIQUE(TOCOL(B2:D10, 1)))

Pitfalls & errors

TOCOL needs Excel 365. Older versions lack it — use a helper layout or Power Query to unpivot.

Reading order matters. By default it goes across rows first. If you expected column order, add the TRUE scan-by-column argument.

Blanks vs errors. The ignore argument distinguishes them: 1 = blanks, 2 = errors, 3 = both. Pick the one you need.

Practice workbook

📊

Download the free Flatten a Range to One Column (TOCOL) practice workbook

TOCOL/TOROW examples (ignore blanks, scan by column, flatten+dedupe) with results shown, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I turn a range into a single column in Excel?

Use =TOCOL(range, 1). It stacks every value into one column in reading order and the 1 ignores blanks. Requires Excel 365.

How do I flatten a grid into one row instead?

Use TOROW the same way: =TOROW(range, 1) produces a single horizontal row of values.

How do I flatten and remove duplicates?

Nest the functions: =SORT(UNIQUE(TOCOL(range, 1))) flattens, removes duplicates, and sorts in one formula.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: TOCOL · TOROW