Excel Pivot Table Layout and Design, using VBA

Excel Pivot Table Layout and Design, using VBA

Layout Forms, Subtotals, GrandTotals, Number Format, Pivot Table Styles & Style Options, Layout & Display options, …


Related Links:

1. Create an Excel Pivot Table report using VBA

2. Referencing an Excel Pivot Table Range using VBA

3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA

4. Excel Pivot Table Address, Location, Move & Copy using VBA

5. Excel Pivot Table Layout and Design, using VBA

6. Excel Pivot Table Properties & Settings, using VBA

7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA

8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA

9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA

10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA

11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA

12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA

13. Create & Customize Excel Pivot Table Charts, using VBA

Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):

Create and Customize a Pivot Table report


————————————————————————————————————-

Contents:

‘Options’ and ‘Design’ tabs under ‘PivotTable Tools’

Layout Forms for PivotTable report, column header and row header

Hide or Display Subtotals

Subtotal Location, Inserting blank lines, show/hide GrandTotals & Grand Total Name, set Number Format

Pivot Table Styles – Style Gallery; PivotTable Style Options – Row Headers, Column Headers, Banded Rows & Banded Columns

PivotTable Layout – custom string for error or empty cells; Classic Pivot Table Layout; display field captions & filter drop downs; show expand/collapse buttons; display tooltips; align cells

————————————————————————————————————-

he ‘Options’ and ‘Design’ tabs (under the ‘PivotTable Tools’ tab on the ribbon) have various tools to customize the Pivot Table report and determine its structure, layout and design.

The ‘PivotTable Options’ dialog box has options for settings in respect of Layout & Format, Totals & Filters, Display, Printing & Data in a PivotTable report. To launch the dialog box: under the ‘PivotTable Tools’ tab on the ribbon, click ‘Options’ tab, then click ‘Options’ in the ‘PivotTable’ group and click ‘Options’ again which will open the dialog box of ‘PivotTable Options’. The dialog box has 5 tabs of Layout & Format, Totals & Filters, Display, Printing & Data each of which have various settings for a PivotTable report.

Under the ‘PivotTable Tools’ tab on the ribbon there is a ‘Design’ tab. This contains special formatting options for a PivotTable report, and has three groups: (i) Layout – this has settings for subtotals, grand totals, layout forms and blank rows; (ii) PivotTable Style Options – has options for display of row & column headers and banded rows & columns; and (iii) PivotTable Styles – you can choose from a variety of visual styles in the gallery or add a custom style.

In this section we discuss Layout and Design options for a PivotTable report using vba.

Layout Forms for PivotTable report, column header and row header

The Design tab contains special formatting options for a PivotTable report, and includes: (i) the 3 Layout Forms for PivotTable report display – Compact Form, Outline Form and Tabular Form; (ii) settings for Subtotals and GrandTotals; and (iii) insertion of blank rows. We show the corresponding vba codes for these below.

When the PivotTable report is displayed in Compact Form, use the PivotTable.CompactLayoutColumnHeader Property to set the caption of the column header, and use the PivotTable.CompactLayoutRowHeader Property to set the caption of the row header.

Example: Refer Image 1 – the PivotTable at the top is displayed in Compact Form, before captions are set and the PivotTable at the bottom displays captions as set for column header and row header with the below code.

Image 1

Sub PivotTableLayout1()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘setting the 3 Layout Forms for PivotTable report display, using the RowAxisLayout method:
PvtTbl.RowAxisLayout xlCompactRow
‘PvtTbl.RowAxisLayout xlOutlineRow
‘PvtTbl.RowAxisLayout xlTabularRow

PvtTbl.CompactLayoutColumnHeader = “Countries”
PvtTbl.CompactLayoutRowHeader = “Models”

End Sub

Hide or Display Subtotals:

The PivotField.Subtotals Property sets display of Subtotals in a PivotField. Index argument in Subtotals(Index): Index values range from 1 to 12 -> 1 means Automatic, 2 means Sum, 3-Count, 4-Average, 5-Max, 6-Min, and so on. Subtotals for the PivotField are displayed if the Index is True. Setting Index 1 (Automatic) to True sets all other values to False.

Image 2a

Example: Refer Image 2a which displays SubTotals for all fields (“Car Models” & “Region”) in the PivotTable. Hide Subtotals in a field after running below code – refer Image 2b.

Image 2b

Sub PivotTableLayout2a()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘set Index 1 (Automatic) to True which sets all other values to False:
PvtTbl.PivotFields(“Car Models”).Subtotals(1) = True

‘then set Index 1 (Automatic) to False so that Subtotals are False (ie. hidden) in the “Car Models” field – refer Image 2b:
PvtTbl.PivotFields(“Car Models”).Subtotals(1) = False

End Sub

Example: Refer Image 2a which displays SubTotals for all fields (“Car Models” & “Region”) in the PivotTable. Hide Subtotals for all fields in the PivotTable after running below code – refer Image 2c.

Image 2c

Sub PivotTableLayout2b()

Dim PvtTbl As PivotTable
Dim pvtFld As PivotField

Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘hide Subtotals for all fields (“Car Models” & “Region”) in the PivotTable – refer Image 2c:
With PvtTbl

For Each pvtFld In .PivotFields

pvtFld.Subtotals(1) = True

pvtFld.Subtotals(1) = False

Next pvtFld

End With

End Sub

Subtotal Location, Inserting blank lines, show/hide GrandTotals & Grand Total Name, set Number Format 

Example: refer Image 3 for the PivotTable Layout display after running the below code.

Image 3

Sub PivotTableLayout3()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘display PivotTable report in Compact Form
PvtTbl.RowAxisLayout xlCompactRow

