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.

Textove Pole
Figure 6-19. The Format Cells dialog box

Add the following routine to a standard module:

Sub FormatNumbersComma()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Qty")

Run the FormatNumbersComma subroutine from the Macros dialog box. The result should look like Figure 6-20.

Figure 6-20. Grand Total rows with commas added
0 0

Post a comment