The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.
.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+

How to: Format Numbers and Dates in a Pivot Table

  • 2 min to read

The following properties apply a number format to the PivotTable fields:

Property Description
PivotField.NumberFormat Allows you to apply a number format to a numeric field in the pivot table's row, column or page area.
PivotDataField.NumberFormat Allows you to apply a number format to a data field.

The example below shows how to format numbers in a data field.

Spreadsheet_PivotField_NumberFormat

Worksheet sourceWorksheet = workbook.Worksheets["Data5"];
Worksheet worksheet = workbook.Worksheets.Add();
workbook.Worksheets.ActiveWorksheet = worksheet;

// Use the cell range "A1:E65" as the data source to create a pivot table.
PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:E65"], worksheet["B2"]);

// Add the "Category" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Category"]);
// Add the "Product" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["Product"]);

// Add the "Amount" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Amount"]);
// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";