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

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 copy > OK
  • 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:

  • Microsoft Office 2007: KB2553085
  • Microsoft Office 2010: KB2598127, which was published in the Office 2010 update for April 2012.

  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 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 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.