I often use pivot tables to summarise one data source so that I can use that pivot table output as the basis for further analysis.
By working on summarised data I can reduce the overall file size by losing unnecessary detail and it is also a useful way to control the format of the data as well.
I find myself spending quite a bit of time adjusting the format of the pivot table so that it looks like a flat file data source that I can then copy and paste values as a new data source.
The stages to format a modern styled pivot table to a flat file style are:
- Convert to classic style
- Remove all subtotals
- Repeat item labels
If you do a similar thing you might appreciate this macro that automates the process
Sub PivotFieldsRepeat() 'Created by Dr Moxie from code assembled from the web 'Great tips from Contextures: 'http://blog.contextures.com/archives/2015/02/12/hide-pivot-table-subtotals/ 'The macros sets the pivot into classic mode, repeats all labels and removes subtotals 'Setting you up to copy and paste the pivot table as a flat file data source if required Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveCell.PivotTable On Error GoTo 0 If Not pt Is Nothing Then 'sets the pivot table into classic format With pt .InGridDropZones = True .RowAxisLayout xlTabularRow End With For Each pf In pt.PivotFields If pf.Orientation = xlRowField Then 'This option sets all subtotal options (Sum, Count, Avg etc) to false 'pf.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False) 'My preferred option is to set the automatic so that only one subtotal option is selected 'Then turn automatic subtotals off pf.Subtotals(1) = True pf.Subtotals(1) = False 'This section then repeats the item labels pf.RepeatLabels = True End If Next pf End If End Sub
If you want to reverse this macro and put your classic styled pivot table back to the new fangled Excel 2007 style, you’ll need to use this macro:
Sub ReturnToModernPivotStyle() Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveCell.PivotTable On Error GoTo 0 If Not pt Is Nothing Then 'sets the pivot table into the modern style With pt .InGridDropZones = False .RowAxisLayout xlCompactRow .TableStyle2 = "PivotStyleLight17" .DisplayContextTooltips = True .ShowDrillIndicators = True End With For Each pf In pt.PivotFields If pf.Orientation = xlRowField Then 'Turn automatic subtotals back on pf.Subtotals(1) = True 'This section then removes the repeating item labels pf.RepeatLabels = False End If Next pf End If End Sub
Here’s the example workbook with a working macro to both flatten the modern style pivot table (repeating item labels and removing subtotals) and a macro to return the classic style pivot table to the modern design. Pivot Macros workbook.xlsm