Resetting the default settings for all my pivot tables takes forever!
No worries! Here’s a piece of VBA code that makes resetting these a breeze!
| Sub ResetPTDefaultSettings() | |
| Dim Wks As Worksheet | |
| Dim PT As PivotTable | |
| '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
| 'written by Angelina Teneva, 17/09/2016 | |
| '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
| For Each Wks In ActiveWorkbook.Worksheets | |
| If Wks.name <> "2013" Then Wks.Activate | |
| For Each PT In ActiveSheet.PivotTables | |
| PT.RowAxisLayout xlCompactRow | |
| 'PT.RowAxisLayout xlOutlineRow | |
| 'PT.RowAxisLayout xlTabularRow | |
| 'PivotTable Options --> Layout & Format tab | |
| PT.HasAutoFormat = False 'turns off columns' autofit on update | |
| PT.DisplayErrorString = True 'shows errors as empty cells | |
| PT.ErrorString = "-" 'displays a value for the error string | |
| PT.DisplayNullString = True 'displays zeros as empty cells | |
| 'PivotTableOptions --> Totals & Filters tab | |
| PT.ColumnGrand = True 'shows grand totals for columns | |
| PT.RowGrand = True 'shows grand totals for rows | |
| PT.AllowMultipleFilters = True 'allows multiple filters to be set on pivot fields | |
| 'Pivot Table Options --> Data tab | |
| PT.EnableDrilldown = False 'prevents access to raw data on double click | |
| PT.PivotCache.MissingItemsLimit = xlMissingItemsNone 'removing non-existing items from filters | |
| Next PT | |
| Next Wks | |
| End Sub |
Why should I bother adjusting, though?
Whilst most of these settings are more of a “visual best practice”, they can have an impact on how people perceive your analysis
Unfamiliar with some of these? Check them out here!
Happy VBA coding!
