How to flatten a table in Excel

In most times of collecting and writing data tasks, you will use a spreadsheet software like Microsoft Excel. And you end up with a two dimensions table where each key has a dedicated column.

Of course, it is a simple table, easy to be read by humans. But if you want to transform it to a dataset or a list of keys/values, to be imported into a database system, then you must convert your 2D table to a flat table.

This tutorial will show you how to flatten a table in Excel, using VBA script :

Let’s take the table below as an example of 2D table :

Country	Capital	Region	Population
China	Beijing	Asia	1,411,778,724
India	New Delhi	Asia	1,379,579,515
United States	Washington, D.C.	Americas	332,040,249
Indonesia	Jakarta	Asia	271,350,000
Pakistan	Islamabad	Asia	225,200,000

And we want to transform it to a flat table like the table below :

Country	Key	Value
China	Capital	Beijing
China	Region	Asia
China	Population	1,411,778,724
India	Capital	New Delhi
India	Region	Asia
India	Population	1,379,579,515
United States	Capital	Washington, D.C.
United States	Region	Americas
United States	Population	332,040,249
Indonesia	Capital	Jakarta
Indonesia	Region	Asia
Indonesia	Population	271,350,000
Pakistan	Capital	Islamabad
Pakistan	Region	Asia
Pakistan	Population	225,200,000

To do this, open Excel and bring your data, in an existing workbook or it is better to put them in a new worksheet. And follow these steps :

• Go to the “Developer” tab and click on the “Visual Basic” icon, or just use the keyboard shortcut (Alt + F11).
• In the opened “Visual Basic for Applications”, go to the menu “Insert”, and select “Module”.
• Now in the new editor window, just paste this code :

Sub ReversePivotTable()
'   Before running this, make sure you have a summary table with column headers.
'   The output table will have three columns.
    Dim SummaryTable As Range, OutputRange As Range
    Dim OutRow As Long
    Dim r As Long, c As Long

    On Error Resume Next
    Set SummaryTable = ActiveCell.CurrentRegion
    If SummaryTable.Count = 1 Or SummaryTable.Rows.Count < 3 Then
        MsgBox "Select a cell within the summary table.", vbCritical
        Exit Sub
    End If
    SummaryTable.Select
    Set OutputRange = Application.InputBox(prompt:="Select a cell for the 3-column output", Type:=8)
'   Convert the range
    OutRow = 2
    Application.ScreenUpdating = False
    OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
    For r = 2 To SummaryTable.Rows.Count
        For c = 2 To SummaryTable.Columns.Count
            OutputRange.Cells(OutRow, 1) = SummaryTable.Cells(r, 1)
            OutputRange.Cells(OutRow, 2) = SummaryTable.Cells(1, c)
            OutputRange.Cells(OutRow, 3) = SummaryTable.Cells(r, c)
            OutputRange.Cells(OutRow, 3).NumberFormat = SummaryTable.Cells(r, c).NumberFormat
            OutRow = OutRow + 1
        Next c
    Next r
End Sub

• Click the “Run” button in the toolbar.
• A dialog box is opened now to choose a cell in your Excel’s workbook to show the result, go to Excel and choose a cell, whether in the same worksheet of your data or in a new worksheet, then click “OK”.
• Now in the choosen cell, you will see a result like the following :

Column1	Column2	Column3
China	Capital	Beijing
China	Region	Asia
China	Population	1,411,778,724
India	Capital	New Delhi
India	Region	Asia
India	Population	1,379,579,515
United States	Capital	Washington, D.C.
United States	Region	Americas
United States	Population	332,040,249
Indonesia	Capital	Jakarta
Indonesia	Region	Asia
Indonesia	Population	271,350,000
Pakistan	Capital	Islamabad
Pakistan	Region	Asia
Pakistan	Population	225,200,000