Pivot Cache Auditor: Find Duplicate and Orphaned Pivot Caches Draining Your Workbook

TL;DR: You inherit a workbook with 8 pivot tables built from 14 separate caches. Some caches pull from identical source ranges (doubling your file size for no reason). Others are orphaned — the pivot table was deleted but the cache lived on, consuming memory and slowing recalc. This macro catalogs every cache on a single report sheet, flags duplicates and orphans, and removes the dead ones in one click.

The Problem

The Reynolds Group consolidation file takes 45 seconds to recalculate. It’s only 22 sheets. You know pivot tables can be heavy, so you open the workbook with the Queries & Connections pane and see… nothing useful. Pivot caches don’t appear there. They don’t appear in Name Manager either. They’re invisible metadata — Excel creates a new cache every time someone builds a pivot table from a slightly different range or copies a pivot to a new sheet.

In this file, four different preparers over three years each built their own pivot tables from the same TB data. Same source, same columns, same rows. But each pivot has its own cache because someone selected A1:F200 instead of A:F, or copied a pivot tab and Excel silently duplicated the cache. Now you’re dragging around 8 copies of the same dataset in memory. Two of the caches are from pivot tables that were deleted in 2023 — the tabs are gone but the caches never left. You have no way to even see this without VBA. This macro is the only tool that exists for this problem.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with pivot tables — any file that’s been through multiple preparers or engagement years

What the macro does:

Scans ThisWorkbook.PivotCaches and catalogs every cache on a new Pivot-Caches report sheet
Counts how many pivot tables depend on each cache
Flags duplicate source ranges (same data, separate caches — file bloat)
Flags orphaned caches (zero pivot tables — dead weight)
On confirm, removes orphaned caches

Limitations:

The macro only catalogs caches — it does NOT consolidate duplicate caches. Consolidating requires repointing pivot tables to a shared cache, which changes the workbook structure. If you need consolidation, use the report as a map to rebuild pivots manually
Orphaned caches are detected by counting pivot tables that reference each cache index. If a pivot table exists but its CacheIndex is inaccessible (protected, corrupted), it may be missed
Some Excel versions may not allow programmatic deletion of pivot caches via PivotCaches(i).Delete. If deletion fails, the macro reports it and continues
RecordCount and RefreshDate may return errors on external data source caches (OLAP, Power Pivot data model). The macro handles these gracefully

#The Macro

Option Explicit

