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
CHAPTER 6 n PIVOTTABLES 241
Changing the Number Format
The default number format in a new PivotTable is Excel??™s general number format. Most of us
like to see commas or currency symbols, which make the data more readable. To change the
number format, you use the PivotField.NumberFormat property. The NumberFormat property
sets or returns the string value that represents the format code for the numeric value. The format
code is the same string value given by the Format Codes option in the Format Cells dialog
box shown in Figure 6-19.
Figure 6-19. The Format Cells dialog box
Add the following routine to a standard module:
Sub FormatNumbersComma()
With ActiveSheet.
Pages:
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235