Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Deleting Unwanted Styles.

Deleting Unwanted Styles

Written by Allen Wyatt (last updated January 4, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


4

When you work with other people who use Excel, it is not unusual to copy worksheets from their workbooks into your own workbook. When you do so, the worksheet isn't the only thing that is copied—Excel also copies their formatting styles to your workbook. Manually deleting the unwanted styles can be a hassle, depending on the number of styles. Removing user-defined styles is very easy, though, if you use a macro. The following macro will quickly delete the unwanted styles:

Sub StyleKill()
    Dim styT As Style
    Dim intRet As Integer

    For Each styT In ActiveWorkbook.Styles
        If Not styT.BuiltIn Then
            intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
            If intRet = vbYes Then styT.Delete
        End If
    Next styT
End Sub

The macro needs just a little user input. Whenever the macro detects a user-defined style, you are asked if you want to delete it. Clicking on the Yes button causes the style to be removed from the workbook.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2135) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Unwanted Styles.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using a Single Password for Multiple Workbooks

While password protecting a workbook does provide some security for the contents in the workbook, if you have several ...

Discover More

Creating Sideheads

A sidehead can be used as a layout element for a document. You can create sideheads in a document by using text boxes, as ...

Discover More

AutoFilling Numbers with a Trailing Period

The AutoFill tool is very handy when it comes to quickly filling cells with a sequence of values. Sometimes, however, it ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Creating Styles

Standardize the formatting in your Excel workbooks quickly and easily with the Style feature. Here's how to use it.

Discover More

Applying a Style

Styles can be a great help in making sure that the cells in a worksheet are formatted consistently. Here's how to apply ...

Discover More

Deleting a Style

Excel allows you to define styles that help you display data in a consistent manner. When you no longer need a particular ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 0?

2023-12-01 21:43:11

Sharon Fry

Still works perfectly in Microsoft 365 version in 2023!

I do like the approach in the comment by "millawitch" also. i didn't try it but it makes sense.

Thanks for all you do, Allen!

-Sharon


2022-06-23 07:03:43

millawitch

How to programmatically reset a workbook to default styles

(Source: https://support.microsoft.com/en-gb/topic/how-to-programmatically-reset-a-workbook-to-default-styles-36e94af7-d185-68fb-3962-0882a5260132)

Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style

'Set MyBook to the active workbook.
Set MyBook = ActiveWorkbook
On Error Resume Next
'Delete all the styles in the workbook.
For Each CurStyle In MyBook.Styles
'If CurStyle.Name <> "Normal" Then CurStyle.Delete
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
'Do nothing, these are the default styles
Case Else
CurStyle.Delete
End Select

Next CurStyle

'Open a new workbook.
Set tempBook = Workbooks.Add

'Disable alerts so you may merge changes to the Normal style
'from the new workbook.
Application.DisplayAlerts = False

'Merge styles from the new workbook into the existing workbook.
MyBook.Styles.Merge Workbook:=tempBook

'Enable alerts.
Application.DisplayAlerts = True

'Close the new workbook.
tempBook.Close

End Sub


2020-10-09 08:22:30

Bubbles

Hi, I've used your fun little style killer for some time, but have run into a new wrinkle in an inherited workbook. When I try to run the script I get a VB error

Run-time error '1004':
Delete method of Style class failed


2020-09-17 10:49:30

John Peters

Hi,
I've inadvertently gained some styles from cutting and pasting data into my workbook from another workbook. I've written a macro, no unlike yours that has deleted the majority of the unwanted styles. However, I have 36 unwanted styles left over. I can't delete these in the interface or macro. They have strange names like "LÓÄþÍN^NuNVþˆHÁ(n 2 2" and the protection: Locked is checked. If I rename them (modify style) it creates another style with the new name that can be deleted but the original persists. As far as I can tell these styles are no in use in the workbook. Any suggestions?


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.