Combine multiple columns into one single column in Excel and Google Sheets

=OFFSET($A$2,CEILING(ROW(A2)/COLUMNS(A2:B5),1)-1,MOD(ROW(A2)-1+COLUMNS(A2:B5),2))

$A$2 = fixed cell; A2 = dynamic cell; A2:B5 = data range

Check below for a detailed explanation with pictures and how to use formulas in Excel and Google Sheets.

Combine multiple columns into one single column in Excel

How to combine multiple columns into one single column in Excel?
Combine multiple columns into one single column in Excel

COMBINE MULTIPLE COLUMNS INTO ONE SINGLE COLUMN — EXCEL FORMULA AND EXAMPLE

=OFFSET($A$2,CEILING(ROW(A2)/COLUMNS(A2:B5),1)-1,MOD(ROW(A2)-1+COLUMNS(A2:B5),2))

    • $A$2 = fixed cell, where data starts

    • A2 = dynamic cell where data starts

    • 2 = number of columns

    • A2:B5 = data range, you can also use columns instead of a range

=OFFSET($A$2,ROUNDUP(ROWS($1:1)/2,0)-1,MOD(ROWS($1:1)-1,2))

  • $A$2 = fixed cell, where data starts

  • $1:1 = fixed column and dynamic row

  • 2 = number of columns

💡 Download the Excel file used in this exercise here. It's much easier and simpler to combine in Google Sheets. The example below and recommended to do it in Google Sheets

Combine multiple columns into one single column in Google Sheets

How to combine multiple columns into one single column in Google Sheets?
Combine multiple columns into one single column in Google Sheets

COMBINE MULTIPLE COLUMNS INTO ONE SINGLE COLUMN — GOOGLE SHEETS FORMULA AND EXAMPLE

=TRANSPOSE(SPLIT(TEXTJOIN(",",1,A2:B5),","))

  • A2:B5 = data cell, you can also use columns instead of a range and the empty cell will be ignored as is shown in the above example

💡 Download the Excel file used in this exercise here. It's much easier and simpler to combine in Google Sheets. The example below and recommended to do it in Google Sheets