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.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
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 installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)
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.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoI am attempting to do this over a very large table (100s of columns and rows that are all linked to formulas. My excel will keep loading and then ultimately go to not responding. Is there a way to get past this? Thanks
To post as a guest, your comment is unpublished.· 3 months agoHi, I'm looking to go a step further with this. I've used Stack Multiple Columns Into One With VBA, code and this works. I've used Tables as the source with this code and I get the list. Is there a way to automate running the Macro? So that when I add a new entry into the table the Macro automatically runs and keeps the resulting list up to date?
To post as a guest, your comment is unpublished.· 8 months agoHi, is there a way to make the formula ignore any blank cells your data may contain?
I am using a helper sheet to create a contiguous list for a data validation drop down list. Thanks in advance.
To post as a guest, your comment is unpublished.· 8 months agoI have data which ends at the same row but multiple columns in which I want them to be stacked in a sequence where B column data will go under A column data where the data ends for A column and C column data to go under A column data where the data from B column ends and so on. This way I will have data from multiple columns to fit into one column. How can I do that?
To post as a guest, your comment is unpublished.· 1 years agoHello , is there any to convert above data to following form ??
To post as a guest, your comment is unpublished.· 9 months agoThanks for your leaving message, this tutorial https://www.extendoffice.com/documents/excel/5401-excel-stack-columns-into-one-column.html
is talking about how to solve this problem, you can go to view.