Chart Data Source Auditor: Find Every Chart That's Feeding Off a Ghost Sheet

TL;DR: You open an inherited workpaper. A chart on the depreciation summary is blank. Another shows data you can’t trace to any visible sheet. A third throws errors when you click it. This macro catalogs every chart in the workbook — embedded and chart sheets — and flags any that reference deleted sheets (#REF!) or hidden data. One report sheet, one MsgBox, zero digging through chart menus.

The Problem

You inherit the Patel consolidated workpaper from the prior preparer who left mid-engagement. The file has 22 tabs, and three of them have charts — a bar chart on the Summary tab showing revenue by entity, a pie chart on the Depreciation tab showing asset class breakdown, and a line chart on Sched-M1 that renders as an empty white rectangle.

You click the empty chart. Nothing happens. You right-click → Select Data. Excel shows a #REF! in the series formula. The source data was on a sheet called “M1-Backup” that someone deleted six months ago. Now you’re wondering: are there other charts in this workbook with the same problem? Charts that reference hidden sheets? Chart sheets you haven’t even noticed?

You could click through 22 tabs, right-click every chart, inspect every data source. Or you could run this macro and get a complete chart inventory with every source flagged in under two seconds.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook with embedded charts or chart sheets — the macro catalogs both
The workbook must contain the macro (or store it in Personal Macro Workbook for use across files)

What the macro does:

Scans every visible worksheet for embedded ChartObject shapes
Scans the Charts collection for standalone chart sheets
For each chart, reports the name, location, chart type, number of series, and the first series’ source formula
Checks each series formula for #REF! (indicating a deleted source sheet or range)
Checks if any referenced sheet is hidden or VeryHidden
Produces a “Chart-Audit” report sheet with all findings and flags
Shows a summary MsgBox with counts: total charts, #REF! sources, hidden-sheet references

What the macro does NOT do:

It does not fix broken sources — it only reports them. Repairing a chart requires knowing what the source should be
It does not modify any chart, sheet, or data. It is strictly read-only
It does not scan charts on hidden sheets — Excel does not expose ChartObjects on hidden or VeryHidden sheets. If a chart itself is on a hidden sheet, it won’t appear in the report

Limitations:

Only checks the first series of each chart as a representative data source. If a chart has 12 series with 12 different sources, only the first is reported. This is intentional — in tax workpapers, multi-series charts almost always pull from a single contiguous range
Chart type detection covers the 20 most common types. Exotic chart types (sunburst, waterfall, treemap, funnel, box & whisker) report as “Other” with the numeric type code
Hidden-sheet detection checks only whether the referenced sheet is hidden or VeryHidden at the moment the macro runs. If a sheet was hidden after the chart was created but the source is still valid, the flag is informational, not necessarily a problem

#The Macro

Option Explicit

Sub ChartDataSourceAuditor()
    ' ── Chart Data Source Auditor ─────────────────────
    ' Catalogs every chart in the workbook — embedded
    ' charts and standalone chart sheets. Reports name,
    ' location, chart type, series count, and data
    ' source. Flags #REF! sources and references to
    ' hidden sheets. Produces a "Chart-Audit" report.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Chart-Audit"

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet, co As ChartObject, ch As Chart
    Dim srs As Series
    Dim wsOut As Worksheet
    Dim outRow As Long, chartCount As Long
    Dim refCount As Long, hiddenCount As Long
    Dim srcFormula As String

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

    ' ── Remove old report if it exists ─────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    ' ── Create report sheet ────────────────────────────
    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Write headers ──────────────────────────────────
    With wsOut.Range("A1:F1")
        .Value = Array("Chart Name", "Sheet Location", _
                        "Chart Type", "Series", _
                        "Data Source (Series 1)", "Flags")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With
    wsOut.Rows(1).AutoFilter

    outRow = 2
    chartCount = 0
    refCount = 0
    hiddenCount = 0

    ' ── Scan embedded charts on visible worksheets ─────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = OUT_SHEET Then GoTo NextSheet
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        If ws.ChartObjects.Count = 0 Then GoTo NextSheet

        For Each co In ws.ChartObjects
            Set ch = co.Chart
            chartCount = chartCount + 1

            ' Basic info
            wsOut.Cells(outRow, 1).Value = co.Name
            wsOut.Cells(outRow, 2).Value = ws.Name
            wsOut.Cells(outRow, 3).Value = ChartTypeName(ch.ChartType)
            wsOut.Cells(outRow, 4).Value = ch.SeriesCollection.Count

            ' Parse first series for source + flags
            If ch.SeriesCollection.Count > 0 Then
                srcFormula = ch.SeriesCollection(1).Formula
                wsOut.Cells(outRow, 5).Value = "'" & srcFormula

                ' Build flags
                Dim flags As String
                flags = ""
                If InStr(1, srcFormula, "#REF!") > 0 Then
                    flags = flags & "#REF! Source, "
                    refCount = refCount + 1
                End If
                If HasHiddenSource(srcFormula) Then
                    flags = flags & "Hidden Sheet Ref, "
                    hiddenCount = hiddenCount + 1
                End If
                If Len(flags) > 0 Then
                    flags = Left(flags, Len(flags) - 2)
                Else
                    flags = "OK"
                End If
                wsOut.Cells(outRow, 6).Value = flags
            Else
                wsOut.Cells(outRow, 5).Value = "(no series)"
                wsOut.Cells(outRow, 6).Value = "No data"
            End If

            outRow = outRow + 1
        Next co

NextSheet:
    Next ws

    ' ── Scan chart sheets ──────────────────────────────
    Dim i As Long
    For i = 1 To ThisWorkbook.Charts.Count
        Set ch = ThisWorkbook.Charts(i)
        chartCount = chartCount + 1

        wsOut.Cells(outRow, 1).Value = ch.Name
        wsOut.Cells(outRow, 2).Value = ch.Name & " (chart sheet)"
        wsOut.Cells(outRow, 3).Value = ChartTypeName(ch.ChartType)
        wsOut.Cells(outRow, 4).Value = ch.SeriesCollection.Count

        If ch.SeriesCollection.Count > 0 Then
            srcFormula = ch.SeriesCollection(1).Formula
            wsOut.Cells(outRow, 5).Value = "'" & srcFormula

            Dim flagsCS As String
            flagsCS = ""
            If InStr(1, srcFormula, "#REF!") > 0 Then
                flagsCS = flagsCS & "#REF! Source, "
                refCount = refCount + 1
            End If
            If HasHiddenSource(srcFormula) Then
                flagsCS = flagsCS & "Hidden Sheet Ref, "
                hiddenCount = hiddenCount + 1
            End If
            If Len(flagsCS) > 0 Then
                flagsCS = Left(flagsCS, Len(flagsCS) - 2)
            Else
                flagsCS = "OK"
            End If
            wsOut.Cells(outRow, 6).Value = flagsCS
        Else
            wsOut.Cells(outRow, 5).Value = "(no series)"
            wsOut.Cells(outRow, 6).Value = "No data"
        End If

        outRow = outRow + 1
    Next i

    ' ── Format report ──────────────────────────────────
    With wsOut
        .Columns("A:F").AutoFit
        .Columns("E").ColumnWidth = 65
    End With
    wsOut.Activate
    wsOut.Range("A1").Select
    ActiveWindow.FreezePanes = True

    ' ── Build summary message ──────────────────────────
    Dim msg As String
    msg = "Found " & chartCount & " chart(s) in this workbook."

    If refCount + hiddenCount > 0 Then
        msg = msg & vbCrLf & vbCrLf
        If refCount > 0 Then
            msg = msg & "⚠ " & refCount & " chart(s) have #REF! (broken) sources." & vbCrLf
        End If
        If hiddenCount > 0 Then
            msg = msg & "⚠ " & hiddenCount & " chart(s) reference hidden data sheets." & vbCrLf
        End If
    Else
        msg = msg & vbCrLf & vbCrLf & "✓ All chart sources are valid."
    End If

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

    MsgBox msg, vbInformation, "Chart Audit Complete"

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

' ── Helper: readable chart type name ──────────────────
Private Function ChartTypeName(ct As XlChartType) As String
    Select Case ct
        Case xlColumnClustered:  ChartTypeName = "Column (Clustered)"
        Case xlColumnStacked:    ChartTypeName = "Column (Stacked)"
        Case xlBarClustered:     ChartTypeName = "Bar (Clustered)"
        Case xlBarStacked:       ChartTypeName = "Bar (Stacked)"
        Case xlLine:             ChartTypeName = "Line"
        Case xlLineMarkers:      ChartTypeName = "Line (Markers)"
        Case xlPie:              ChartTypeName = "Pie"
        Case xlPieExploded:      ChartTypeName = "Pie (Exploded)"
        Case xlXYScatter:        ChartTypeName = "Scatter (XY)"
        Case xlXYScatterLines:   ChartTypeName = "Scatter (Lines)"
        Case xlArea:             ChartTypeName = "Area"
        Case xlAreaStacked:      ChartTypeName = "Area (Stacked)"
        Case xlDoughnut:         ChartTypeName = "Doughnut"
        Case xlRadar:            ChartTypeName = "Radar"
        Case xlConeBarClustered: ChartTypeName = "Cone (Bar)"
        Case xlConeColClustered: ChartTypeName = "Cone (Column)"
        Case xlCylinderBarClustered: ChartTypeName = "Cylinder (Bar)"
        Case xlCylinderColClustered: ChartTypeName = "Cylinder (Column)"
        Case xlPyramidBarClustered:  ChartTypeName = "Pyramid (Bar)"
        Case xlPyramidColClustered:  ChartTypeName = "Pyramid (Column)"
        Case Else: ChartTypeName = "Other (Type " & ct & ")"
    End Select
End Function

' ── Helper: check if a series formula references a ────
'           sheet that is currently hidden              '
Private Function HasHiddenSource(formula As String) As Boolean
    ' Series formula looks like:
    ' =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
    ' Extract sheet names between commas that contain "!"
    Dim i As Long, pos As Long, endPos As Long
    Dim sheetRef As String

    For pos = 1 To Len(formula)
        If Mid(formula, pos, 1) = "!" Then
            ' Walk backwards to find the start of the sheet ref
            ' (after a comma, open paren, or start of string)
            For i = pos - 1 To 1 Step -1
                If Mid(formula, i, 1) = "," Or _
                   Mid(formula, i, 1) = "(" Then
                    sheetRef = Mid(formula, i + 1, pos - i - 1)
                    Exit For
                End If
            Next i
            If Len(sheetRef) = 0 Then
                sheetRef = Left(formula, pos - 1)
            End If

            ' Strip single-quote wraps if present
            If Left(sheetRef, 1) = "'" And _
               Right(sheetRef, 1) = "'" Then
                sheetRef = Mid(sheetRef, 2, Len(sheetRef) - 2)
            End If

            ' Check if this sheet exists and is hidden
            On Error Resume Next
            Dim testWs As Worksheet
            Set testWs = ThisWorkbook.Worksheets(sheetRef)
            If Not testWs Is Nothing Then
                If testWs.Visible <> xlSheetVisible Then
                    HasHiddenSource = True
                    Exit Function
                End If
            End If
            On Error GoTo 0

            sheetRef = ""
        End If
    Next pos

    HasHiddenSource = False
End Function

#How It Works

#Two chart collections, one report

Excel stores charts in two separate places. Embedded charts live on worksheets as ChartObject shapes — you iterate them with ws.ChartObjects. Standalone chart sheets live in ThisWorkbook.Charts — a completely different collection that most VBA developers never touch.

' Embedded: ws.ChartObjects → co.Chart
For Each co In ws.ChartObjects
    Set ch = co.Chart
    ' ...

' Chart sheets: ThisWorkbook.Charts → direct Chart object
For i = 1 To ThisWorkbook.Charts.Count
    Set ch = ThisWorkbook.Charts(i)

The macro reports both in the same output so you see every chart in one view. Chart sheets are identified in the “Sheet Location” column with “(chart sheet)” appended so you know at a glance whether you’re looking at an embedded chart or a standalone sheet.

#Series formulas tell you everything

A chart series formula looks like this:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)

