Pivot Table Options in Excel

24 Jul 202612 minutes to read

Show or hide the field list

To show or hide the pivot table field list pane, use the ShowFieldList property. The default value is true.

//Hide the pivot table field list pane
options.ShowFieldList = false;
//Hide the pivot table field list pane
options.ShowFieldList = false;
'Hide the pivot table field list pane
options.ShowFieldList = False

Header caption

The RowHeaderCaption and ColumnHeaderCaption properties edit the corresponding pivot table headers. The header captions can be shown or hidden using the DisplayFieldCaptions property. By default, the row header shows “Row Labels” and the column header shows “Column Labels” in compact layout mode.

//Set the row and column header captions
options.RowHeaderCaption = "Payment Dates";
options.ColumnHeaderCaption = "Payments";
//Set the row and column header captions
options.RowHeaderCaption = "Payment Dates";
options.ColumnHeaderCaption = "Payments";
'Set the row and column header captions
options.RowHeaderCaption = "Payment Dates"
options.ColumnHeaderCaption = "Payments"

Grand total

Use the ColumnGrand and RowGrand properties of the IPivotTable to show or hide the column and row grand totals. Both properties default to true.

//Hide column grand total; show row grand total
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = true;
//Hide column grand total; show row grand total
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = true;
'Hide column and row grand totals
pivotTable.ColumnGrand = False
pivotTable.RowGrand = False

Show or hide collapse button

You can also show or hide the Collapse button that appears in the fields of the pivot table, when more than one item exists in a field. The following code example illustrates how to do this.

//Show the collapse (drill) buttons
pivotTable.ShowDrillIndicators = true;
//Show the collapse (drill) buttons
pivotTable.ShowDrillIndicators = true;
'Show the collapse (drill) buttons
pivotTable.ShowDrillIndicators = True

Display field caption and filter option

The filter buttons and field names in the pivot table can be shown or hidden, as in the following code.

//Show the field captions and filter buttons
pivotTable.DisplayFieldCaptions = true;
//Show the field captions and filter buttons
pivotTable.DisplayFieldCaptions = true;
'Show the field captions and filter buttons
pivotTable.DisplayFieldCaptions = True

Repeating row label on each page

You can set the row label on each page while printing, and the header can be viewed on each page.

//Repeat row labels on every printed page
pivotTable.RepeatItemsOnEachPrintedPage = true;
//Repeat row labels on every printed page
pivotTable.RepeatItemsOnEachPrintedPage = true;
'Repeat row labels on every printed page
pivotTable.RepeatItemsOnEachPrintedPage = True

Repeat Labels

You can repeat labels for row or column fields when the pivot table layout is set to tabular or outline layout forms.

Specific Pivot Field

The following code illustrates how to set the repeat labels option to a specific pivot field.

//Set repeat labels option to a specific pivot field
pivotTable.Fields[0].RepeatLabels = true;
//Set repeat labels option to a specific pivot field
pivotTable.Fields[0].RepeatLabels = true;
'Set repeat labels option to a specific pivot field
pivotTable.Fields(0).RepeatLabels = True

All Pivot Fields

The following code illustrates how to set the repeat labels option to all the pivot fields.

//Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(true);
//Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(true);
'Set repeat labels option to all the pivot fields
pivotTable.Options.RepeatAllLabels(True)

Show Values Row

To show values in rows in a pivot table, set the ShowValuesRow property of IPivotTableOptions to true. The following code illustrates how to enable the values row in the pivot table.

//Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = true;
//Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = true;
'Set the show values row option in pivot table.
pivotTable.Options.ShowValuesRow = True

Other pivot table operations

Adding calculated field in the existing pivot table

Calculated field is a special type of database field that perform calculations by using the contents of other fields in the pivot table with the given formula. The formula can contain operators and expressions as in other worksheet formulas. You can use constants and refer to the data from the PivotTable.

You can read and create the calculated fields in the existing pivot table. The following are the Excel restrictions when using the formula:

  • Formula cannot contain cell references or defined names.
  • Formula cannot contain Worksheet functions that require cell references.
  • Formula cannot use array functions.

The calculated field in XlsIO can be achieved using the following code sample.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet sheet = workbook.Worksheets[1];
  IPivotTable pivotTable = sheet.PivotTables[0];

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  //Saving the workbook
  workbook.SaveAs("PivotTableCalculate.xlsx");
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet sheet = workbook.Worksheets[1];
  IPivotTable pivotTable = sheet.PivotTables[0];

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  workbook.SaveAs("PivotTableCalculate.xlsx");
  excelEngine.ThrowNotSavedOnDestroy = false;
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("PivotTable.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(1)
  Dim pivotTable As IPivotTable = sheet.PivotTables(0)

  ' Add calculated field to the first pivot table
  Dim field As IPivotField = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100")

  workbook.SaveAs("PivotTableCalculate.xlsx")
  excelEngine.ThrowNotSavedOnDestroy = False
End Using

The formula can also be set to the IPivotField property as follows.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet sheet = workbook.Worksheets[1];
	IPivotTable pivotTable = sheet.PivotTables[0];

	//Add calculated field to the first pivot table
	IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Units/3000*100");

	//Set Field Formula
	field.Formula = "Units/3000*200";

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/CalculatedField.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("PivotTable.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];
  IPivotTable pivotTable = sheet.PivotTables[0];

  //Add calculated field to the first pivot table
  IPivotField field = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100");

  //Set Field Formula
  field.Formula = "Sales/Total*200";

  workbook.SaveAs("PivotTable.xlsx");
  excelEngine.ThrowNotSavedOnDestroy = false;
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("PivotTable.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  Dim pivotTable As IPivotTable = sheet.PivotTables(0)

  Dim field As IPivotField = pivotTable.CalculatedFields.Add("Percent", "Sales/Total*100")

  'Set Field Formula
  field.Formula = "Sales/Total*200"

  workbook.SaveAs("PivotTable.xlsx")
  excelEngine.ThrowNotSavedOnDestroy = False
End Using

A complete working example to add calculated field in pivot table in C# is present on this GitHub page.