Classic Pivot Table Format Macro

Use this macro from Bob Ryan, of Simply Learning Excel, to format a pivot table in classic style, similar to the pivot tables in older versions of Excel

Format a Pivot Table in Classic Style

This sample Excel VBA code from Bob Ryan, of Simply Learning Excel, formats the first pivot table on the active sheet.

The macro does the following:

• Applies the Classic PivotTable display, with gridlines and no colors
• Ensures that only data that still exists in ' the data that drives the PivotTable ' will appear in the PivotTable dropdown lists
• Sets all fields to ascending order with no subtotals including fields that are not in the Row Labels or Column Labels areas
• For the data field(s) in the Values area, changes the setting to Sum, changes the number format, and if the field in the Values area is named "Amount" ot "Total Amount" it shortens the label in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively..

Watch the Format Pivot Table in Classic Style macro video to see the manual formatting time, compared to running the macro.

Copy the Code

Sub ClassicPlusPivotTableSettings()

'Last modified and tested 2010-07
'Much of this is based on a book by Debra Dalgleish named
'    "Excel Pivot Tables Recipe Book: A Problem-Solution Approach", Chapter 13
'
'The following code applies to PivotTable(1) on the active sheet.
'In summary, this macro:
'     Applies the Classic PivotTable display, with gridlines and no colors
'     Ensures that only data that still exists in 
'     the data that drives the PivotTable
'          will appear in the PivotTable dropdown lists
'     Sets all fields to ascending order with no subtotals
'          including fields that are not in the Row Labels or Column Labels areas
'     For the data field(s) in the Values area, 
'          changes the setting to Sum, changes the number format, and
'          if the field in the Values area is named "Amount" ot "Total Amount" 
'          it shortens the label
'          in the PivotTable to "Sum Amt" or "Sum TtlAmt" respectively.

On Error Resume Next
Application.ScreenUpdating = False
 
Dim pt As PivotTable
Dim pf As PivotField
 
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
  
'This section applies Classic PivotTable settings
'     and turns off the Contextual Tooltips and the Expand/Collapse buttons
With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .TableStyle2 = ""
    .DisplayContextTooltips = False
    .ShowDrillIndicators = False
End With

'This sets each field in ascending order. It applies this even to fields 
'     that are not currently in the PivotTable.
For Each pf In pt.PivotFields
    pf.AutoSort xlAscending, pf.Name
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
Next pf

'This command changes the formatting of any field that appears in the Values area
For Each pf In pt.DataFields
    pf.Function = xlSum
    pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Next pf

'These next two sets of statements shorten the description of 
'     any field in the Values area,
'     but only if the field is named "Amount" or "Total Amount"
With pt.PivotFields("Sum of Amount")
    .Caption = "Sum Amt"
End With
With pt.PivotFields("Sum of Total Amount")
    .Caption = "Sum TtlAmt"
End With

pt.ManualUpdate = False
Application.ScreenUpdating = True

'This ensures that only data that still exists in the data 
'     that drives the PivotTable
'     will appear in the PivotTable dropdown lists
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub

Get the Sample Workbook

Get the Format a pivot table in classic style sample file. The file is zipped, and in Excel xlsm format.

More Pivot Table Tutorials

How to Plan and Set Up a Pivot Table

FAQs - Pivot Tables

Pivot Table Introduction

Excel Slicer Macros

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table