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 — 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 — 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
- Combine two or more cell
- Combine two or more cells with a line break
- Combine two or more cells and transpose
- Combine data from multiple Google Sheets tabs and get sheet/tab name
- Sum by month
- SUMIF cells if contains part of a text string
- Sum total sales based on quantity & price
- Combine date and time
- Convert 1-12 to month name
- Dynamic current date and time
- Count blank cell
- Count cell between two values
- Extract left before first space
- Extract the nth words in a text string
- Flip the first and last name
- SUMIF formulas and example
- SUMIFS formulas and example
- COUNTIF formulas and example
- IF formulas and example
- TEXT formulas and example
- CONVERT formulas and example
- Change the negative number to zero
- Change negative to positive numbers
- Calculate percentage changes for containing zero or negative values
- Count cells over 10 characters
- Count cells that are not blank
- Count if cells contain a specific text
- Count cells that begin with a specific text
- COUNTIF based on multiple criteria