Clean up Pivot Cache

What can you learn here?

• Space used by pivot cache
• Match cache to pivots
• Clear out cache errors

Using Pivot Caches

Excel Introduced the concept of pivot caches to try to minimize duplicated data when you have multiple pivot tables in your workbook. Here is how to find out which caches you have, how big they are and which pivot tables are using which caches. Armed with this information you should be able to optimize your pivot usage to decrease the sizeof you workbook.

What are pivot Caches

These are shared cube data that is used by multiple pivot tables in a workbook. They are in a collection called workbook.pivotcaches.

How to get a report of how big each cache is, and which pivot tables are sharing them

This code just outputs the result to a msgBox  with a summary of usage for each pivot cache. If you want to make it more elaborate, then format the output to a worksheet.

Sub PivotCacheReport()
    Dim pc As PivotCache
    Dim s As String, sn As String
    Dim ws As Worksheet
    Dim pt As PivotTable
    With ActiveWorkbook
        For Each pc In .PivotCaches
            s = "Pivotcache " & CStr(pc.Index) & " uses " & CStr(pc.MemoryUsed) & " and has " _
                    & CStr(pc.RecordCount) & " records"
            s = s & Chr(10) & "The following pivot tables use it"
            For Each ws In .Worksheets
                sn = ws.Name
                For Each pt In ws.PivotTables
                    If pt.CacheIndex = pc.Index Then
                        If Len(sn) > 0 Then
                            s = s & Chr(10) & sn & Chr(10)
                            sn = ""
                        End If
                        s = s & Replace(pt.Name, "PivotTable", "PT") & ","
                    End If
                Next pt
            Next ws
            MsgBox (s)
        Next pc
        sn = Chr(10) & "Couldnt find the pivotcache for these pivot tables"
        s = ""
        For Each ws In .Worksheets
            For Each pt In ws.PivotTables
                If pt.CacheIndex < 1 Or pt.CacheIndex > .PivotCaches.Count Then
                    s = s & Chr(10) & ws.Name & ":" & Replace(pt.Name, "PivotTable", "PT")
                End If
            Next pt
        Next ws
        If (Len(s) > 0) Then
            MsgBox (sn & s)
        End If
    End With
End Sub

Clearing out failed pivot tables

Sometimes you delete the input to a pivot table. Excel allows these pivot tables dependent on data no longer there to hang around. This will delete any pivot tables in your workbook that are in this invalid state.

Sub PivotCacheClearRubbish()
    Dim pc As PivotCache
    Dim ws As Worksheet
    With ActiveWorkbook
        For Each pc In .PivotCaches
            pc.MissingItemsLimit = xlMissingItemsNone
        Next pc
    End With
End Sub

For more tips like this, take a look at Get Started Snippets In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. You can also submit anything you want considered for publication on this site to our forum.