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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.· 6 days agoThank you, a lot, the formula option worked very well.. your saved my lot of time.
To post as a guest, your comment is unpublished.· 6 months agoI am using the Stack Multiple Columns Into One With Formula method and it worked perfectly, but I want it to maintain the formatting, i.e. some columns were bold & 1 was hyperlinks. Is there something that can be added to the formula to make this happen?
To post as a guest, your comment is unpublished.· 9 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.· 10 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.· 1 years 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.· 1 years 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.· 1 years 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.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 1 years agoyou are stacking multiple rows into one column, not multiple columns... not what I was looking for.
To post as a guest, your comment is unpublished.· 1 years agoMaybe this tutorial can help you.https://www.extendoffice.com/documents/excel/3327-excel-convert-multiple-rows-to-columns.html
To post as a guest, your comment is unpublished.· 1 years agoTo stack columns A-P that are in blocks of 6 rows put this formulae in cell R1 and fill down
To post as a guest, your comment is unpublished.· 1 years agoCancel that last one, missed the truncate step.
To post as a guest, your comment is unpublished.· 1 years agoDang, now it doesn’t work! Tried on a new tab but it’s now back to stacking column contents in one in stead of originally row content.
To post as a guest, your comment is unpublished.· 1 years agoThank you for posting this solution! In case anyone needs a space after/at the end of each block, just add an additional count to the rows, so going by the example above, instead of $A$6, write $A$7. It inputs a zero ‘0’ to the row but you can easily ‘Use selection for Find’ in Sublime to edit them out. If you know the solution which doesn’t add a zero, please post!?
To post as a guest, your comment is unpublished.· 1 years agoto output a blank cell instead of 0, do an IF function>0, TRUE-->output the original cell content, FALSE-->output ""
To post as a guest, your comment is unpublished.· 1 years agoThis works perfect, thanks