‘show Subtotals for all fields (“Car Models” & “Region”) in the PivotTable:
With PvtTbl

For Each pvtFld In .PivotFields

pvtFld.Subtotals(1) = True

Next pvtFld

End With

‘use the SubtotalLocation property to display Subtotals at the Top or Bottom of each group of outer row items in a Field.
‘Subtotals for Tabular Form layout will always be shown at the bottom; For the Compact Form or Outline Form layouts, Subtotals can be shown either at the Top or Bottom.
PvtTbl.SubtotalLocation xlAtTop
‘PvtTbl.SubtotalLocation xlAtBottom

‘Inserting blank lines after each item of a field; Note that blank rows can be added only after outer row items and not after each item in the innermost nest.
PvtTbl.PivotFields(“Car Models”).LayoutBlankLine = True

‘show/hide GrandTotals in a PivotTable, using the PivotTable.ColumnGrand Property for columns and the PivotTable.RowGrand Property for rows. True setting will show and False will hide the GrandTotals:
PvtTbl.ColumnGrand = True
PvtTbl.RowGrand = True

‘set the Grand Total Name for both columns and rows, using the PivotTable.GrandTotalName Property. Default name is “Grand Total”.
PvtTbl.GrandTotalName = “Car Sales Total”

‘set number format of a specific data field:
PvtTbl.PivotFields(“Sum of Sales”).NumberFormat = “$#,##0”

End Sub

Pivot Table Styles – Style Gallery; PivotTable Style Options – Row Headers, Column Headers, Banded Rows & Banded Columns

Refer the ‘Design’ tab under the ‘PivotTable Tools’ tab on the ribbon. You can easily change the style of a Pivot Table Report, as to its visual appearance in terms of format and color scheme. Excel 2007 provides a gallery of predefined styles from which you can choose and you can also create your own Custom Style.

Refer the ‘Design’ tab under the ‘PivotTable Tools’ tab on the ribbon. Selecting PivotTable Style Options makes readability easier in a PivotTable. This group has check boxes for Row Headers, Column Headers, Banded Rows & Banded Columns which can be selected or cleared. Selecting Row Headers or Column Headers displays special formatting for the row headers (or column headers) of the Pivot Table. Selecting Banded Rows or Banded Columns will band rows (or columns) wherein alternate rows (or columns) are formatted or shaded differently, which provides easier readability.

Example: Refer Image 4a for PivotTable display in Compact Form with no design or formatting. Refer Image 4b for PivotTable display after running below code.

Image 4a

 

Image 4b

Sub PivotTableLayout4()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘display PivotTable report in Tabular Form
PvtTbl.RowAxisLayout xlTabularRow

‘specify the style to apply to a PivotTable using the PivotTable.TableStyle2 Property
PvtTbl.TableStyle2 = “PivotStyleMedium3”

‘PivotTable.ShowTableStyleColumnHeaders Property. Set to True to display the column headers in the PivotTable.
PvtTbl.ShowTableStyleColumnHeaders = True

‘PivotTable.ShowTableStyleRowHeaders Property. Set to True to display the row headers in the PivotTable.
PvtTbl.ShowTableStyleRowHeaders = True

‘PivotTable.ShowTableStyleColumnStripes Property. Set to True to display the banded columns in the PivotTable.
PvtTbl.ShowTableStyleColumnStripes = True

‘PivotTable.ShowTableStyleRowStripes Property. Set to True to display the banded rows in the PivotTable.
PvtTbl.ShowTableStyleRowStripes = True

End Sub

PivotTable Layout – custom string for error or empty cells; Classic Pivot Table Layout; display field captions & filter drop downs; show expand/collapse buttons; display tooltips; align cells.

Example: Refer Image 5 for PivotTable display after running below code.

Image 5

Sub PivotTableLayout5()

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets(“Sheet1”).PivotTables(“PivotTable1”)

‘Use the PivotTable.DisplayErrorString Property to display or not to display a custom string for error cells (ex. data cells with zero division). If set to True, the custom string will be displayed and a False setting will not display the custom string.
PvtTbl.DisplayErrorString = True
‘to specify the custom string which will be displayed for error cells, use the PivotTable.ErrorString Property.
PvtTbl.ErrorString = “Error”

‘Use the PivotTable.DisplayNullString Property to display or not to display a custom string for empty or null cells. If set to True, the custom string will be displayed and a False setting will not display the custom string.
PvtTbl.DisplayNullString = True
‘to specify the custom string which will be displayed for Null cells, use the PivotTable.NullString Property.
PvtTbl.NullString = “Blank”

‘to view the PivotTable in Classic Pivot Table Layout which enables dragging of fields in the grid (as it was in Excel 2003), set InGridDropZones property to True, else set to False:
PvtTbl.InGridDropZones = True

‘To determine display of field captions and filter drop downs, use the PivotTable.DisplayFieldCaptions Property. If set to True, the field captions and filter drop downs will be displayed and a False setting will not display them. Default setting is True.
PvtTbl.DisplayFieldCaptions = True

‘show expand/collapse buttons in a PivotTable report, using the PivotTable.ShowDrillIndicators Property. If set to True, drill indicators are displayed, while the False setting will not display drill indicators:
PvtTbl.ShowDrillIndicators = True

‘Running your mouse cursor over data items in a Pivot Table report can display tooltips which show value, row or column details. To display tooltips use the PivotTable.DisplayContextTooltips Property and set to True. Setting the value to False will not display tooltips.
PvtTbl.DisplayContextTooltips = True

‘align cells in the data field of a PivotTable report:
PvtTbl.DataBodyRange.HorizontalAlignment = xlCenter

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top