Cell Style Cleaner: Delete Every Barnacle Custom Style That's Bloating Your Inherited Workbook

TL;DR: You open an inherited workbook and it takes 30 seconds to load. The file is 18MB but only has 12 sheets of data. The culprit: 847 custom cell styles accumulated from every preparer who ever touched the file. This macro catalogs every non-built-in style, shows you the list, and strips them all in one pass. The 47 built-in styles (Normal, Comma, Currency, etc.) are untouched.

The Problem

You inherit the Chen Manufacturing engagement file from a departed manager. The file takes 28 seconds to open. Saving takes 12 seconds. Scrolling lags. You check the sheets — 14 tabs, nothing excessive. The data is maybe 500KB of numbers. You File → Save As a copy and it’s still 15MB.

You dig into the Cell Styles gallery and your jaw drops. There are 847 custom styles. “Accent1 40%,” “Accent1 40% — Accent 2,” “Normal 2,” “Normal 3,” “20% — Accent4,” and on and on. Styles copied from Jim’s 2018 workpaper, pasted into Sarah’s 2020 review, merged into Mike’s 2022 consolidation, and inherited by you in 2026. Each style carries a full set of formatting metadata — font, fill, border, number format, alignment, protection. Many are duplicates of each other with slightly different names. None are actually used anywhere in the workbook.

Excel offers no way to delete them in bulk. You click each style in the gallery, right-click, Delete, confirm. One at a time. 847 times. Or you accept the bloat and the 30-second load time until the engagement is over.

#Prerequisites & Setup

What you’ll need:

Excel 2016+ (desktop)
A workbook that’s slow to open or save despite modest data — the classic symptom of custom style bloat

What the macro does:

Loops every style in ActiveWorkbook.Styles and checks the BuiltIn property — built-in styles (Normal, Comma, Currency, Percent, etc.) are skipped
Catalogs every user-created style by name and shows up to 20 of them in the confirmation message box
On confirm, deletes every custom style in a single pass
Reports how many were deleted and how many built-in styles were preserved

What the macro does NOT do:

It does not touch the 47 built-in Excel styles — Normal, Comma, Currency, Percent, Note, Warning Text, Heading 1–4, Title, Total, and all 20 Accent/60% combinations. These are protected by the BuiltIn flag
It does not change any cell formatting. If a cell was formatted with a custom style “Accent1 40% — Accent 2,” the cell’s formatting reverts to direct formatting after the style is deleted — it still looks the same
It does not remove conditional formatting, data validation, named ranges, or any other metadata — strictly cell styles

Limitations:

Works on ActiveWorkbook — the workbook you’re currently viewing. Make sure the right file is active before running
Excel’s built-in style list varies slightly by version and language. The macro uses the .BuiltIn property rather than a hardcoded list — this is reliable across all Excel versions
Custom styles that are currently in use (applied to cells) will be deleted, but the cell formatting persists as direct formatting. No data is lost
The Style.Delete method cannot be undone with Ctrl+Z. Save the workbook before running

#The Macro

Option Explicit

