# How to combine multiple columns into one column in Google Sheets

Give FLATTEN each column in order: `=FLATTEN(A2:A5,B2:B5,C2:C5)` stacks them into one column.

**Google Sheets:** `=FLATTEN(A2:A5,B2:B5,C2:C5)`

**Excel:** Excel has no FLATTEN. In Excel 2024 or Microsoft 365, use `=TOCOL(A2:C5,1,TRUE)` or `=VSTACK(A2:A5,B2:B5,C2:C5)`.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Mon | Tue | Wed |  | Rota |
| Ana | Raj | Lee |  |  |
| Kim | Mo | Sam |  |  |
| Eve | Noor | Ivy |  |  |
| Tom | Ben | Cara |  |  |

Formula in E2: `=FLATTEN(A2:A5,B2:B5,C2:C5)`

Result (spills 12 × 1): Ana; Kim; Eve; Tom; Raj; Mo; Noor; Ben; Lee; Sam; Ivy; Cara

## How it works

FLATTEN takes any number of ranges and returns all their values in one column, in the order you list them. Giving it each column separately keeps each day's names together. Given one block, `FLATTEN(A2:C5)`, it reads across the rows instead.

## Other ways

- `=TOCOL(A2:C5,1,TRUE)`: TOCOL reads the block column by column with TRUE, and the 1 skips blanks.
- `=FLATTEN(A2:C5)`: One block is read row by row. (Ana, Raj, Lee, Kim, Mo, Sam, …)
- `=VSTACK(A2:A5,B2:B5,C2:C5)`: VSTACK stacks the columns the same way, in both apps.

## Common mistakes

- FLATTEN keeps empty cells as blank rows. If the columns have different lengths, use `=TOCOL(A2:C10,1,TRUE)` to skip the gaps.
- FLATTEN doesn't remove duplicates; wrap it in UNIQUE if a name can appear on several days.

## Questions

**How do I stack columns from different sheets?**

List each range: `=FLATTEN(Mon!A2:A20,Tue!A2:A20)`, then filter out the blanks.

**How do I go back from one column to several?**

Use WRAPCOLS: `=WRAPCOLS(E2:E13,4)` makes columns of four.

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/combine-multiple-columns-into-one