That’s: =SERIES(name, x-values, y-values, plot-order). The macro reads the first series formula via ch.SeriesCollection(1).Formula and parses it for two things:

#REF! check. If the source sheet or range was deleted, Excel replaces that part of the formula with #REF!. A simple InStr check catches it.
Hidden sheet check. The helper HasHiddenSource extracts every sheet reference from the formula (looks for ! tokens and walks backward to the previous comma or parenthesis), then checks ws.Visible for each referenced sheet.

Both flags can appear on the same chart — a chart might reference three sheets, one of which is deleted (#REF!) and another which is hidden. The “Flags” column shows both, separated by commas.

#Why only the first series as representative source

If ch.SeriesCollection.Count > 0 Then
    srcFormula = ch.SeriesCollection(1).Formula

Most tax workpaper charts pull all their series from a single contiguous range. A bar chart showing revenue by entity for 2024–2027 has four series, all sourced from TB!$B$4:$E$7. Checking the first series covers the representative case. If series 1 references TB and series 2 references a deleted sheet, you’ll still catch it — but the rare case where only series 2 is broken won’t be flagged until you inspect the full formula in the report.

If your firm uses charts with heterogeneous data sources (each series from a different sheet), change the 1 to loop through all series:

Dim j As Long
For j = 1 To ch.SeriesCollection.Count
    srcFormula = ch.SeriesCollection(j).Formula
    ' ... check each ...
Next j

The report writes the first series formula with a leading apostrophe so Excel doesn’t try to interpret it as a formula. The ' prefix is invisible in the cell display — it only prevents formula evaluation.

#HasHiddenSource — the sheet-name parser

The HasHiddenSource helper walks through the series formula string looking for ! characters (the separator between sheet name and cell reference in Excel formulas). For each ! found, it walks backward to the previous , or ( to extract the sheet name, strips single-quote wrappers (sheets with spaces get quoted), and checks ThisWorkbook.Worksheets(name).Visible.

If Mid(formula, pos, 1) = "!" Then
    For i = pos - 1 To 1 Step -1
        If Mid(formula, i, 1) = "," Or _
           Mid(formula, i, 1) = "(" Then
            sheetRef = Mid(formula, i + 1, pos - i - 1)
            Exit For
        End If
    Next i

This catches both hidden sheets (xlSheetHidden) and VeryHidden sheets (xlSheetVeryHidden). The distinction matters — hidden sheets can be unhidden from the Excel UI, while VeryHidden sheets require VBA. If a chart references a VeryHidden sheet, the data is deliberately concealed, and you should investigate why.

#AutoFilter on the report

wsOut.Rows(1).AutoFilter

The six-column report has an AutoFilter on the header row. Filter to show only rows where Flags contains “#REF!” to triage broken charts first. Or filter by sheet name to focus on one tab. The filter persists — close and reopen the workbook and the report is still filterable.

#Why the flag column is text, not conditional formatting

The “Flags” column uses plain text ("#REF! Source, Hidden Sheet Ref") instead of conditional formatting color scales. The reason: text is searchable, sortable, and filterable across Excel versions. Conditional formatting is visual but doesn’t survive copy-paste into email or an audit memo. If you want color, add it after the report is generated using the technique in the Adapt It section.

#Adapt It

Check all series, not just the first. Replace the single-series check with a loop through every series. Use this when your firm’s charts pull from multiple distinct sources:

Dim j As Long
For j = 1 To ch.SeriesCollection.Count
    srcFormula = ch.SeriesCollection(j).Formula
    ' Check for #REF!
    If InStr(1, srcFormula, "#REF!") > 0 Then
        refCount = refCount + 1
        ' Write a separate row for this series
        wsOut.Cells(outRow, 5).Value = "'" & srcFormula
        wsOut.Cells(outRow, 6).Value = "#REF! Source"
        outRow = outRow + 1
    End If
Next j

Add hyperlinks to chart locations. Make the “Sheet Location” column clickable to jump directly to the chart sheet:

wsOut.Hyperlinks.Add _
    Anchor:=wsOut.Cells(outRow, 2), _
    Address:="", _
    SubAddress:="'" & ws.Name & "'!A1", _
    TextToDisplay:=ws.Name

Color-code the flags column. Add conditional formatting to the report to make #REF! rows red and hidden-sheet rows yellow:

' After writing all data, apply formatting
Dim lastRow As Long
lastRow = outRow - 1
If lastRow >= 2 Then
    With wsOut.Range("F2:F" & lastRow)
        .FormatConditions.Add Type:=xlTextString, _
            TextOperator:=xlContains, String:="#REF!"
        .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(254, 202, 202)

        .FormatConditions.Add Type:=xlTextString, _
            TextOperator:=xlContains, String:="Hidden Sheet"
        .FormatConditions(.FormatConditions.Count).Interior.Color = RGB(254, 240, 138)
    End With
End If

Run on the active workbook. Store in Personal Macro Workbook and change ThisWorkbook to ActiveWorkbook:

' Replace all four occurrences of ThisWorkbook with ActiveWorkbook
For Each ws In ActiveWorkbook.Worksheets
' ... and in HasHiddenSource:
Set testWs = ActiveWorkbook.Worksheets(sheetRef)

Add a count of charts per sheet to the summary. Prepend a per-sheet breakdown to the summary message:

Dim sheetChartCount As Object, key As Variant
Set sheetChartCount = CreateObject("Scripting.Dictionary")
' During the scan, increment: sheetChartCount(ws.Name) = sheetChartCount(ws.Name) + 1
' Then in the summary:
msg = msg & vbCrLf & "Per-sheet breakdown:" & vbCrLf
For Each key In sheetChartCount.Keys
    msg = msg & "  " & key & ": " & sheetChartCount(key) & " chart(s)" & vbCrLf
Next key

See also: External Link Reporter — the same “catalog what’s there before you touch anything” pattern for workbook links. Named Range Documenter — inventory of named ranges with scope and value. Run all three when you first inherit a workbook to understand its full dependency graph.

Download the chart data source auditor sample workbook (8 data sheets plus instructions — create test charts to audit with the macro)

xlsx 12 KB