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.

Value Field Settings I

Source Name: Amount Custom Name [sum of Amount

Summarize by | Show values as Summarize value field by

Choose the type of calculation that you want to use to summarize the data from selected field

Summarize by | Show values as Summarize value field by

Choose the type of calculation that you want to use to summarize the data from selected field

isLim

Count

Average

il

Max

Product

v|

Figure 6-21. Value Field Settings dialog box

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.

Figure 6-23. Summary field headings modified
0 0

Post a comment