XL2000: Empty Cells in Published PivotChart and PivotTable Display Zeros

Empty cells (blanks) in your PivotTable data field appear as zeros in your published PivotTable and PivotChart.

When you use the Office PivotTable Component to publish a PivotChart with interactivity, any empty cells in the PivotTable data field are shown as zeros in the published PivotTable and PivotChart.

This behavior differs from the PivotTable in Excel. When you use the Excel PivotTable, you can choose not to chart empty cells at all.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

The default behavior for PivotTables in Excel is to display empty cells when there are no matching records. You can change this setting in the PivotTable Options dialog box with the For empty cells, show option, which includes a box for the value to display in empty cells. By default, the option is selected with nothing in the box, so that the cells are empty. If you clear the option or put a zero in the box, Excel places zeros in the empty cells.

The problem occurs because although Excel saves this setting, an interactive PivotChart and PivotList only support charting blank cells as zero. When you publish with interactivity, both the PivotChart and the PivotTable that is published with it display empty cells as zeros.