When an Excel file contains think-cell data links and too many cell styles, it might start to scroll very slowly and Excel seems to be unresponsive.
This problem can happen if the Excel file contains more than ~3000 cell styles. You can check the number of cell styles with the following VBA command in the Immediate Window:
The steps to check this are in detail:
In Office 2013 and later copying worksheets between workbooks prevents the accumulation of unused cell styles by default:
If your Office version is fully updated, this Hotfix is probably already installed. However, you also must activate this fix either by using the Fix it solution provided by Microsoft or in the registry as explained in the following Microsoft articles:
If activating the Hotfix does not work, apply the update mentioned in section Resolution in the Microsoft articles above.
Open the VBA editor with Alt+F11. Select your workbook and click on Insert → Module. Then paste one of the following macros and run it by pressing F5:
DeleteCustomStyles() removes all custom cell styles from your workbook.
Sub DeleteCustomStyles() Dim st As Style Dim i, i_cust As Long i_cust = 0 For i = (ActiveWorkbook.Styles.Count) To 1 Step -1 With ActiveWorkbook.Styles(i) If .BuiltIn = False Then On Error Resume Next .Locked = False On Error GoTo 0 If Not .Locked Then .Delete i_cust = i_cust + 1 End If End If End With If (i Mod 100) = 0 Then Debug.Print i Next MsgBox "Styles deleted: " & i_cust & " styles" & Chr(13) & "Styles left: " & ActiveWorkbook.Styles.Count End Sub
Remove_Styles() removes only unused custom cell styles from your workbook. It is similar to the macro above, however it may take very long to run. The macro was provided by the Microsoft Office Support.
Option Explicit Dim st_array() As String Dim i_x As Long Sub Remove_Styles() Dim stname As String Dim ustname As String Dim uc As Range Dim retval As Boolean Dim ust As Style Dim sh As Worksheet i_x = 0 For Each sh In ActiveWorkbook.Worksheets For Each uc In sh.UsedRange stname = uc.Style.Name retval = Check_Array(stname) If retval = False Then ReDim Preserve st_array(i_x) st_array(i_x) = stname i_x = i_x + 1 End If Next uc Next sh For Each ust In ActiveWorkbook.Styles If ust.BuiltIn = False Then ustname = ust.Name retval = Delete_Styles(ustname) On Error Resume Next If retval = True Then ust.Delete On Error GoTo 0 End If Next ust End Sub Function Delete_Styles(stylename As String) As Boolean Delete_Styles = True Dim i_y As Long For i_y = 0 To i_x - 1 If st_array(i_y) = stylename Then Delete_Styles = False Next i_y End Function Function Check_Array(stylename As String) As Boolean Check_Array = False Dim i_y As Long For i_y = 0 To i_x - 1 If st_array(i_y) = stylename Then Check_Array = True Next i_y End Function
The best solution would be to prevent the pasting of unused cell styles, but requires Microsoft to fix a bug in Excel 2007 and later. Up to now Microsoft has not considered the business impact high enough to provide this fix. If your company has a Microsoft Office Support contract and you want to contact Microsoft to ask for a fix, you may refer to Microsoft case number REG:113012810172527.
Some .xlsx workbooks contain a large number (several ten thousands) of cell styles due to bugs and problematic code design in Excel. A contributing factor are also reporting tools that sometimes generate unnecessarily many cell styles.