Sub PivotCacheAuditor()
    ' ── Pivot Cache Auditor ────────────────────────────
    ' Catalogs every pivot cache in the workbook.
    ' Creates a "Pivot-Caches" report sheet showing the
    ' cache index, source data, record count, last
    ' refresh date, how many pivots use each cache,
    ' whether it's orphaned, and whether it duplicates
    ' another cache's source data.
    '
    ' On confirm, removes all orphaned caches (those
    ' with zero pivot tables depending on them).
    ' Duplicate caches are flagged but NOT removed —
    ' they still serve active pivot tables.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Pivot-Caches"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim pc As PivotCache
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rpt As Worksheet
    Dim outRow As Long
    Dim i As Long, j As Long
    Dim totalCaches As Long
    Dim orphanCount As Long
    Dim dupCount As Long
    Dim ptRefCount() As Long    ' Pivot count per cache index
    Dim cacheSrc() As String    ' Source data string per cache index
    Dim sourceDesc As String
    Dim deleted As Long
    Dim msg As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    totalCaches = ThisWorkbook.PivotCaches.Count

    If totalCaches = 0 Then
        MsgBox "No pivot caches found in this workbook.", vbInformation
        GoTo CleanUp
    End If

    ' ── Step 1: Count pivot tables per cache index ─────
    ReDim ptRefCount(1 To totalCaches)

    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        For Each pt In ws.PivotTables
            If pt.CacheIndex > 0 And pt.CacheIndex <= totalCaches Then
                ptRefCount(pt.CacheIndex) = _
                    ptRefCount(pt.CacheIndex) + 1
            End If
        Next pt
        On Error GoTo CleanUp
    Next ws

    ' ── Step 2: Collect source data per cache ──────────
    ReDim cacheSrc(1 To totalCaches)

    For i = 1 To totalCaches
        On Error Resume Next
        sourceDesc = ThisWorkbook.PivotCaches(i).SourceData
        If Err.Number <> 0 Or sourceDesc = "" Then
            ' Try the connection string for external sources
            sourceDesc = ThisWorkbook.PivotCaches(i).Connection
        End If
        On Error GoTo CleanUp

        If sourceDesc = "" Then
            sourceDesc = "(unavailable)"
        End If
        cacheSrc(i) = sourceDesc
    Next i

    ' ── Step 3: Create report sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rpt = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    rpt.Name = OUT_SHEET

    ' ── Headers ────────────────────────────────────────
    rpt.Range("A1:G1").Value = Array( _
        "Index", "Source Data / Connection", "Records", _
        "Last Refresh", "PivotTables", "Status", _
        "Notes")
    With rpt.Range("A1:G1")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
        .WrapText = True
    End With

    ' ── Step 4: Populate the report ────────────────────
    outRow = 2
    orphanCount = 0
    dupCount = 0

    For i = 1 To totalCaches
        Set pc = ThisWorkbook.PivotCaches(i)

        ' ── Column A: Cache index ──────────────────────
        rpt.Cells(outRow, 1).Value = i
        rpt.Cells(outRow, 1).HorizontalAlignment = xlCenter
        rpt.Cells(outRow, 1).Font.Color = RGB(140, 140, 140)

        ' ── Column B: Source data description ──────────
        rpt.Cells(outRow, 2).Value = cacheSrc(i)
        rpt.Cells(outRow, 2).WrapText = True

        ' ── Column C: Record count ─────────────────────
        On Error Resume Next
        rpt.Cells(outRow, 3).Value = pc.RecordCount
        If Err.Number <> 0 Then
            rpt.Cells(outRow, 3).Value = "N/A"
        End If
        Err.Clear
        On Error GoTo CleanUp

        ' ── Column D: Last refresh date ────────────────
        On Error Resume Next
        If pc.RefreshDate > 0 Then
            rpt.Cells(outRow, 4).Value = pc.RefreshDate
            rpt.Cells(outRow, 4).NumberFormat = _
                "mmm d, yyyy hh:mm"
        Else
            rpt.Cells(outRow, 4).Value = "Never refreshed"
        End If
        Err.Clear
        On Error GoTo CleanUp

        ' ── Column E: PivotTable count ─────────────────
        rpt.Cells(outRow, 5).Value = ptRefCount(i)
        rpt.Cells(outRow, 5).HorizontalAlignment = xlCenter

        ' ── Column F: Status ───────────────────────────
        If ptRefCount(i) = 0 Then
            rpt.Cells(outRow, 6).Value = "ORPHANED"
            rpt.Cells(outRow, 6).Interior.Color = _
                RGB(254, 202, 202)
            rpt.Cells(outRow, 6).Font.Bold = True
            rpt.Cells(outRow, 7).Value = _
                "No pivot table uses this cache. Safe to remove."
            orphanCount = orphanCount + 1
        Else
            rpt.Cells(outRow, 6).Value = "ACTIVE"
            rpt.Cells(outRow, 6).Interior.Color = _
                RGB(220, 252, 231)
            rpt.Cells(outRow, 7).Value = _
                ptRefCount(i) & " pivot table(s) use this cache."
        End If

        outRow = outRow + 1
    Next i

    ' ── Step 5: Duplicate source detection ─────────────
    ' An active cache is a duplicate if another cache
    ' (with a lower index) uses the same source data.
    For i = 1 To totalCaches
        If ptRefCount(i) > 0 And cacheSrc(i) <> "(unavailable)" Then
            For j = 1 To i - 1
                If cacheSrc(i) = cacheSrc(j) And _
                   cacheSrc(j) <> "(unavailable)" Then
                    rpt.Cells(i + 1, 7).Value = _
                        rpt.Cells(i + 1, 7).Value & _
                        " DUPLICATE — same source as cache #" & j & "."
                    dupCount = dupCount + 1
                    Exit For
                End If
            Next j
        End If
    Next i

    ' ── Step 6: Format the report sheet ────────────────
    With rpt
        .Columns("A").ColumnWidth = 8
        .Columns("B").ColumnWidth = 52
        .Columns("C").ColumnWidth = 10
        .Columns("D").ColumnWidth = 18
        .Columns("E").ColumnWidth = 14
        .Columns("F").ColumnWidth = 12
        .Columns("G").ColumnWidth = 58
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
        ActiveWindow.Zoom = 90
    End With

    ' ── Step 7: Summary message ────────────────────────
    msg = "Found " & totalCaches & " pivot cache(s)." & _
          vbCrLf & vbCrLf & _
          "  ACTIVE: " & (totalCaches - orphanCount) & vbCrLf & _
          "  ORPHANED: " & orphanCount

    If dupCount > 0 Then
        msg = msg & vbCrLf & "  DUPLICATE SOURCES: " & dupCount & _
              " cache(s) share identical source data."
    End If

    msg = msg & vbCrLf & vbCrLf & _
          "Full report on '" & OUT_SHEET & "' sheet."

    If orphanCount > 0 Then
        msg = msg & vbCrLf & vbCrLf & _
              "Remove " & orphanCount & " orphaned cache(s)?"

        If MsgBox(msg, vbYesNo + vbQuestion, _
                  "Pivot Cache Audit") = vbYes Then
            ' ── Step 8: Delete orphaned caches ─────────────────
            ' Must iterate in reverse — deleting an item
            ' shifts the remaining indexes
            deleted = 0
            For i = totalCaches To 1 Step -1
                If ptRefCount(i) = 0 Then
                    On Error Resume Next
                    ThisWorkbook.PivotCaches(i).Delete
                    If Err.Number = 0 Then
                        deleted = deleted + 1
                    Else
                        rpt.Cells(i + 1, 7).Value = _
                            rpt.Cells(i + 1, 7).Value & _
                            " DELETE FAILED."
                    End If
                    On Error GoTo CleanUp
                End If
            Next i
            MsgBox "Removed " & deleted & " orphaned " & _
                   "cache(s).", vbInformation, "Done"
        Else
            MsgBox "No caches removed. Report remains " & _
                   "for review.", vbInformation, "Cancelled"
        End If
    Else
        MsgBox msg & vbCrLf & vbCrLf & _
              "No orphaned caches to remove.", _
              vbInformation, "Pivot Cache Audit"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & _
               Err.Description, vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Why pivot caches matter (and why Excel hides them)

