Workbook Bloat Analyzer: A One-Page Health Check for Any Inherited File

TL;DR: You open an inherited workbook and it takes 45 seconds. Is it 2 MB or 20 MB? Three sheets or forty-seven? Five named ranges or five hundred? This macro answers every question in one click, building a “Workbook-Health” report with thirteen metrics that tell you exactly what you’re dealing with before you touch a single number.

The Problem

A partner forwards you the Whitman Manufacturing file from the departed senior. You double-click. Nothing happens for 20 seconds. When it finally opens, you notice 47 tabs, three “This workbook contains links” popups, and a scroll bar that thinks the TB has 65,000 rows. You have no idea what’s bloating this file or where to start cleaning. You spend 10 minutes manually counting sheets, checking the Name Manager for dead ranges, and clicking through the Queries pane — before you’ve even opened a single workpaper tab.

This macro gives you the full diagnostic in 3 seconds. One sheet. Thirteen metrics. Every answer visible at a glance.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
Any workbook — the macro reads metadata, not cell data

What the macro does NOT do:

It does not modify any cell on any existing sheet. It creates one new “Workbook-Health” sheet and writes only there.
It does not delete, clean, or fix anything. It’s a pure diagnostic — it tells you what’s there so you can decide what to fix.
It does not crack passwords or bypass protection. If VBA project access is restricted, it reports “Unknown” for the VBA module count rather than crashing.

Limitations:

