This post will guide you how to convert cross table to list in Excel 2013/2016. How do I transpose cross table to flat table with VBA Macro in Excel.

1. VBA Macro for Transposing Cross Tab to Flat Table

CrossTab (cross table) is a two-dimensional table with a horizontal header row and a vertical header row. Assuming that you have a list of data in range A1:C5, and you want to convert this cross table or two dimensional table to a list or flat table. How to do it. You can  use VBA Macro to achieve the result quickly. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = iLastCol To 3 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 2).Value = .Cells(i, j).Value
.Cells(i, j).Value = ""
Next j
Next i
.Rows(1).Delete
End With
Application.ScreenUpdating = True
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Let’s see the last result:

2. Video: Transposing Cross Tab to Flat Table

This Excel video tutorial where we’ll unravel the secrets of transforming cross-tabulated data into a streamlined, flat table. In this session, we’ll focus on a powerful approach utilizing VBA Macros to effortlessly transpose your data.