# How to convert a table into one column in Excel

Use TOCOL: `=TOCOL(B2:E4,1)` reads the table row by row into a single column, and the 1 skips empty cells.

**Excel:** `=TOCOL(B2:E4,1)` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Table | Seat 1 | Seat 2 | Seat 3 | Seat 4 |  | Guest list |
| Table 1 | Ana Ruiz | Raj Patel | Lee Chen | Kim Ortiz |  |  |
| Table 2 | Mo Diaz | Sam Cole |  | Eve Lund |  |  |
| Table 3 | Noor Ali | Ivy Shah | Tom Reyes |  |  |  |

Formula in G2: `=TOCOL(B2:E4,1)`

Result (spills 10 × 1): Ana Ruiz; Raj Patel; Lee Chen; Kim Ortiz; Mo Diaz; Sam Cole; Eve Lund; Noor Ali; Ivy Shah; Tom Reyes

## 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.

## Other ways

- `=TOCOL(B2:E4,1,TRUE)`: 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' 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-a-table-into-one-column