Every pivot table in Excel is backed by a pivot cache — an in-memory copy of the source data. When you build two pivot tables from the same data range and Excel reuses the cache, you get one copy of the data serving both pivots. When the caches are separate, you store the same data twice. In a large tax workpaper — a TB with 15,000 rows and 20 columns feeding five pivot tables — duplicate caches can add megabytes of unnecessary memory overhead and force Excel to recalculate each cache independently.

Excel creates a separate cache when:

You select a slightly different range (e.g., A1:F200 vs. A1:F200)
You copy a pivot table sheet and Excel duplicates the entire cache instead of sharing
You import data from an external source and Excel treats each import as a new cache
A pivot table is deleted but its cache survives (Excel doesn’t garbage- collect orphaned caches on its own)

This macro is the X-ray. It shows you what’s actually stored in memory — information Excel’s built-in UI intentionally hides.

#Counting pivot references per cache

For Each ws In ThisWorkbook.Worksheets
    On Error Resume Next
    For Each pt In ws.PivotTables
        If pt.CacheIndex > 0 And pt.CacheIndex <= totalCaches Then
            ptRefCount(pt.CacheIndex) = _
                ptRefCount(pt.CacheIndex) + 1
        End If
    Next pt
    On Error GoTo CleanUp
Next ws

Every PivotTable object exposes a .CacheIndex property — an integer that maps directly to an item in the PivotCaches collection. The macro walks every sheet, every pivot table, and increments a counter for the matching cache index. A cache with a count of zero has no pivot table attached — it’s an orphan.

The On Error Resume Next wrapper is necessary because a corrupted or inaccessible pivot table can throw an error on .CacheIndex access. If that happens, the macro skips the pivot and moves on — one broken pivot shouldn’t block the entire audit.

#The source data lookup: range first, connection second

sourceDesc = ThisWorkbook.PivotCaches(i).SourceData
If Err.Number <> 0 Or sourceDesc = "" Then
    sourceDesc = ThisWorkbook.PivotCaches(i).Connection
End If

Pivot caches come in two flavors: range-based (data lives in the same workbook) and external (data comes from a database, Power Query, or another workbook). For range-based caches, .SourceData returns something like "TB Data!R1C1:R15001C20" — the R1C1-format range address. For external caches, .SourceData is empty and .Connection contains the ODBC connection string or OLE DB query.

The macro tries .SourceData first because it’s the most informative (you can read “Sheet1!A1:F200” and know exactly what data feeds this pivot). If that’s empty, it falls back to .Connection, which for external sources might read something like "OLEDB;Provider=SQLOLEDB.1;Data Source=ACCT-SERVER...".

#Duplicate detection: same source, different cache

For j = 1 To i - 1
    If cacheSrc(i) = cacheSrc(j) And _
       cacheSrc(j) <> "(unavailable)" Then
        rpt.Cells(i + 1, 7).Value = _
            rpt.Cells(i + 1, 7).Value & _
            " DUPLICATE — same source as cache #" & j & "."
        dupCount = dupCount + 1
        Exit For
    End If
Next j

After building the source data array, the macro scans each active cache against every earlier cache in the list. If two caches have identical source strings (e.g., both read "TB!R1C1:R15001C20"), the later one is flagged as a duplicate.

