How to stack columns of data into one column in Excel

Problem

In Excel, from time-to-time you may want to stack different columns of data into one column. For example, if cell A1 has the value of Bob, A2 has Jane, and B1 has Mary and B2 has Sue, you may want to collapse all the values from both A and B columns into one column. The result would be: A1 will contain Bob, A2 will contain Jane, A3 will contain Mary, A4 will contain Sue. The following procedure will help you do so. This code also includes a function to check whether a worksheet exists.

Solution

To run this code follow these steps:

  1. Press Alt + F11 to bring up the VBA editor
  2. Insert a new module in your workbook using the Insert menu option from the toolbar and clicking Module
  3. Copy and paste this code
  4. Go back to worksheet with data in it
  5. Press Alt + F8 to bring the macro window
  6. Select the Stack_cols procedure and hit run
  7. Enter the new worksheet name in the input box
  8. If everything went well, you should have a new worksheet with all the data from original worksheet in one column with the column headers. See the screen shots for example.

Before stacking-the original data:

cells before stacking columns in Excel

before applying stack cols function

After stacking:

After stacking columns in Excel

after applying the stack cols function

Here’s the code:

Option Explicit
 
Sub Stack_cols()
 
    On Error GoTo Stack_cols_Error
 
    Dim lNoofRows As Long, lNoofCols As Long
    Dim lLoopCounter As Long, lCountRows As Long
    Dim sNewShtName As String
    Dim shtOrg As Worksheet, shtNew As Worksheet
 
    'Turn off the screen update to make macro run faster
    Application.ScreenUpdating = False
    'Ask for a new sheet name, if not provided use newsht
    sNewShtName = InputBox("Enter the new worksheet name", "Enter name", "newsht")
    'Set a sheet variable for the sheet where the data resides
    Set shtOrg = ActiveSheet
    'Add a new worksheet, rename it and set it to a variable
    If Not SheetExists(sNewShtName) Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sNewShtName
        Set shtNew = Worksheets(sNewShtName)
    Else
        MsgBox "Worksheet name exists. Try again", vbInformation, "Sheet Exists"
        Exit Sub
    End If
 
    With shtOrg
        'Get the last column number
        'Replace .Range("IV1") with .Range("XFD1") for Excel 2007
        lNoofCols = .Range("IV1").End(xlToLeft).Column
        'Start a loop to copy and paste data from the first column to the last column
        For lLoopCounter = 1 To lNoofCols
        'Count the number of rows in the looping column
            'Replace .Cells(65536, lLoopCounter) with .Cells(1048576, lLoopCounter) for Excel 2007
            lNoofRows = .Cells(65536, lLoopCounter).End(xlUp).Row
            .Range(.Cells(1, lLoopCounter), .Cells(lNoofRows, lLoopCounter)).Copy Destination:=shtNew.Range(shtNew.Cells(lCountRows + 1, 1), shtNew.Cells(lCountRows + lNoofRows, 1))
            'count the number of rows in the new worksheet
            lCountRows = lCountRows + lNoofRows
        Next lLoopCounter
    End With
 
    On Error GoTo 0
SmoothExit_Stack_cols:
        Application.ScreenUpdating = True
        Exit Sub
 
Stack_cols_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub:Stack_cols"
    Resume SmoothExit_Stack_cols
End Sub
'Check if a worksheet exists or not
Public Function SheetExists(sShtName As String) As Boolean
    On Error Resume Next
 
    Dim wsSheet As Worksheet, bResult As Boolean
    bResult = False
    Set wsSheet = Sheets(sShtName)
 
    On Error GoTo 0
    If Not wsSheet Is Nothing Then
        bResult = True
    End If
    SheetExists = bResult
End Function

Update: I’ve uploaded a video on YouTube of a walk through of this process of stacking columns in Excel. Here it is:

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

Leave a Reply 31 comments