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

Name

Value

Description

xlAverage

-4106

Averages all numeric values

xlCount

-4112

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

xlCountNums

-4113

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

xlMax

-4136

Shows the largest value

xlMin

-4139

Shows the smallest value

xlProduct

-4149

Multiplies all the cells together

xlStDev

-4155

Standard deviation based on a sample

xlStDevP

-4156

Standard deviation based on the whole population

xlSum

-4157

Returns the total of all numeric data

xlUnknown

1000

No subtotal function specified

xlVar

-4164

Variation based on a sample

xlVarP

-4165

Variation based on the whole population

0 0

Post a comment