A Decided Lack of Style
This article looks at how to remove styles from an Excel workbook. By Liam Bastick, director (and Excel MVP) with SumProduct Pty Ltd.
Query
I have a workbook where the number of styles appears to be getting out of control. What created so many and how do I get rid of them?
Advice
A long time ago, the difference between formats and styles was one of the first things I ever wrote about and the original article may be found here. To recap:
Formats and styles
These terms, often used interchangeably, but they are not the same thing. To see this, select any cell in Excel and apply the shortcut keystroke CTRL + 1. This shortcut brings up the ‘Format Cells’ dialog box:
Excel has six format properties: Number, Alignment, Font, Border, Patterns and Protection. A style is simply a pre-defined set of these various formats. With a little forethought, these styles can be set up and applied to a worksheet cell or range very easily.
Creating your own styles is straightforward with Excel’s ‘Style’ dialog box, by going to the ‘Home’ tab, click the arrow in the bottom right corner and then select ‘New Cell Style…’ (ALT + N). Either way, the following dialog box appears:
Example
Let’s create an assumption format for entering data in dollars. First, select a cell or range of cells. Then, call up the above dialog box. The dropdown box (highlighted above) can be edited. We’ll change the name to ‘Dollar Assumptions’ and click the ‘Format’ button.
The ‘Format Cells’ dialog box reappears:
- Number: select the Currency category, with zero decimal places and apply the ‘$’ symbol
- Alignment: Horizontal – Right (Indent) with zero indent
- Patterns: select an ‘easy-on-the-eye’ colour such as pale green
- Protection: uncheck the Locked check box (allows the cell to be changed in a protected worksheet)
- Click ‘OK’ to return to the ‘Style’ dialog box.
Note that no formats have been ascribed for Font or Border in this example. I don’t want the style to control (that is, overwrite) these properties, so the ‘Style includes’ check boxes for these two format properties should be unchecked:
By clicking ‘OK’ or ‘Add’, the cell or range has now been formatted with the ‘Dollar Assumptions’ style. Now that this style has been added, you may simply select the range and then click on the style in the ‘Custom’ section of the ‘Styles’ gallery on the ‘Home’ tab:
So What Goes Wrong?
Too often, as our reader states, styles go “out of control”. You open up a workbook, inspect the styles and note that there are more styles than cells on the worksheet:
Above is one example. Sometimes you have multiple copies of the same styles as well. Often, when you have styles appear as above, if you right click on one of these styles, they may not delete as they should. Even if you are able to delete them, you may only delete one at a time – and that’s unacceptable too. It’s possible to have tens of thousands of styles and that causes other problems. The styles gallery only shows a limited number – and if you cannot see them, that makes them even harder to remove.
So how is this caused? The three most common causes are:
- Cells have been copied from one file to another where the two workbooks have different style definitions
- Entire worksheets have been copied from one workbook to another
- Especially if styles will not delete, the file was originally saved in Excel 2010 and has then been saved in another version of Excel and / or 32-bit Excel was used.
Given an ounce of prevention is worth a ton of cure, preventative measures include:
- Not copying entire worksheets from one workbook to another
- Paste special without formatting
- Use the same version of Excel throughout a workbook’s life
- Work in 64-bit Excel.
Of course, these are not necessarily realistic measures and often the damage has already been done. In that case, to remove multiple styles, there are three common alternatives.
1. Use a Macro
Everyone has their favourite macros. Whilst I try to avoid them, I am no exception. The following will clear unused custom styles:
Public Sub ClearUnusedCustomStyles()
Dim xStyle As Style
Dim rngCell As Range
Dim ws As Worksheet
Dim sStyle As String
Dim iStyleCount As Long
Dim aKey As Variant
Dim dict As New Scripting.Dictionary
' from Tools / References... / "Microsoft Scripting Runtime"
' dict := list of styles
For Each xStyle In ActiveWorkbook.Styles
If Not xStyle.BuiltIn Then
sStyle = xStyle.NameLocal
iStyleCount = iStyleCount + 1
dict.Add sStyle, 0 ' add key
End If
Next xStyle
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible Then
For Each rngCell In ws.UsedRange.Cells
If Not rngCell.Style.BuiltIn Then
sStyle = rngCell.Style.Name
dict.Item(sStyle) = dict.Item(sStyle) + 1 ' increment occurrences
End If
Next rngCell
End If
Next ws
On Error Resume Next
For Each aKey In dict.Keys
If dict.Item(aKey) = 0 Then ' unused
ActiveWorkbook.Styles(aKey).Delete
If Err.Number <> 0 Then
Err.Clear
End If
dict.Remove aKey
End If
Next aKey
End Sub
This is cleverer than the “generic” macro that just kills all custom styles. Selective euthanasia is recommended!
Sub StyleKill()
Dim styT As Style
On Error Resume Next
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then styT.Delete
Next styT
MsgBox "Custom styles have been removed"
End Sub
2. Use a Third Party Tool
It’s been around since 2011 but it’s still popular. The website https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/ is recommended by several Excel MVPs, where you can download a Silverlight tool or a .NET 4.0 tool that runs on your local computer against a closed file. If you use this software, do not use it on an OpenXML format, i.e. it is saved as .xls or .xlsb. It must be either a macro-enabled workbook (.xlsm) or a ‘standard’ Excel workbook (.xlsx). This may mean you will have to save the file in a different format first.
I must admit I have not tried this software myself but I understand others have had it come to their rescue. Please note we never warrant that any third party software is free form virus or any other malicious code, not that we are accusing the author of anything other than trying to help others.
3. XML Solution with a Zip File
If you are not keen on black box / macro solutions, there is still a manual, “semi-transparent” approach you may take:
- Open File Explorer and rename the file changing the suffix to .zip (e.g. File.xlsm would become File.zip)
- Open the zip file and drag the file Styles.xml out of the zip folder
- Open this file in Notepad by right-clicking this file and selecting ‘Open With…’ and then choose Notepad
- Locate and highlight the text <cellStyles count=”222”> … </cellStyles> (“222” is a dummy number; your file will probably have a different number unless you tend to win lotteries often)
- Delete all of this text
- Save and close this Styles.xml file
- Drag the file back into the zip folder
- Close the zip folder and rename the file once more by changing the suffix back to its original tag (e.g. File.zip would revert to File.xlsm).
It should be noted though that this does remove all of the styles contained in the workbook.
Word to the Wise
I cannot stress enough that it is better not creating corrupt / excess styles in the first place, rather than have to remove them afterwards. However, this should not detract from using styles. They can make model building very efficient and enable last minute changes to formatting. As always, it’s everything in moderation!
If you have a query for the Spreadsheet Skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the SumProduct website.
