Contextures

Home > Pivot > Layout > Classic Layout

Classic Layout Pivot Table Format Macro

How to format a pivot table in classic style, like pivot tables in older versions of Excel. With Classic Layout, you can drag and drop pivot fields on the worksheet, instead of using the PivotTable Fields List to change the layout. Video shows the steps, or use macros to automate the layout change.

drag and drop fields with pivot table in classic format

Video: Format Pivot Table with Macro

Format a Pivot Table in Classic Style

Code: Format Pivot Table

Format Selected Pivot Table

Format All Pivot Tables

Get the Sample Workbook

Video: Change Pivot Table to Classic Layout

In this video, you'll see the manual steps required to format a pivot table in Classic Style, so you can drag and drop the fields, right on the worksheet. There are several steps in the process:

  • change subtotal setting
  • change report layout of the pivot table
  • change pivot table style
  • change the pivot table display options
  • change setting to clear old items in Pivot Table
  • format each value field as number format
  • sort each row field alphabetically.

Save Time with a Macro: Near the end of the video, you'll see how much quicker it is to run the recorded macro, to format the pivot table in seconds, instead of minutes.

Get the macro code in the sections below, and you can download the sample workbook with the macros in it.

Format a Pivot Table in Classic Style

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

For example, this pivot table is in the sample file that you can download.

  • It has a modern pivot table style, and there are subtotals showing for the regions.
  • You cannot drag and drop the fields on the worksheet. Instead, you can change the field layout in the PivotTable Field list.

pivot table before running classic format macro

Change to Classic Layout

To get the pivot table into Classic Layout, so you can drag and drop, you can:

  • Follow the steps shown in the video (above)
  • Or, save time by using one of the macros in the following sections.

Classic Style Macros

Each of the Classic Layout macros on this page do the following steps:

  • 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.

Pivot Table with Classic Layout

Here's the sample file pivot table after running a Classic Layout macro.

pivot table after running macro

Now you can point to a field button in the pivot table layout, and drag and drop that field in a different location. Just like the good old days!

drag and drop fields with pivot table in classic format

Macro to Change One Pivot Table

This macro formats a single pivot table -- the first pivot table indexed on the active sheet.

NOTE: This might NOT be the first pivot table, starting from the top left of the worksheet.

 
Sub ClassicSettings_PT01()

'formats PivotTable(1) on active sheet
'In summary, this macro:
'  Applies Classic PivotTable display,
'     with gridlines and no colors
'  Ensures that only current data
'   appear in PivotTable dropdown lists
'  Sets all fields to ascending order,
'   no subtotals
'  For data field(s) in Values area,
'   changes setting to Sum,
'     changes the number format, and
'   if field in Values area is named
'   "Amount" or "Total Amount"
'     it shortens label to
'     "Sum Amt" or "Sum TtlAmt"

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

'Sets each field in ascending order
'   even fields not currently
'     in PivotTable layout
For Each pf In pt.PivotFields
    pf.AutoSort xlAscending, pf.Name
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
Next pf

'Changes formatting of Value fields
For Each pf In pt.DataFields
    pf.Function = xlSum
    pf.NumberFormat = _
        "#,##0.00_);[Red](#,##0.00)"
Next pf

'Shorten description of Value fields,
'    if 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

'prevent old data from appearing in drop downs
pt.PivotCache.MissingItemsLimit _
    = xlMissingItemsNone

End Sub

Format Selected Pivot Table

The macro in the previous section only formats one pivot table one the active worksheet - the pivot table that is first in the index.

Ideally, you will only have one pivot table on a worksheet, to prevent problems with overlapping. However, if there are multiple pivot tables on the worksheet, the following macro will only format the selected pivot table.

  • This code is a variation of the original macro, with a change in the top section.
  • Instead of setting the pivot table to PivotTables(1), it uses the pivot table connected to the active cell.
  • If the active cell is not in a pivot table, a message box appears, and the macro stops running

no pivot table selected message box

Format Selected Pivot Table Only

Before you run the following macro, select any cell in the pivot table that you want to format.