Sub CleanCellStyles()
    ' ── Cell Style Cleaner ─────────────────────────────
    ' Catalogs every user-created cell style, confirms
    ' with the user, and deletes them all in one pass.
    ' Built-in styles (Normal, Comma, Currency, etc.)
    ' are identified by the .BuiltIn flag and preserved.
    '
    ' Store in PERSONAL.XLSB to run on any workbook.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim st As Style
    Dim customCount As Long
    Dim builtinCount As Long
    Dim styleList As String
    Dim deleted As Long
    Dim msg As String

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

    ' ── Pass 1: Count and catalog custom styles ────────
    customCount = 0
    builtinCount = 0
    styleList = ""

    For Each st In ActiveWorkbook.Styles
        If st.BuiltIn Then
            builtinCount = builtinCount + 1
        Else
            customCount = customCount + 1
            ' List the first 20 — beyond that it's noise
            If customCount <= 20 Then
                styleList = styleList & "  • " & st.Name & vbCrLf
            End If
        End If
    Next st

    ' ── Pass 2: Report findings and confirm ────────────
    If customCount = 0 Then
        MsgBox "No custom cell styles found. " & builtinCount & _
               " built-in style(s) detected.", vbInformation, "All Clear"
        GoTo CleanUp
    End If

    msg = "Found " & customCount & " custom cell style(s) " & _
          "(plus " & builtinCount & " built-in)." & vbCrLf & vbCrLf

    If customCount > 20 Then
        msg = msg & "First 20 custom styles:" & vbCrLf & styleList & vbCrLf & _
              "... and " & (customCount - 20) & " more not shown." & vbCrLf & vbCrLf
    Else
        msg = msg & "Custom styles:" & vbCrLf & styleList & vbCrLf
    End If

    msg = msg & "Delete ALL " & customCount & " custom style(s)? " & _
          "This action cannot be undone." & vbCrLf & vbCrLf & _
          "Built-in styles (Normal, Comma, Currency, etc.) " & _
          "will NOT be affected."

    If MsgBox(msg, vbExclamation + vbYesNo, _
              "Confirm — Delete Custom Styles") = vbNo Then
        MsgBox "No styles were removed. " & customCount & _
               " custom style(s) remain in the workbook.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Pass 3: Delete every non-built-in style ────────
    deleted = 0

    For Each st In ActiveWorkbook.Styles
        If Not st.BuiltIn Then
            On Error Resume Next
            st.Delete
            If Err.Number = 0 Then
                deleted = deleted + 1
            Else
                Err.Clear
            End If
            On Error GoTo CleanUp
        End If
    Next st

    ' ── Pass 4: Report results ─────────────────────────
    Dim skipped As Long
    skipped = customCount - deleted

    msg = "Deleted " & deleted & " custom cell style(s)." & vbCrLf & _
          builtinCount & " built-in style(s) preserved."

    If skipped > 0 Then
        msg = msg & vbCrLf & vbCrLf & skipped & _
              " style(s) could not be deleted " & _
              "(workbook structure may be protected)."
    End If

    msg = msg & vbCrLf & vbCrLf & _
          "Tip: Save the workbook now — file size should decrease."

    MsgBox msg, vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True

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

#How It Works

#The `.BuiltIn` property — no hardcoded list required

The key design decision: the macro does not maintain a list of 47 built-in style names. Instead, it checks st.BuiltIn for every style in the workbook. Excel’s Style object has this property natively — it returns True for Normal, Comma, Currency, Percent, Note, Warning Text, Heading 1–4, Title, Total, and all 20 theme-based Accent/60% combinations. If BuiltIn is False, the style was created by a human (or copied from another workbook).

For Each st In ActiveWorkbook.Styles
    If st.BuiltIn Then
        builtinCount = builtinCount + 1
    Else
        customCount = customCount + 1
    End If
Next st

This works across all Excel versions and languages. French Excel, German Excel, Excel 2010, Excel 365 — the BuiltIn flag is the same. No localization bugs, no version-specific style name lists to maintain.

#Three passes, not one — count, confirm, delete

Like the Data Validation Stripper and Hyperlink Stripper, this macro separates discovery from destruction. Pass 1 catalogs every style and counts built-in vs. custom. Pass 2 shows the confirmation message box — the user sees what they’re about to lose. Pass 3 (after the user clicks Yes) does the actual deletion.

If MsgBox(msg, vbExclamation + vbYesNo, _
          "Confirm — Delete Custom Styles") = vbNo Then
    ' User said no — exit without touching anything
    GoTo CleanUp
End If

This three-pass pattern is deliberate. You can’t undo style deletion with Ctrl+Z. The message box is your safety net — read it before you click.

#Only showing the first 20 custom styles

Workbooks with style bloat often have hundreds of custom styles — 400, 800, even 1,200. Listing every one in the message box would overflow the screen:

If customCount <= 20 Then
    styleList = styleList & "  • " & st.Name & vbCrLf
End If

Twenty is enough to confirm the styles are what you expect: “Accent1 40%,” “Normal 2,” “20% — Accent4,” etc. If you see a style name you recognize and want to keep, you know to click No and investigate. If you see barnacle names you’ve never heard of, you know it’s safe to delete.

#`On Error Resume Next` for individual deletions, `GoTo CleanUp` for the loop

Some styles cannot be deleted — particularly if the workbook structure is protected. Deleting one custom style should not crash the entire cleanup. The macro uses targeted error suppression for each deletion:

On Error Resume Next
st.Delete
If Err.Number = 0 Then
    deleted = deleted + 1
Else
    Err.Clear
End If
On Error GoTo CleanUp

If a style fails to delete, the counter simply doesn’t increment it, and the macro moves on. The final message box reports how many were skipped so the user knows the cleanup wasn’t complete:

If skipped > 0 Then
    msg = msg & vbCrLf & vbCrLf & skipped & _
          " style(s) could not be deleted..."
End If

