How to: Control Style Options

• Jun 07, 2019
• 4 minutes to read

A pivot table style consists of a collection of table style elements (TableStyleElement), each of which specifies formatting for a particular element of a pivot table. The TableStyleElementType enumerator lists supported table style element types. The PivotTable object provides specific properties that allow you to configure PivotTable style options by specifying table elements to be formatted as defined by the corresponding elements of the applied pivot table style.

• Format Row Headers

  Use the PivotTable.ShowRowHeaders property to enable or disable style formatting for row headers in a pivot table. The appearance of row headers is specified by the TableStyleElementType.FirstRowSubheading, TableStyleElementType.SecondRowSubheading and TableStyleElementType.ThirdRowSubheading elements of the applied pivot table style.

  View Example

  PivotTableFormattingActions.vb

  Dim worksheet As Worksheet = workbook.Worksheets("Report1")
  workbook.Worksheets.ActiveWorksheet = worksheet
  
  ' Access the pivot table by its name in the collection.
  Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
  ' Add the "Region" field to the column axis area. 
  pivotTable.ColumnFields.Add(pivotTable.Fields("Region"))
  ' Remove formatting from row headers.
  pivotTable.ShowRowHeaders = False
  

  PivotTableFormattingActions.cs

  Worksheet worksheet = workbook.Worksheets["Report1"];
  workbook.Worksheets.ActiveWorksheet = worksheet;
  
  // Access the pivot table by its name in the collection.
  PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
  // Add the "Region" field to the column axis area. 
  pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);
  // Remove formatting from row headers.
  pivotTable.ShowRowHeaders = false;
  

• Format Column Headers

  Use the PivotTable.ShowColumnHeaders property to enable or disable style formatting for column headers in a pivot table. The appearance of column headers is specified by the TableStyleElementType.HeaderRow, TableStyleElementType.FirstColumnSubheading, TableStyleElementType.SecondColumnSubheading and TableStyleElementType.ThirdColumnSubheading elements of the applied pivot table style.

  View Example

  PivotTableFormattingActions.vb

  Dim worksheet As Worksheet = workbook.Worksheets("Report1")
  workbook.Worksheets.ActiveWorksheet = worksheet
  
  ' Access the pivot table by its name in the collection.
  Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
  ' Remove formatting from column headers.
  pivotTable.ShowColumnHeaders = False
  

  PivotTableFormattingActions.cs

  Worksheet worksheet = workbook.Worksheets["Report1"];
  workbook.Worksheets.ActiveWorksheet = worksheet;
  
  // Access the pivot table by its name in the collection.
  PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
  // Remove formatting from column headers.
  pivotTable.ShowColumnHeaders = false;
  

• Apply Banded Row Formatting

  Set the PivotTable.BandedRows property to true to shade alternate rows in a pivot table as specified by the TableStyleElementType.FirstRowStripe and TableStyleElementType.SecondRowStripe elements of the applied pivot table style. To specify a number of PivotTable rows to be included into odd and even row stripes, use the TableStyleElement.StripeSize property of these table style elements.

  View Example

  PivotTableFormattingActions.vb

  Dim worksheet As Worksheet = workbook.Worksheets("Report4")
  workbook.Worksheets.ActiveWorksheet = worksheet
  
  ' Access the pivot table by its name in the collection.
  Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
  ' Apply the banded row formatting to the pivot table. 
  pivotTable.BandedRows = True
  

  PivotTableFormattingActions.cs

  Worksheet worksheet = workbook.Worksheets["Report4"];
  workbook.Worksheets.ActiveWorksheet = worksheet;
  
  // Access the pivot table by its name in the collection.
  PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
  // Apply the banded row formatting to the pivot table. 
  pivotTable.BandedRows = true;
  

• Apply Banded Column Formatting

  Set the PivotTable.BandedColumns property to true to shade alternate columns in a pivot table as specified by the TableStyleElementType.FirstColumnStripe and TableStyleElementType.SecondColumnStripe elements of the applied pivot table style. To specify the number of PivotTable columns to be displayed as odd and even column stripes, use the TableStyleElement.StripeSize property of these table style elements.

  View Example

  PivotTableFormattingActions.vb

  Dim worksheet As Worksheet = workbook.Worksheets("Report4")
  workbook.Worksheets.ActiveWorksheet = worksheet
  
  ' Access the pivot table by its name in the collection.
  Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
  ' Apply the banded column formatting to the pivot table. 
  pivotTable.BandedColumns = True
  

  PivotTableFormattingActions.cs

  Worksheet worksheet = workbook.Worksheets["Report4"];
  workbook.Worksheets.ActiveWorksheet = worksheet;
  
  // Access the pivot table by its name in the collection.
  PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
  // Apply the banded column formatting to the pivot table. 
  pivotTable.BandedColumns = true;