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

by Allen Wyatt
(last updated May 11, 2017)


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.


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


Boxes in Boxes

When you insert a text box within another text box, you may expect any text in the outer text box to wrap around the ...

Discover More

Setting the Return Address Used in Word

When you create envelopes with Word, it normally displays a return address by default. If you can't get Word to retain ...

Discover More

Printing a Chart Across Multiple Pages

Wouldn't it be great to have your huge charts print out on multiple pieces of paper that you could then piece together? ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros 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

Copying Styles

Styles can make your worksheet formatting easier and more consistent. Here's how to copy styles from an existing workbook ...

Discover More

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

View most recent newsletter.


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}] 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 two less than 4?

2018-01-16 16:45:25

Mateo Sem

Just wondering how to run this Macro, I have never run one before and when I try It simply writes the code back to me.
Am I recording it wrong or running it wrong?

2017-06-20 11:32:57

JoAnn Paules

Great macro - unfortunately it must have a couple of hundred styles. I've been clicking yes for the last half hour. (I wish I was kidding)

2017-05-11 18:33:33

Jamie Smith

Allen, I can attest to the magic of this macro! At one point, an office I had worked in had SO MANY styles in it that you could not copy a tab from another spreadsheet into the workbook template being used, which was beyond frustrating to say the least. I highly recommend this macro for a fresh start!

2017-01-25 05:34:01


This code saved my life! Thank you Allen I have just deleted 60,000 unused styles.

By the way you can automatize the code by deleting this part:
intRet = MsgBox("Delete style '" & styT.Name & "'?", vbYesNo)
If intRet = vbYes

It does make excel crash if you have too many styles but if you wait 20min the file will restore and all unused styles will be deleted. Which mean it is not a real crash as the code is runing during those 20min

2016-01-21 12:23:37


Is there a way to do this without the MsgBox? Very annoying when there are dozens of unused styles.

2015-10-07 05:34:35


I had a sheet that somehow inherited styles from spreadsheets going back to 1993 (i'm not joking). My own attempt to code was not picking up many of them ... I had used a For/Each Loop.

I ran the macro in edited form and styles appeared top still be there. So I restarted Excel and re-opened the spreadsheet. Between my code and this I was left with the just two native Excel styles. I could not be happier!

2015-09-17 13:25:20

Brad Butler

Is there a way to permanently delete the default styles for a given workbook? I only want to show about a dozen custom styles. I manually delete the default styles which works for a given Excel session, but they all return the next time that I open the same file.

2015-04-16 11:54:32

Ian Clements

I've installed and run this macro a few times now in Excel 2013, and nothing happens.

I'm assuming that means my styles are OK

2014-09-23 13:17:15


This code makes excel crash each time. Thanks.

2014-06-25 07:51:42

Kenneth Andersen

I think it will be very usefull for me, but my problem is, that I have to run the macro over and over again. I expect that the macro each time cleans up a lot but not all. I have no clue how many userdefined styles the workbook has, but it certantly gets faster. The matter is, that I guess I have run the macro 50 times, and I'm not done. I don't know if it is just about to be finished, or I have to run the macro for days. Could the macro make a total cleanup, or maybee tell me how many userdefined styles still left.
Thanks in advance.

2013-10-29 18:44:15

Bob Jordan

This problem also occurs on Mac Excel 2011 but the styles tool does not work there.

Also the ActiveWorkBook.Styles.Count does not reflect the unused styles on that platform. My ActiveWorkBook.Styles.Count was 27 yet i had 64956 styles in the styles.xml file. I had to resort to a vba cleanup.
I found the XLGeek Coder comments unhelpful in this case.
One needs to get ones code running again by whatever means possible. If there are no Microsoft solutions then one must help ones self.



Bob J.

2013-10-11 16:15:51

Michelle Sharp

Thanks! I've been using this now for a week on the spreadsheets at work. They were all full of extra styles and were giving us all sorts of errors!!

Has anyone found a way to prevent the styles from accumulating when copying and pasting data between spreadsheets in the first place?

2013-01-28 07:57:22

Harish Mali

Thank you very much. This macro was really useful and helped me fix the file that I messed up. :)

2012-11-08 17:29:38

XLGeek Coder

Please don't encourage the use of VBA to deal with styles explosion in Excel workbooks. Excel Object Model based tools (VBA goes against the OM exposed API)can't clean out corrupted system styles or hidden styles. Updated MS KB article on this issue has links to the tools that will guarantee clean up 100%:

2012-11-07 16:29:40

JIm Palmer

Thanks Allen

I had never encountered this problem before. Someone had spent many hours working on a budget file and she was suddenly presented with the message "there are too many styles" after she had copied some data from someone else's file.

Your macro did the trick.

I ended up commenting out the section where it asked the user if they wanted to delete the style.

At first I thought the system had looked up then I broke out of the macro and in the immediate window I entered "? ActiveWorkbook.Sytles.Count"

This showed the number of styles decreasing after running the macro for aobut a minute. It went from over 64,000 to 28,400 and eventually down to about 45 after running the macro a few times.

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

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.