Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.
doc stack columns to one 1

Stack multiple columns into one with formula

Stack multiple columns into one with VBA

Stack multiple columns into one with Transform Range good idea3

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


Stack multiple columns into one with formula


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:
doc stack columns to one 2

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:
doc stack columns to one 3

In the formula, MyData is the range name you have specified in step 1.

Stack multiple columns into one with VBA

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

doc stack columns to one 4

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:
doc stack columns to one 5

4. Click OK. Now the columns have been stacked in one column.
doc stack columns to one 6


Stack multiple columns into one with Transform Range

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:
doc stack columns to one 8

2. In the Transform Range dialog, check Range to single column option, and click Ok, then select a cell to place results. See screenshot:
doc stack columns to one 9

3. Click OK. Now the columns have been stacked into one single column.
doc stack columns to one 1


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tom · 3 months ago
    I 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.
      Sunny · 1 months ago
      Hi, Tom, which method you apply does not work? The method three, transform range tool must can work.
  • To post as a guest, your comment is unpublished.
    Chris Blackburn · 3 months ago
    Hi, 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.
      Sunny · 3 months ago
      Hi, Chris Blackburn, sorry I am affraid I cannot find a code can automatically run to update the result, maybe someone else can.
  • To post as a guest, your comment is unpublished.
    tom · 8 months ago
    Hi, is there a way to make the formula ignore any blank cells your data may contain?


    =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1),


    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.
      Sunny · 7 months ago
      Sorry, I am not good at formula, maybe you can try to use Kutools for Excel's Select Nonblank cells utility to select the unempty cells firstly, and copy and paste them to another location, then use above methods.
  • To post as a guest, your comment is unpublished.
    Gokce · 8 months ago
    I 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.
    badusha · 1 years ago
    Hello , is there any to convert above data to following form ??
    1
    A
    Lilly
    2
    B
    Judy
    ...