Home > Pivot > Layout > Classic Layout Classic Layout Pivot Table Format MacroHow 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. |
Video: Change Pivot Table to Classic LayoutIn 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:
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. |
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 TableThe 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.
Format Selected Pivot Table OnlyBefore 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 TablesThe 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:
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 SheetThis 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 SheetsThis 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 WorkbookSample 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):
|
More Pivot Table Tutorials |
Last updated: November 21, 2022 7:09 PM