Blank Data Records

To see the effect of blank records on a PivotTable report, let's make Sheetl active and remove the data for Reading, PA's tank top sales. The Quantity and Sales Total values are 0, but we want to make them blank as though no data were added (as shown in Figure 6-15).

Figure 6-15. Blank data for Reading, PA tank top sales

1. Activate the worksheet containing the PivotTable report.

2. Refresh the data (either through the UI or the RefreshPivotTableFromWorksheet procedure). Figure 6-16 shows Excel 2007's default behavior when we have blank values in a PivotTable.

Figure 6-16. Blank values display as blank on PivotTable report

3. Drag the Sum of Qty label back up to the field selection list in the PivotTable Field List.

There is a little quirk that exists in the UI that you might encounter when coding PivotTables that bears a quick mention here. When Excel finds blank or null data in a range of data used in a PivotTable, and that field is used in the summary section, it defaults the summary field to "Count of field name" even though "Sum of field name" may be a more appropriate selection.

4. Drag the Qty field back down to the Values list.

Figure 6-17 shows Excel displaying "Count of field name" when we want to sum.

Figure 6-17. Count of Qty is the default due to the blank data record.

5. To prevent blank data from displaying, we can use the NullString property of the PivotTable object. In the VBE, add the following subroutine to the project:

Sub ZeroForBlanks()

ActiveSheet.PivotTables("PivotTable1").NullString = "0" End Sub

6. From the Macros dialog box, run the subroutine. Figure 6-18 shows the result of running the ZeroForBlanks macro.

7. To fix Excel's inaccurate guess that we wanted to count the number of Qty records in the summary section of our PivotTable, we can use the Function property of the PivotField object. Add the following subprocedure to the standard code module:

Sub ChangeSummaryFunction()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Qty") .Caption = "Sum of Qty" .Function = xlSum End With End Sub

Once this code runs, the PivotTable will look like it did in Figure 6-16.

Figure 6-18. Zeros displayed instead of blanks

Table 6-4 lists the possible choices for the Function property.

Table 6-4. XlConsolidationFunction Enumeration






Averages all numeric values



Counts all cells including numeric, text, and errors; equal to the worksheet function =COUNTA()



Counts numeric values only; equal to the worksheet function =COUNT()



Shows the largest value



Shows the smallest value



Multiplies all the cells together



Standard deviation based on a sample



Standard deviation based on the whole population



Returns the total of all numeric data



No subtotal function specified



Variation based on a sample



Variation based on the whole population

0 0

Post a comment