How to quickly stack multiple columns into one column in Excel?
in Excel, the Text to columns function can split a cell into multiple columns based on delimiter, but if there are any ways to stack multiple columns into one column as below screenshot shown? In this article, I will introduce some tricks on solving this task quickly.
1. Select the range you want to stack, and go to the Name Box to give this range a name, and press Enter key. See screenshot:
2. Then select a blank cell that you want place the results, enter this formula =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1), press Enter key and drag fill handle down until an error displayed. See screenshot:
Here is a VBA code that can help you too.
1. Press Alt + F11 keys to display Microsoft Visual Basic for Applications window.
2. Click Insert > Module, paste below code to the Module.
VBA: Stack columns to one
Sub ConvertRangeToColumn() 'UpdatebyExtendoffice Dim Range1 As Range, Range2 As Range, Rng As Range Dim rowIndex As Integer xTitleId = "KutoolsforExcel" Set Range1 = Application.Selection Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8) rowIndex = 0 Application.ScreenUpdating = False For Each Rng In Range1.Rows Rng.Copy Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True rowIndex = rowIndex + Rng.Columns.Count Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
3. Press F5 key to run the code, a dialog pops out to select the source data, and click OK, to select a cell to place the results. See screenshot:
4. Click OK. Now the columns have been stacked in one column.
To apply Kutools for Excel’s Transform Range utility is also can help you quickly solve this problem.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
1. Select the columns data you use, and click Kutools > Range > Transform Range. See screenshot:
2. In the Transform Range dialog, check Range to single column option, and click Ok, then select a cell to place results. See screenshot:
3. Click OK. Now the columns have been stacked into one single column.