Learn How To Replace Blanks In A Pivot Table With Zeroes With This Microsoft Excel Tutorial


When pivot tables first came out, there was no way to correct this. After much outcry from accountants everywhere, Microsoft gave us two ways to solve the problem.

Method 1:

If you discover the problem after the fact in a completed pivot table, follow these steps.

1) Select one cell in the pivot table in order to display the PivotTable toolbar. From the toolbar, use the PivotTable dropdown and select the Table Options menu item, as shown in Fig. 870.

2) In the Table Options dialog, on the right side, locate the field called For Empty Cells, Show:, as shown in Fig. 871.

3) By default, this setting is blank. Type a zero in the textbox and choose OK, as shown in Fig. 872.


As you see in Fig. 873, blanks in the data section of the pivot table are shown as zeroes.

Method 2:

The other method for having zeroes appear is to use the Options button in Step 3 of the PivotTable Wizard, as shown in Fig. 874. This button will open the same Options dialog as shown above.


Make sure to use the Options dialog to display empty cells as zeroes instead of blanks.

Commands Discussed:

Data – Pivot Table


Fig. 869
Fig. 870
Fig. 871
Fig. 872
Fig. 873
Fig. 874