My Kiva

Skydrive




Contact
Ed Ferrero

Pivot Table Tutorial - Part 7

Back

By the way, some of you may be wondering how to format Pivot Table fields: Double-click the "Row&qout; field in the above example, then click the "Number" button, select the "Custom" category and type "mmm-yyyy" as the number format.

5) Using Calculated Fields

It is easy to add fields to a Pivot Table that are a function of other fields. This is most useful when the data source is external to the workbook. For example, open EG5.XLS again and select a cell anywhere within the Pivot Table. Then use the menu on the Pivot Table toolbar Pivot Table-Formulas-Calculated Fields. The following dialog appears.

Insert Calculated Field

Now add a field that calculates the total cost for each record. Type "Cost" in the Name box, double-click "Sales" in the Fields box, the Formula box changes to "=Sales", then type a minus sign and double-click "Margin". Now click the Add button. The dialog should look like this.

Insert Calculated Field

Notice that you can delete or modify the calculated field from this dialog. Press OK, the new calculated field is place in the Pivot Table as a data field. You can now use this new field just like any other.

6) Some Useful VBA Routines for Pivot Tables

If you have written VBA macros before, this last section will provide some useful examples of how to manipulate Pivot Tables with code. If you are not used to programming in VBA, you might want to just use the sample worksheets to run the VBA macros, these provide a handy set of utilities for working with Pivot Tables. The macros are found in EG8.XLS, open this now and press the buttons in the blue band above the Pivot Table. The next section explains how each macro works.

You can also download the EFutil xla. This is an add-in that places the "Utility" menu item on the standard Excel worksheet menus. The Utility menu contains five handy items.

a) Aligning Multiple Data Fields

The default setting in Excel is for multiple data fields to be displayed in rows down the page. We can change this to show the fields side by side. The code uses the Orientation property of the PivotField object.

Sub ToggleAlignment()
' Sets data fields side by side and back again on Pivot

' First, turn off screen refresh
Application.ScreenUpdating = False

' Switches orientation from one to the other
With ActiveSheet.PivotTables(1).PivotFields("Data")
    If .Orientation = xlColumnField Then
        .Orientation = xlRowField
    Else
        .Orientation = xlColumnField
        .Position = 1
    End If
End With

End Sub

b) Formatting a Pivot Table

This can be trickier that it first appears. Here is a general scheme for Pivot Table formatting. It uses the PivotSelect method to format different areas of the Pivot Table.

Sub FormatData()
' Formats each field in Data Fields

Application.ScreenUpdating = False
' Need to make sure selection is on
Application.PivotTableSelection = True

Set pvtTable = ActiveSheet.PivotTables(1)

' Format months as right-aligned with date format
For Each pvtField In pvtTable.PivotFields
    If pvtField = "Month" Then
        pvtTable.PivotSelect "Month[All]", xlLabelOnly
        Selection.HorizontalAlignment = xlRight
        Selection.NumberFormat = "mmm-yy"
    End If
Next pvtField

For Each pvtField In pvtTable.DataFields
    Select Case pvtField
    '   Format % fields to not show DIV ZERO
    Case "Sum of VarMargin"
        pvtTable.PivotSelect pvtField, xlDataOnly
        Selection.NumberFormat = "[Black]0%;[Red](0%);[Black]0%"
        Selection.Font.ColorIndex = 2
    '   Format other fields as numbers
    Case Else
        pvtTable.PivotSelect pvtField, xlDataOnly
        Selection.NumberFormat = "# ##0"
    End Select
Next pvtField

'   Format grand totals to wrap text
For Each pvtField In pvtTable.ColumnFields
    pvtTable.PivotSelect "'Row Grand Total'", xlDataAndLabel
    Selection.WrapText = True
Next pvtField

Range("A3").Select
End Sub

c) Toggling Data Fields

I often find a need to quickly switch between Sales and Margin when looking at Pivot Table data. This macro lets me swap fields quickly without having to go into the Pivot Table Wizard.

Sub ToggleSales()
' Changes data field from Sales to Margin
Application.ScreenUpdating = False
Application.PivotTableSelection = True

Set pvtTable = ActiveSheet.PivotTables(1)

For Each pvtField In pvtTable.DataFields
    Select Case pvtField
        Case "Sum of Sales"
            pvtField.Orientation = xlHidden
            With pvtTable.PivotFields("Margin")
                .Orientation = xlDataField
                .Position = 1
            End With
        Case "Sum of Margin"
            pvtField.Orientation = xlHidden
            With pvtTable.PivotFields("Sales")
                .Orientation = xlDataField
                .Position = 1
            End With
    End Select
Next pvtField

End Sub

d) Grouping and Ungrouping

When you need to group into quarters without going into the menus you can use a macro that changes the Periods array.

Sub GroupQuarters()
' groups pivot table on active sheet by quarter
Application.PivotTableSelection = True
ActiveSheet.PivotTables(1).PivotSelect "Month", xlLabelOnly
Selection.Group Periods:=Array(False, False, False, False, False, True, False)
FormatData
End Sub

Sub GroupMonths()
' groups pivot table on active sheet by quarter
Application.PivotTableSelection = True
ActiveSheet.PivotTables(1).PivotSelect "Month", xlLabelOnly
Selection.Ungroup
FormatData
End Sub

If you have appreciated the time and effort that has gone into providing this tutorial, you may wish to assist us by making a PayPal donation towards the running costs of this website. Just click the button below.




Back to Start

Back