Knowledge base KB0204

Some Excel files are slow with data links

Problem

If an Excel file contains Excel data links (see 21. Excel 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:

print Activeworkbook.Styles.Count

The steps to check this are in detail:

  1. Press Alt+F11 to open the VBA editor.
  2. Go to the Immediate Window. If it is not visible, you can display it by clicking on ViewImmediate Window.
  3. Type the following command and press Enter
    print Activeworkbook.Styles.Count

Solution: Clean up affected workbooks

  • Copy your worksheets into a new workbook

    In Office 2013 and later copying worksheets between workbooks prevents the accumulation of unused cell styles by default:

    • Select all sheets in your workbook by holding SHIFT and click on the first and last worksheet
    • Right-click on the selection: Move or copy…To book: (new book) and tick Create a copyOK
    • Save the new workbook under a new name

    In Office 2007 and 2010 copying a worksheet into a new workbook copies all unused cell styles. To prevent this, please activate the following Hotfix first:

    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.

  • Clean up your workbooks with a cleanup tool

    Use the cleanup tool recommended by Microsoft in KB213904. Using the XLStyles Tool, click Get Excel File, tick Force all cell styles to 'Normal', then click Process File. When Excel opens with the cleaned up file, save it under a new name.

  • Remove custom cell styles from your workbook using a VBA macro

    Open the VBA editor with Alt+F11. Select your workbook and click on InsertModule. 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 113012810172527.

Explanation

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.

Share