Delete Styles

In Excel, when sharing workbooks, it is possible for a workbook to unintentionally build up hundreds of styles as users copy data in. When cells from another workbook are copied in, their styles are copied in at the same time.

This can cause a performance slowdown in the workbook and larger file sizes.

Styles can be removed manually, one at a time or by running a VBA macro.

Delete Styles

Manual

On the Home menu tab, click on the Cell Style dropdown.
Right click on the style to remove and select Delete.

Delete Styles

VBA

Put this VBA code into a module.

Sub DeleteNonBuiltInStyles()

Dim sty_AllStyles As Style
Dim intRet As Integer

    Debug.Print "Total no. of styles: " & ActiveWorkbook.Styles.Count
'Print the number of styles to the immediate window

    For Each sty_AllStyles In ActiveWorkbook.Styles
'Loop through all the styles

        Debug.Print sty_AllStyles.Name & " - " & sty_AllStyles.BuiltIn
'Print the style names and a boolean value of whether it is built in or not to the immediate window
           
        If Not sty_AllStyles.BuiltIn Then
            sty_AllStyles.Delete
        End If
'If the style is not built in, delete it
        
    Next sty_AllStyles

End Sub

Select the Workbook with excess styles.
From the Developer menu, click on Macros and select DeleteNonBuiltInStyles.

Styles that are not built in will be deleted.

All styles processed will be written to the Immediate window.

Once the macro is finished, you should be left with the standard built-in styles.

If not, create a new workbook and then use it to merge styles to your existing workbook.

With the existing workbook active, on the Home menu tab, click on the Cell Style dropdown.

Then click on Merge Styles and select the new workbook.

Delete Styles