Spreadsheet Practice

How to combine multiple columns into one column in Google Sheets

Google Sheets formula

=FLATTEN(A2:A5,B2:B5,C2:C5)

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

Result on the example
a 12-row list that spills from E2 in E2
Excel
See below

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

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.

=FLATTEN(A2:A5,B2:B5,C2:C5)

Result{12 rows × 1 col} "Ana", "Kim", "Eve", "Tom", …

  1. A2:A54 rows × 1 col: "Ana", "Kim", "Eve", "Tom"

    A range: 4 cells in one column.

  2. B2:B54 rows × 1 col: "Raj", "Mo", "Noor", "Ben"

    A range: 4 cells in one column.

  3. C2:C54 rows × 1 col: "Lee", "Sam", "Ivy", "Cara"

    A range: 4 cells in one column.

  4. FLATTEN(A2:A5,B2:B5,C2:C5){12 rows × 1 col} "Ana", "Kim", "Eve", "Tom", …

    FLATTEN: Every value of one or more ranges in a single column, row by row.

    • range1A2:A54 rows × 1 col: "Ana", "Kim", "Eve", "Tom"
    • range2, ...B2:B54 rows × 1 col: "Raj", "Mo", "Noor", "Ben"
    • range2C2:C54 rows × 1 col: "Lee", "Sam", "Ivy", "Cara"

In 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).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=TOCOL(A2:C5,1,TRUE)spills 12 × 1TOCOL reads the block column by column with TRUE, and the 1 skips blanks.
=FLATTEN(A2:C5)spills 12 × 1Different resultOne block is read row by row. Ana, Raj, Lee, Kim, Mo, Sam, …
=VSTACK(A2:A5,B2:B5,C2:C5)spills 12 × 1VSTACK 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.

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.

Combine multiple columns into one column

In E2, stack the Monday, Tuesday and Wednesday columns into one column: all of Monday first, then Tuesday, then Wednesday.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.