PivotTables("PivotTable1").PivotFields("Sum of Qty")
.NumberFormat = "#,##0"
End With
End Sub
Run the FormatNumbersComma subroutine from the Macros dialog box. The result should
look like Figure 6-20.
CHAPTER 6 n PIVOTTABLES 242
Figure 6-20. Grand Total rows with commas added
Changing Field Names
By default, Excel uses the name ???Sum of field name??? or ???Count of field name??? when you add
summary value fields to a PivotTable. You can change the names to something with more
visual appeal using VBA code.
Add the Amount field to the Values list in the PivotTable Field List. Change the Count
value to Sum in the Value Field Settings dialog box (as shown in Figure 6-21) by clicking the
Amount field in the Values list and choosing Value Field Settings from the right-click shortcut
menu. Figure 6-22 shows the result of changing the field names.
Figure 6-21. Value Field Settings dialog box
CHAPTER 6 n PIVOTTABLES 243
Figure 6-22. PivotTable showing Sum of Qty and Sum of Amount fields
Use the PivotField.Caption property to change the captions to something more easily
readable.
Add the following subroutine to a standard code module:
Sub ChangeColHeading()
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Qty").Caption = ??
"Item Qty"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Amount").Caption = ??
"Item Amount"
End Sub
Run the code from the Macros dialog box. The result should look like Figure 6-23.
CHAPTER 6 n PIVOTTABLES 244
Figure 6-23.
Pages:
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236