Eliminating Blank Cells in the Data Area

People started complaining about the blank cells immediately when pivot tables were introduced in Excel 95. Anyone using Excel 97 or later can replace the blank cells with zeroes. In the user interface, the setting can be found in the PivotTable Options dialog. This dialog can be accessed in Pivot Table Wizard Step 3, or from the Table Options choice in the Pivot Table drop-down on the left side of the Pivot Table toolbar. Choose the For Empty Cells, Show option and type 0 in the box, as shown in Figure 12.11.

Figure 12.11

Fill the empty cells in the data area of a pivot table with the For Empty Cells,Show setting in the PivotTable dialog.

PivotTable Options

Name: PivotTable 1

Format options 0 Grand totals for colunns 0 Grand £otab for rows 0 AutoFofmat table □ Subtotal hidden page Items n Merge labels 0 Preserve formatting 0 Repeat item labels or each printed

□ Mark Totals vwth * Data options

Data source options: 0 Save data ^ith table layoi* 0 Enable drill to details 1~1 Refresh on open n Refresh every [0^

External data options:

Q Save password [~~1 Background query I I Optimize memory mmutes

0 0

Post a comment