File size uses FileLen() which reads the file on disk. If the workbook hasn’t been saved, file size shows as 0 KB — save first.
VBA module count requires “Trust access to the VBA project object model” enabled in Excel → Options → Trust Center → Trust Center Settings → Macro Settings. If disabled, the macro reports “Unknown (VBA trust not enabled)” instead of the actual count.
Custom cell styles are detected by comparing against a list of ~28 built-in style names. If Microsoft adds new built-in styles in a future Excel update, they may be incorrectly flagged as “custom” until the list is updated.
Pivot cache count includes all caches, including orphaned caches from deleted pivot tables.
Named range count includes both workbook-scoped and sheet-scoped names. Broken names (showing #REF!) are flagged separately.

#The Macro

Option Explicit

Sub WorkbookBloatAnalyzer()
    ' ── Workbook Bloat Analyzer ────────────────────────
    ' Creates a "Workbook-Health" report sheet showing
    ' key structural metrics: file size, sheet count,
    ' used-range dimensions, named ranges (broken/live),
    ' pivot caches, custom cell styles, external links,
    ' hyperlinks, conditional formatting rules, cell
    ' comments, shapes, and VBA module count.
    '
    ' Zero input. Non-destructive. Run on any workbook.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Workbook-Health"

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

    ' ── Variables ──────────────────────────────────────
    Dim rpt As Worksheet, ws As Worksheet
    Dim row As Long, r As Long
    Dim totalRows As Long, maxRows As Long
    Dim maxRowsSheet As String, vbaCount As String
    Dim fileSizeKB As Double, xlCount As String
    Dim nmCount As Long, nmBroken As Long
    Dim pcCount As Long, hypTotal As Long
    Dim cfTotal As Long, cmtTotal As Long
    Dim shpTotal As Long, styleCount As Long
    Dim i As Long, j As Long

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

    ' ── Create/refresh output 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( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    rpt.Name = OUT_SHEET

    ' ── Headers ────────────────────────────────────────
    rpt.Range("A1:B1").Value = Array("Metric", "Value")
    rpt.Range("A1:B1").Font.Bold = True
    rpt.Range("A1:B1").Interior.Color = RGB(50, 50, 50)
    rpt.Range("A1:B1").Font.Color = vbWhite
    row = 2

    ' ── 1. File name & size ────────────────────────────
    rpt.Cells(row, 1) = "File Name"
    rpt.Cells(row, 2) = ThisWorkbook.Name
    row = row + 1

    On Error Resume Next
    fileSizeKB = FileLen(ThisWorkbook.FullName) / 1024
    On Error GoTo CleanUp
    rpt.Cells(row, 1) = "File Size (KB)"
    rpt.Cells(row, 2) = Format(fileSizeKB, "#,##0")
    row = row + 1

    ' ── 2. Sheet count ─────────────────────────────────
    rpt.Cells(row, 1) = "Sheet Count"
    rpt.Cells(row, 2) = ThisWorkbook.Worksheets.Count
    row = row + 1

    ' ── 3. Used-range rows (total & largest) ───────────
    totalRows = 0: maxRows = 0
    For Each ws In ThisWorkbook.Worksheets
        Dim uRows As Long
        uRows = ws.UsedRange.Rows.Count
        totalRows = totalRows + uRows
        If uRows > maxRows Then
            maxRows = uRows
            maxRowsSheet = ws.Name
        End If
    Next ws
    rpt.Cells(row, 1) = "Used-Range Rows (Total)"
    rpt.Cells(row, 2) = Format(totalRows, "#,##0")
    row = row + 1

    rpt.Cells(row, 1) = "Largest Sheet"
    rpt.Cells(row, 2) = "'" & maxRowsSheet & "' (" & _
        Format(maxRows, "#,##0") & " rows)"
    row = row + 1

    ' ── 4. Named ranges (live & broken) ────────────────
    nmCount = ThisWorkbook.Names.Count
    nmBroken = 0
    If nmCount > 0 Then
        Dim nm As Name
        On Error Resume Next
        For Each nm In ThisWorkbook.Names
            If InStr(1, nm.RefersTo, "#REF!", _
                vbTextCompare) > 0 Then
                nmBroken = nmBroken + 1
            End If
        Next nm
        On Error GoTo CleanUp
    End If
    rpt.Cells(row, 1) = "Named Ranges"
    rpt.Cells(row, 2) = nmCount & " total"
    If nmBroken > 0 Then
        rpt.Cells(row, 2) = rpt.Cells(row, 2) & _
            " (" & nmBroken & " broken)"
        rpt.Cells(row, 2).Font.Color = vbRed
    End If
    row = row + 1

    ' ── 5. Pivot caches ────────────────────────────────
    On Error Resume Next
    pcCount = ThisWorkbook.PivotCaches.Count
    On Error GoTo CleanUp
    rpt.Cells(row, 1) = "Pivot Caches"
    rpt.Cells(row, 2) = pcCount
    row = row + 1

    ' ── 6. External links ──────────────────────────────
    On Error Resume Next
    Dim links As Variant
    links = ThisWorkbook.LinkSources(xlExcelLinks)
    If IsEmpty(links) Then
        xlCount = "0"
    Else
        xlCount = CStr(UBound(links))
    End If
    On Error GoTo CleanUp
    rpt.Cells(row, 1) = "External Links"
    rpt.Cells(row, 2) = xlCount
    row = row + 1

    ' ── 7. Custom cell styles ──────────────────────────
    Dim builtIn As Variant, sty As Style
    builtIn = Array("Normal", "Comma", "Currency", _
        "Percent", "Note", "Warning Text", _
        "Explanatory Text", "Title", "Heading 1", _
        "Heading 2", "Heading 3", "Heading 4", _
        "Input", "Output", "Calculation", _
        "Check Cell", "Linked Cell", "Total", _
        "Good", "Bad", "Neutral", "Accent1", _
        "Accent2", "Accent3", "Accent4", _
        "Accent5", "Accent6", "Followed Hyperlink", _
        "Hyperlink")
    styleCount = 0
    For Each sty In ThisWorkbook.Styles
        Dim isBuiltIn As Boolean
        isBuiltIn = False
        For i = LBound(builtIn) To UBound(builtIn)
            If sty.Name = builtIn(i) Then
                isBuiltIn = True
                Exit For
            End If
        Next i
        If Not isBuiltIn Then _
            styleCount = styleCount + 1
    Next sty
    rpt.Cells(row, 1) = "Custom Cell Styles"
    rpt.Cells(row, 2) = styleCount
    row = row + 1

    ' ── 8. Hyperlinks ──────────────────────────────────
    hypTotal = 0
    For Each ws In ThisWorkbook.Worksheets
        hypTotal = hypTotal + ws.Hyperlinks.Count
    Next ws
    rpt.Cells(row, 1) = "Hyperlinks"
    rpt.Cells(row, 2) = hypTotal
    row = row + 1

    ' ── 9. Conditional formatting rules ────────────────
    cfTotal = 0
    For Each ws In ThisWorkbook.Worksheets
        cfTotal = cfTotal + _
            ws.Cells.FormatConditions.Count
    Next ws
    rpt.Cells(row, 1) = "Conditional Format Rules"
    rpt.Cells(row, 2) = cfTotal
    row = row + 1

    ' ── 10. Cell comments ──────────────────────────────
    cmtTotal = 0
    For Each ws In ThisWorkbook.Worksheets
        cmtTotal = cmtTotal + ws.Comments.Count
    Next ws
    rpt.Cells(row, 1) = "Cell Comments"
    rpt.Cells(row, 2) = cmtTotal
    row = row + 1

    ' ── 11. Shapes ─────────────────────────────────────
    shpTotal = 0
    For Each ws In ThisWorkbook.Worksheets
        shpTotal = shpTotal + ws.Shapes.Count
    Next ws
    rpt.Cells(row, 1) = "Shapes (Text Boxes, Arrows, etc.)"
    rpt.Cells(row, 2) = shpTotal
    row = row + 1

    ' ── 12. VBA modules ────────────────────────────────
    On Error Resume Next
    Dim vbCount As Long
    vbCount = ThisWorkbook.VBProject.VBComponents.Count
    If Err.Number <> 0 Then
        vbaCount = "Unknown (VBA trust not enabled)"
    Else
        vbaCount = CStr(vbCount) & " module(s)"
    End If
    On Error GoTo CleanUp
    rpt.Cells(row, 1) = "VBA Modules"
    rpt.Cells(row, 2) = vbaCount
    row = row + 1

    ' ── Format output ──────────────────────────────────
    rpt.Columns("A").ColumnWidth = 32
    rpt.Columns("B").ColumnWidth = 42
    rpt.Range("A2:B" & (row - 1)).Font.Size = 10

    For r = 2 To row - 1
        If r Mod 2 = 0 Then
            rpt.Range("A" & r & ":B" & r). _
                Interior.Color = RGB(248, 248, 250)
        End If
    Next r

    rpt.Range("A1").Select
    ActiveWindow.FreezePanes = True

    MsgBox "Health report created on '" & OUT_SHEET & _
        "' sheet." & vbCrLf & vbCrLf & _
        "File: " & Format(fileSizeKB, "#,##0") & " KB" & _
        "  |  Sheets: " & ThisWorkbook.Worksheets.Count & _
        vbCrLf & "Named ranges: " & nmCount & _
        IIf(nmBroken > 0, " (" & nmBroken & " broken!)", _
        "") & vbCrLf & _
        "Custom styles: " & styleCount & _
        "  |  Ext. links: " & xlCount, _
        vbInformation, "Workbook Health"

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

#Every metric answers a diagnostic question

The report isn’t just a list of numbers — each metric tells you something about the workbook’s history and health:

File Size: A 30-tab tax workpackage should be 200–800 KB. If it’s 15 MB, something is bloating it — probably hundreds of custom styles or orphaned pivot caches.
Sheet Count: Tells you the scale of the cleanup job. Three sheets vs. 47 changes your approach entirely.
Largest Sheet: A sheet with 65,536 used-range rows but only 200 actual data rows is a classic sign of “last cell drift” — someone had data at row 65,536, deleted it, but Excel still thinks it’s there. That’s a job for the Last Cell Resetter.
Named Ranges (broken): Every #REF! is a named range that pointed to a sheet or range that was deleted. They don’t break formulas but they clutter the Name Manager and confuse anyone auditing the workbook.
Custom Cell Styles: Excel ships with ~28 built-in styles. Anything beyond that is a user-created style. Inherited workbooks can accumulate hundreds. Each one adds a few bytes, multiplied by the number of cells that use it.

#Why the VBA module count is sometimes “Unknown”

On Error Resume Next
vbCount = ThisWorkbook.VBProject.VBComponents.Count
If Err.Number <> 0 Then
    vbaCount = "Unknown (VBA trust not enabled)"

Accessing the VBA project programmatically requires a specific Trust Center setting: “Trust access to the VBA project object model.” This is disabled by default for security reasons — it prevents malware from reading or modifying your VBA code. The macro attempts the access, and if it fails, it reports “Unknown” instead of crashing. The other twelve metrics still work fine without this setting.

To enable it: File → Options → Trust Center → Trust Center Settings → Macro Settings → check “Trust access to the VBA project object model.” Enable it before running the macro, then disable it afterward if you’re security-conscious.

#Custom cell styles: how the macro tells them apart

For Each sty In ThisWorkbook.Styles
    isBuiltIn = False
    For i = LBound(builtIn) To UBound(builtIn)
        If sty.Name = builtIn(i) Then
            isBuiltIn = True
            Exit For
        End If
    Next i
    If Not isBuiltIn Then styleCount = styleCount + 1
Next sty

Excel’s Styles collection includes every style in the workbook — both Microsoft’s built-in styles (Normal, Comma, Currency, etc.) and any styles created by a user. The macro maintains a hardcoded list of known built-in style names. Any style not on that list is counted as custom.

This means the count is approximate, not authoritative. If Microsoft adds new built-in styles in a future Excel version, they’ll be misidentified as custom. But for the typical inherited tax workpaper, the difference between 28 built-in styles and 847 total styles is unambiguous — 819 of them are custom.

#Named ranges with #REF! are flagged in red

If nmBroken > 0 Then
    rpt.Cells(row, 2) = rpt.Cells(row, 2) & _
        " (" & nmBroken & " broken)"
    rpt.Cells(row, 2).Font.Color = vbRed
End If

A broken named range means the sheet or range it referenced was deleted. The name is still registered in the workbook but resolves to #REF!. These are harmless — they don’t cause formula errors unless a formula references the named range — but they confuse anyone auditing the workbook and they take up space. The macro flags them in red so you see them immediately without scanning every entry.

#The message box gives you the key numbers at a glance

MsgBox "Health report created on '" & OUT_SHEET & _
    "' sheet." & vbCrLf & vbCrLf & _
    "File: " & Format(fileSizeKB, "#,##0") & " KB" & _
    "  |  Sheets: " & ThisWorkbook.Worksheets.Count & _
    vbCrLf & "Named ranges: " & nmCount & _
    IIf(nmBroken > 0, " (" & nmBroken & " broken!)", "") & _
    vbCrLf & "Custom styles: " & styleCount & ...

The MsgBox highlights the four metrics that are most likely to be surprising: file size, sheet count, named range count, and custom style count. You get the headlines immediately. The full detail — all thirteen metrics — is on the report sheet for when you need it.

#What to do with the report

Once you have the health report, you can triage:

File size > 5 MB with sheet count < 15: Custom cell styles or orphaned pivot caches are the likely culprit. Run the Cell Style Cleaner or Pivot Cache Auditor when those macros are available. For now, prune manually.
Named ranges > 50 with broken count > 10: Run the Named Range Documenter for a full inventory, then Delete Broken Named Ranges.
Largest sheet has suspiciously high row count: Run the Last Cell Resetter.
External links > 0: Run the External Link Reporter to audit what’s linked, then Strip External Links if you want them gone.
Conditional formatting rules > 20: Run the Conditional Formatting Stripper to remove conflicting layers.

The health report doesn’t fix anything — it tells you what to fix and which macro to use next.

#Adapt It

Add a per-sheet breakdown. The current report aggregates everything. To see used-range rows per sheet instead of just the total, add a second report sheet:

Dim detail As Worksheet
Set detail = ThisWorkbook.Worksheets.Add( _
    After:=rpt)
detail.Name = "Sheet-Detail"
detail.Cells(1, 1) = "Sheet Name"
detail.Cells(1, 2) = "Used-Range Rows"
detail.Cells(1, 3) = "Formulas"
detail.Cells(1, 4) = "Comments"
Dim dRow As Long: dRow = 2
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> OUT_SHEET And ws.Name <> "Sheet-Detail" Then
        detail.Cells(dRow, 1) = ws.Name
        detail.Cells(dRow, 2) = ws.UsedRange.Rows.Count
        dRow = dRow + 1
    End If
Next ws

Add a recommended-actions column. Based on the metrics, add a third column that suggests cleanup macros:

rpt.Cells(1, 3) = "Recommended Action"
If styleCount > 50 Then
    rpt.Cells(styleRow, 3) = "Run Cell Style cleaner"
End If
If nmBroken > 0 Then
    rpt.Cells(nmRow, 3) = "Run Delete Broken Named Ranges"
End If

Run automatically on open. Add to ThisWorkbook to build the health report every time the file opens:

Private Sub Workbook_Open()
    Call WorkbookBloatAnalyzer
End Sub

Export the report as a standalone file. Save the report sheet to a new workbook for sharing with a reviewer or partner:

rpt.Copy  ' Copies the sheet to a new workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & _
    "\" & Replace(ThisWorkbook.Name, ".xlsm", "") & _
    "_Health.xlsx", xlOpenXMLWorkbook
ActiveWorkbook.Close

See also: Workbook Inventory — a per-sheet map of visibility, dimensions, formula presence, and protection. Pair the two: run the Bloat Analyzer for the macro-level picture, then the Inventory for the sheet-level detail.

Download the workbook bloat analyzer sample (10 sheets: varied sizes, named ranges — some broken, external links, hyperlinks, conditional formatting rules, comments, shapes, and custom cell styles)

xlsx 16 KB