Pivot Table Per-Item Color Scales

Pivot Table Per-Item Color Scales

Let’s take another journey to the nexus of conditional formatting and pivot tables. My latest foray involved adding per-item color-scales to a pivot table, something like this, where the color scale is applied separately to each quarter:

color scale by quarter

You can see, for example, that the very high sales of $9,999,999 for January in Region 1 affect the color scale for the other cells in Quarter 1. However, the color scale within other quarters are unaffected, and a 99,999 value in Quarter 2 gets the same green that the much higher value did in Quarter 1.

Here’s another view with each month getting its own scale:

color scale by month

Finally, this shows icon sets by region. Notice that although they span each region for all months, the icon sets ignore the subtotals, which is often preferable:

icons by region

My code allows you to include or include or ignore subtotals in each item’s color scale. I went about this a couple of ways before settling on the PivotTable.PivotSelect method. I learned about this method recently in a comment by Mike Alexander on one of Jeff Weir’s massive DDOE posts. I can’t find any good documentation, but in Excel 2007 forward you can do things like:

pvt.PivotSelect "'Row Grand Total'", xlDataOnly

Select a pivot table’s Row Grand Total area

pvt.PivotSelect "'Region'['East';Data]", xlDataOnly

Select the data rows for the East PivotItem of the Region PivotField

(There is a PivotSelect method in 2003, but these types of statements failed.)

It looks like PivotSelect is the VBA version of clicking, for example, a single subtotal in a pivot table and thereby selecting them all. I figured out the syntax by turning on the Macro Recorder and doing those types of selections:

selecting pivot parts in Excel

I’m not crazy about using PivotSelect – I assume all that selecting will slow things down on big pivot tables. On the other hand it seems to work consistently. I had much more complicated code that worked most of the time, but only most.

Here’s the code, which runs in Excel 2007 on:

Sub PivotItemCF(pvtFieldToFormat As Excel.PivotField, IgnoreSubtotals As Boolean)
Dim pvt As Excel.PivotTable
Dim pvtItem As Excel.PivotItem
Dim pvtField As Excel.PivotField
Dim rngTest As Excel.Range

'Lots of selecting!
Application.ScreenUpdating = False

Set pvt = pvtFieldToFormat.Parent
With pvt
    For Each pvtItem In pvtFieldToFormat.PivotItems
        'A distressing number of possible errors from hidden items.
        'If the item has no DataRange ignore it.
        On Error Resume Next
        Set rngTest = pvtItem.DataRange
        If Err.Number = 0 Then
            'Specify the Field name in case there are multiple items
            'with the same name,for example from Grouping.
            'Surround PivotItem names with single quotes in case they have spaces.
            pvt.PivotSelect pvtFieldToFormat.Name & "['" & pvtItem.Name & "';Data;Total]", xlDataOnly
            'Call routine that does the formatting
            FormatRange Selection
        End If
        On Error GoTo 0
    Next pvtItem
   
    If IgnoreSubtotals Then
        'Clear the FormatConditions from Row and Column fields
        For Each pvtField In pvt.PivotFields
            If pvtField.Orientation = xlRowField Or pvtField.Orientation = xlColumnField Then
                'I can't figure a test for whether the subtotals are visible
                'so just blindly firing away.
                On Error Resume Next
                .PivotSelect "'" & pvtField.Name & "'[All;Total]", xlDataOnly
                If Err.Number = 0 Then
                    Selection.FormatConditions.Delete
                End If
                On Error GoTo 0
            End If
        Next pvtField
        .PivotSelect "'Row Grand Total'", xlDataOnly
        Selection.FormatConditions.Delete
        .PivotSelect "'Column Grand Total'", xlDataOnly
        Selection.FormatConditions.Delete
    Else
    End If
End With

Application.ScreenUpdating = True
End Sub

The FormatRange routine is just Macro Recorder code generated by selecting part of the pivot table and applying a color scale. You can do the same thing for data bars or icons.

In the sample workbook, the code is triggered by a WorkSheet_Change event that fires whenever one of the three yellow data validation cells is changed.

One thought on “Pivot Table Per-Item Color Scales

  1. Pingback: Excel Roundup 20131216 | Contextures Blog

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>