Excel files can become unexpectedly large and slow to open. This often happens when a workbook accumulates hundreds or thousands of unused cell styles. These styles are created by copying data from other files or using certain templates. This article explains how to find and reset these excess cell styles to shrink your Excel file and improve performance.
Key Takeaways: Reset Cell Styles to Shrink Files
- Cell Styles gallery: Shows all styles in the workbook, revealing duplicates and unused entries that bloat file size.
- Merge Styles dialog: Copies only the styles you need from a clean workbook, replacing the corrupted or excessive style list.
- VBA macro to delete styles: Automates the removal of all custom cell styles, leaving only the default Excel styles.
How Excess Cell Styles Increase File Size
Every cell style in Excel, whether used or not, is stored in the workbook’s XML structure. When you copy cells from another workbook, import data, or use complex templates, their styles come with them. Excel does not automatically clean up styles that are no longer applied to any cells. Over time, a file can contain thousands of these invisible style definitions. Each one adds a small amount of data, which collectively increases file size, slows down saving, and can cause the Cell Styles gallery to load slowly. The goal is to reset the style list to only the necessary ones.
Steps to Identify and Reset Cell Styles
First, make a backup copy of your workbook. The following methods will modify its core style definitions.
Method 1: Merge Styles from a Clean Workbook
This is the most reliable method to replace all styles. It requires a new, blank Excel workbook.
- Open a new blank workbook
Launch Excel and start with a completely new, empty file. This contains only the default styles. - Open your large workbook
Open the file you want to clean in a separate window. - Open the Merge Styles dialog
In your large workbook, go to the Home tab. Click the Cell Styles dropdown arrow at the bottom of the gallery. At the bottom of the menu, click Merge Styles. - Select the clean workbook
In the Merge Styles dialog box, you will see a list of open workbooks. Select the name of your new, blank workbook and click OK. - Confirm the style replacement
Excel will ask if you want to merge styles that have the same names. Click Yes. This replaces your workbook’s entire style set with the clean default set. - Save the cleaned workbook
Go to File > Save As and save the file with a new name. Check the file size; it should now be smaller.
Method 2: Use a VBA Macro to Delete Custom Styles
This method uses a Visual Basic macro to remove all non-default styles. Enable the Developer tab first via File > Options > Customize Ribbon.
- Open the VBA Editor
In your workbook, go to the Developer tab and click Visual Basic. Alternatively, press Alt + F11. - Insert a new module
In the VBA editor, from the menu, click Insert > Module. - Paste the macro code
In the new module window, paste the following code exactly:Sub DeleteCustomCellStyles()
Dim sty As Style
Application.ScreenUpdating = False
For Each sty In ThisWorkbook.Styles
If Not sty.BuiltIn Then sty.Delete
Next sty
Application.ScreenUpdating = True
End Sub - Run the macro
Close the VBA editor. Back in Excel, go to the Developer tab and click Macros. Select DeleteCustomCellStyles and click Run. The macro will remove all custom styles. - Save the workbook
Save your file. You may need to save it as a .xlsm file if it wasn’t already macro-enabled.
Common Mistakes and Limitations
File Size Does Not Decrease Significantly
If the file remains large, cell styles are not the primary cause. Other factors include unused cells with formatting, excessive conditional formatting rules, embedded objects like images, or large amounts of data in hidden sheets. Use the Inquire add-in or manually inspect these areas.
Styles Reappear After Saving and Reopening
Some add-ins or template features automatically re-add their styles. Check if you have any third-party Excel add-ins enabled. Try opening the file in Safe Mode by holding Ctrl while launching Excel to see if an add-in is the source.
Loss of Custom Formatting You Want to Keep
The merge method removes all custom styles. If you need to keep a few, first note their exact settings. After merging, manually recreate those few styles in the cleaned workbook before you delete the original file.
Reset Methods Comparison
| Item | Merge Styles Method | VBA Macro Method |
|---|---|---|
| Primary Use | Complete style list replacement | Bulk deletion of custom styles |
| Skill Level Required | Beginner, uses built-in dialog | Intermediate, requires running a macro |
| Result | Workbook uses only default Excel styles | Workbook keeps default styles, deletes all others |
| Risk Level | Low, easy to reverse with a backup | Medium, macro permanently deletes data |
| Best For | Files with corrupted or unknown style sources | Files where you know all custom styles are unwanted |
You can now reset cell styles to reduce your Excel file size. Use the Merge Styles method for a clean slate or the VBA macro for targeted removal. For further cleanup, inspect the Used Range on each sheet by pressing Ctrl + End. If the selection goes far beyond your data, clear formatting from the unused rows and columns.