Unpivottables (Unpivot)

Description

Pivot tables (pivot tables) are a powerful tool in data analysis. However, sometimes you need to deploy (unpivot) aggregated data (otherwise known as "straightening" or uncollapsing the data) to put it into a regular table format so that all similar values are in the same column. This is necessary, for example, to create a new or modified chart or report.

You can perform this conversion in Excel using the Power Query tool, but it is not fast and not very convenient. Let's try a different approach to roll out summary data without Power Query.

Formulation of the problem

Imagine that we need to convert some data set from a pivot table to a flat table (Unpivot):

Such a task is better solved with the help of a macro. Let's write our own procedure that will be able to convert a cross table (cross table) with data into a regular (flat) table format, which will allow us again create pivot tables with changed or supplemented data.

VBA code for the UnPivotTable procedure

Open the editor Visual Basic ( Service - Macro - Editor Visual Basic ), insert the new module (Insert - Module) and copy the text of this macro there:

Sub UnPivotTable()
  'moonexcel.com.ua
  Dim InVal    As Variant
  Dim OutVal() As Variant
  Dim j, k, i  As Long
  Dim NewSheet
  
  i = 1
  InVal = Selection.Formula
  ReDim OutVal(1 To Selection.Count, 1 To 3)
  
  For j = 2 To UBound(InVal, 1)
    For k = 2 To UBound(InVal, 2)
      If InVal(j, k) <> "" Then
        OutVal(i, 1) = InVal(j, 1)
        OutVal(i, 2) = InVal(1, k)
        OutVal(i, 3) = InVal(j, k)
        i = i + 1
      End If
    Next k
  Next j
  
  Set NewSheet = Worksheets.Add
  NewSheet.Range("A1").Resize(UBound(OutVal, 1), 3).Value = OutVal
End Sub 

After that, you can close the VBA editor, return to Excel, select the source table (completely, with the header and the first column with countries) and run our macro through the menu Service - Macro - Macros (Tools - Macro - Macros) (ALT+F8) .

The macro will insert a new sheet into the workbook and create a new, reconstructed version of the selected table on it. You can work with such a table "with the full program", using the entire arsenal of Excel tools for processing and analyzing large tables.

Related articles:

• Reorganization of tables (Unpivot) in LibreOffice Calc
• Creating reports using Pivot Tables