#Why `ActiveWorkbook` instead of `ThisWorkbook`

This macro is designed to live in your Personal Macro Workbook (PERSONAL.XLSB), not in the workbook you’re cleaning. Using ActiveWorkbook means:

You store the macro once and run it on any open file
The macro workbook’s own styles (likely zero custom ones) aren’t the target
You don’t need to paste VBA into every bloated workbook you receive

If you prefer to embed it in a specific workbook, replace ActiveWorkbook with ThisWorkbook. See Adapt It.

#No `Calculation` toggle needed

Deleting styles doesn’t change cell values, trigger formula recalculation, or shift any ranges. The only visual effect is a brief flash as Excel redraws formatting — suppressed by ScreenUpdating = False.

#The message box uses `vbExclamation` — intentional

If MsgBox(msg, vbExclamation + vbYesNo, ...) = vbNo Then

Style deletion is destructive. The yellow warning triangle signals “read before you click.” Regular readers of this blog learn the icon language instinctively: blue i = information only, yellow ! = think first.

#Adapt It

Use ThisWorkbook instead of ActiveWorkbook. If you want the macro to only clean the workbook it’s stored in (not your Personal Macro Workbook), replace every occurrence of ActiveWorkbook with ThisWorkbook:

For Each st In ThisWorkbook.Styles

This is safer in shared environments where you don’t want accidental cleanup of the wrong file.

Skip styles matching a pattern. If your firm has intentional custom styles with a specific prefix (e.g., “Firm_”), add a filter before deletion:

If Not st.BuiltIn Then
    If Left(st.Name, 5) = "Firm_" Then GoTo SkipStyle
    On Error Resume Next
    st.Delete
    If Err.Number = 0 Then deleted = deleted + 1
    On Error GoTo CleanUp
SkipStyle:
End If

This preserves intentional styles while cleaning up barnacles.

Log deleted style names to a sheet. Create a “Style-Cleanup-Log” sheet with a dated list of every style deleted. Useful for audit or if someone asks “what happened to my ‘Special Green’ style?”:

Dim logWS As Worksheet
Dim logRow As Long
Set logWS = ThisWorkbook.Sheets.Add
logWS.Name = "Style-Cleanup-Log"
logWS.Cells(1, 1) = "Deleted Style Name"
logWS.Cells(1, 2) = "Date/Time"
logRow = 2

' Inside the deletion loop, after st.Delete succeeds:
logWS.Cells(logRow, 1) = st.Name
logWS.Cells(logRow, 2) = Now
logRow = logRow + 1

Audit only — don’t delete. Comment out the deletion loop and the macro becomes a read-only inspection tool. Run it to see how many custom styles a workbook has without touching any:

' For Each st In ActiveWorkbook.Styles
'     If Not st.BuiltIn Then
'         ' st.Delete   <-- commented out
'     End If
' Next st

Useful before accepting a file from a client or another firm — you can estimate how much file bloat you’re dealing with.

Show ALL custom style names in the report sheet. The message box caps at 20. For a complete catalog, write every name to a “Style-Inventory” sheet:

Dim rptWS As Worksheet
Dim rptRow As Long
Set rptWS = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
rptWS.Name = "Style-Inventory"
rptWS.Cells(1, 1) = "Style Name"
rptWS.Cells(1, 2) = "Built-In?"
rptRow = 2

For Each st In ActiveWorkbook.Styles
    rptWS.Cells(rptRow, 1) = st.Name
    rptWS.Cells(rptRow, 2) = IIf(st.BuiltIn, "Yes", "No")
    rptRow = rptRow + 1
Next st

rptWS.Columns("A:B").AutoFit

This gives you the full picture — every built-in and custom style side by side.

See also: Data Validation Stripper and Delete Broken Named Ranges — two more macros for cleaning inherited workbook metadata. Hyperlink Stripper uses the same confirm-before-delete pattern. Run all four when you inherit a file from a departed preparer.

Download the sample workbook (12 sheets with realistic tax data and 95+ custom cell styles simulating 7 years of preparer accumulation)

xlsx 14 KB

The Problem

#Prerequisites & Setup

#The Macro

#How It Works

#The .BuiltIn property — no hardcoded list required

#Three passes, not one — count, confirm, delete

#Only showing the first 20 custom styles

#On Error Resume Next for individual deletions, GoTo CleanUp for the loop

#Why ActiveWorkbook instead of ThisWorkbook

#No Calculation toggle needed

#The message box uses vbExclamation — intentional