How to convert a table into one column in Excel
Formula (Excel and Google Sheets)
=TOCOL(B2:E4,1)Use TOCOL: =TOCOL(B2:E4,1) reads the table row by row into a single column, and the 1 skips empty cells.
- Result on the example
- a 10-row list that spills from G2 in
G2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
TOCOL reads the block across the first row, then the second, and so on, and returns one tall column. Its second argument says what to ignore: 1 skips blanks, 2 skips errors and 3 skips both. A third argument of TRUE reads down each column instead.
=TOCOL(B2:E4,1)Result{10 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …
B2:E43 rows × 4 cols: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …A range: 3 rows × 4 columns.
TOCOL(B2:E4,1){10 rows × 1 col} "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", …TOCOL: An array or range as one column, optionally skipping blanks and errors.- array
B2:E43 rows × 4 cols: "Ana Ruiz", "Raj Patel", "Lee Chen", "Kim Ortiz", … - ignore
1
- array
In Google Sheets
The same formula works in Google Sheets: =TOCOL(B2:E4,1). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 10 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TOCOL(B2:E4,1,TRUE) | spills 10 × 1Different result | Read column by column: everyone in seat 1 first, then seat 2. Ana Ruiz, Mo Diaz, Noor Ali, Raj Patel, … |
| =FLATTEN(B2:E4)Google Sheets | spills 12 × 1Different result | Google Sheets' older FLATTEN reads the same way but keeps the empty seats as blank cells. Includes two blank cells for the empty seats. |
Common mistakes
- Without the 1, empty cells come through as 0.
- TOCOL needs Excel 2024 or Microsoft 365; in Google Sheets it works too, and FLATTEN is the older option.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert a table into one column
G2, list every guest from the seating plan B2:E4 in one column, reading across each table, without the empty seats.Questions
How do I turn a column back into a table?
WRAPROWS does the reverse in Microsoft 365: =WRAPROWS(G2:G11,4) makes rows of four.
How do I keep the table name next to each guest?
Repeat the names to match: =TOCOL(IF(B2:E4="","",A2:A4&": "&B2:E4),1) skips nothing, so filter out the empty strings with FILTER.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.