NOTE: If the active cell is not in a pivot table, a message box appears, and the macro stops running

 
Sub ClassicSettings_SelPT()
On Error Resume Next
Application.ScreenUpdating = False
 
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveCell.PivotCell.PivotTable

If pt Is Nothing Then
	MsgBox "No Pivot Table selected" _
	& vbInformation _
	& vbCrlf _
	& "Please try again"
	Exit Sub
End If

pt.ManualUpdate = True
  
With pt
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .TableStyle2 = ""
    .DisplayContextTooltips = False
    .ShowDrillIndicators = False
End With

For Each pf In pt.PivotFields
    pf.AutoSort xlAscending, pf.Name
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
Next pf

For Each pf In pt.DataFields
    pf.Function = xlSum
    pf.NumberFormat = _
       "#,##0.00_);[Red](#,##0.00)"
Next pf

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

pt.PivotCache.MissingItemsLimit _
     = xlMissingItemsNone

End Sub

Format All Pivot Tables

The previous macros format specific pivot tables - either the first indexed pivot table on the active sheet, or the pivot table for the active cell.

The following two macros format ALL pivot tables, either:

  1. all pivot tables on the active sheet only
  2. all pivot tables on all worksheets

Both macros are in the sample file that you can download, or copy the code below, and paste it into a regular code module in your Excel workbook.

1) Format All Pivot Tables on Active Sheet

This macro formats ALL pivot tables on the active worksheet only.

 
Sub ClassicSettings_AllWS()
On Error Resume Next
Application.ScreenUpdating = False
 
Dim pt As PivotTable
Dim pf As PivotField

For Each pt in ActiveSheet.PivotTables

  pt.ManualUpdate = True
  
  With pt
      .InGridDropZones = True
      .RowAxisLayout xlTabularRow
      .TableStyle2 = ""
      .DisplayContextTooltips = False
      .ShowDrillIndicators = False
  End With
  
  For Each pf In pt.PivotFields
      pf.AutoSort xlAscending, pf.Name
      pf.Subtotals(1) = True
      pf.Subtotals(1) = False
  Next pf
  
  For Each pf In pt.DataFields
      pf.Function = xlSum
      pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
  Next pf
  
  With pt.PivotFields("Sum of Amount")
      .Caption = "Sum Amt"
  End With
  With pt.PivotFields("Sum of Total Amount")
      .Caption = "Sum TtlAmt"
  End With
  pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  pt.ManualUpdate = False
Next pt

Application.ScreenUpdating = True

End Sub

2) Format All Pivot Tables on All Sheets

This macro formats ALL pivot tables on ALL worksheets in the active workbook.

 
Sub ClassicSettings_AllWB()
On Error Resume Next
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
  
    pt.ManualUpdate = True
    
    With pt
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        .TableStyle2 = ""
        .DisplayContextTooltips = False
        .ShowDrillIndicators = False
    End With
    
    For Each pf In pt.PivotFields
        pf.AutoSort xlAscending, pf.Name
        pf.Subtotals(1) = True
        pf.Subtotals(1) = False
    Next pf
    
    For Each pf In pt.DataFields
        pf.Function = xlSum
        pf.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    Next pf
    
    With pt.PivotFields("Sum of Amount")
        .Caption = "Sum Amt"
    End With
    With pt.PivotFields("Sum of Total Amount")
        .Caption = "Sum TtlAmt"
    End With
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.ManualUpdate = False
  Next pt
Next ws

Application.ScreenUpdating = True

End Sub

Get the Sample Workbook

Sample Excel File: Click this link to download the Format a pivot table in classic style sample file, which contains sample data for the video, and for testing the macros shown on this page.

NOTE: The zipped file is in Excel xlsm format, and contains macros, so be sure to do these steps (required for Microsoft security settings):

  1. Check the box to unblock the file in Windows Explorer, after you download it.
  2. Then, when you open the workbook, enable macros when you see the notification, if you want to test the Classic Pivot Table macros.

More Pivot Table Tutorials

Plan and Set Up Pivot Table

FAQs - Pivot Tables

Pivot Table Introduction

Excel Slicer Macros

Summary Functions

Clear Old Items in Pivot Table

 

 

 

About Debra

Last updated: November 21, 2022 7:09 PM