How to Turn a Table into a Single Column with TOCOL in Excel

Flatten a rota grid into one list, choose row-by-row or column-by-column order, skip blanks and list each person once.

You will learn to

• Flatten a grid into one column, skipping blanks
• Scan the grid column by column instead
• Produce a sorted list of distinct names from a grid

How it works

TOCOL(array, [ignore], [scan_by_column]) reads a two-dimensional range and returns every value in a single column. By default it reads row by row; set scan_by_column to TRUE to read down each column first. The ignore argument skips unwanted cells: 1 ignores blanks, 2 ignores errors and 3 ignores both.

Flattening is often the first step to analysing a grid. =SORT(UNIQUE(TOCOL(B2:D8,1))) turns a weekly rota into an alphabetical list of everyone on it — a task that used to need helper columns or copy-and-paste.

=TOCOL(B2:D8,1)