How Do I Quickly Reset a Pivot Table Default Settings with VBA?

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.