Pivot tables (pivot tables) are a powerful tool in data analysis. However, sometimes you need to unpivot (unpivot) the pivoted data (otherwise known as "straightening" or unpivoting the data) to put it in 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.
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 summary tables with changed or supplemented data.
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 original 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.