The comparison uses simple string equality — if someone selected A1:F200 and someone else selected A1:F15000 from the same sheet, they won’t match. This is intentional: slightly different ranges produce different caches, and the macro shows you the actual range so you can decide whether to consolidate or leave it alone.

Duplicates are flagged but NOT removed. They serve active pivot tables, and deleting a cache would break those pivots. Consolidation requires rebuilding the pivot tables to share a single cache — a manual process the report helps you decide on.

#Reverse-order deletion protects against index shifting

For i = totalCaches To 1 Step -1
    If ptRefCount(i) = 0 Then
        On Error Resume Next
        ThisWorkbook.PivotCaches(i).Delete
        ...

This is the same pattern used in delete-broken-named-ranges: when you remove an item from a VBA collection, all subsequent items shift up by one index. Iterating forward would skip every other orphan after the first deletion. Iterating backward ensures every index that hasn’t been visited yet is still valid.

The On Error Resume Next around .Delete catches cases where Excel refuses to remove the cache — some versions lock caches created by Power Query or the Data Model, and deletion fails silently rather than crashing the macro.

#Orphans happen when you least expect them

An orphaned cache is created whenever a pivot table is deleted without also removing its cache. Common scenarios:

Deleting a sheet that contained the only pivot table using a cache
Clearing a pivot table range (select all, press Delete) — the cache survives
Copying a pivot, deleting the copy — if Excel split the cache during the copy (which it does unpredictably), the copy’s cache becomes orphaned

Excel never shows you these orphans. They don’t appear in Name Manager, the Queries & Connections pane, or any built-in dialog. They silently consume memory and contribute to file size. The only way to find them is to iterate ThisWorkbook.PivotCaches and check for zero references — exactly what this macro does.

#Why the report sheet goes first

Set rpt = ThisWorkbook.Worksheets.Add( _
    Before:=ThisWorkbook.Worksheets(1))

The report sheet is placed at the beginning of the workbook (before all other sheets). This is different from the hard-coded-number-detector which puts output at the end. The reason: the pivot cache report is an audit tool you reference while working through the file. Having it as Tab 1 means you can switch to it with Ctrl+PageUp from Tab 2, see which caches serve which pivots, and navigate to the relevant sheets. An audit sheet at the end requires scrolling through 20 tabs to find it.

#Adapt It

Run on the active workbook from Personal Macro Workbook. Store the macro in PERSONAL.XLSB and replace all ThisWorkbook references with ActiveWorkbook:

Dim wb As Workbook
Set wb = ActiveWorkbook

totalCaches = wb.PivotCaches.Count
' Then use wb.PivotCaches, wb.Worksheets, etc.

This lets you audit any workbook without pasting the macro into it first.

Add cache memory usage. If your Excel version supports it (Excel 2019+), add the .MemoryUsed property to the report:

' In Step 4, after Column D:
rpt.Cells(outRow, 4).Offset(0, 1).Value = pc.MemoryUsed

Warning: MemoryUsed isn’t available in all Excel versions. Wrap in On Error Resume Next.

Export to CSV for comparison across workbook versions. Add an export step after building the report:

Dim csvPath As String
csvPath = ThisWorkbook.Path & "\cache-audit-" & _
          Format(Now, "yyyy-mm-dd_hhmm") & ".csv"
rpt.SaveAs csvPath, xlCSV
MsgBox "Report exported to " & csvPath, vbInformation

Run the audit on the February version, run it on the March version, and diff the CSV files to see what changed.

Filter to only show orphans or duplicates. Add an AutoFilter to the report sheet after it’s built:

rpt.Range("A1:G1").AutoFilter
' User can now filter Column F for "ORPHANED" or
' Column G for text containing "DUPLICATE"

Warn about duplicate source caches before the confirm dialog. Add a second confirm if duplicates were found — consolidating them will reduce file size and memory but requires rebuilding the pivot tables:

If dupCount > 0 Then
    MsgBox dupCount & " cache(s) share identical source " & _
           "data with another cache." & vbCrLf & vbCrLf & _
           "To consolidate: rebuild the affected pivot " & _
           "tables to share a single source range. " & _
           "See the report for which caches are involved.", _
           vbInformation, "Duplicate Sources Found"
End If

See also: Workbook Bloat Analyzer — get a full file health report including pivot caches alongside custom styles, named ranges, and connections. Run the Delete Broken Named Ranges macro to clean up the names that disappeared with deleted sheets.

Download the pivot cache auditor sample workbook (3 data sheets with instructions to build 4 pivot tables — creates real pivot caches)

xlsx 13 KB