Setting Pivot Table Option - For Empty Cells Show

Setting Pivot Table Option in Microsoft Excel

To find and set this option in Microsoft Excel:

  1. Select a pivot table and right-click.
  2. Select PivotTable Options.
  3. Select the Layout & Format tab.
  4. Select the For empty cells show option and specify a string.

Setting Pivot Table Option Using Aspose.Cells

Aspose.Cells provides the PivotTable.DisplayNullString and PivotTable.NullString properties for setting the “For empty cells show” pivot table option.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
Workbook wb = new Workbook(dataDir + "input.xlsx");
PivotTable pt = wb.Worksheets[0].PivotTables[0];
// Indicating if or not display the empty cell value
pt.DisplayNullString = true;
// Indicating the null string
pt.NullString = "null";
pt.CalculateData();
pt.RefreshDataOnOpeningFile = false;
wb.Save(dataDir+ "output_out.xlsx");
  • Formatting Pivot Table