Excel Too Many Custom Number Formats: How to Delete Unused Formats

Excel can slow down or display an error when your workbook contains too many custom number formats. This happens because Excel stores these formats in the file itself, and a large collection can cause performance issues. This article explains how to identify and remove unused custom formats to improve your workbook’s speed and stability. You will learn a manual cleanup method and how to use a VBA macro for larger jobs.

Why Custom Number Formats Accumulate and Cause Problems

Custom number formats are powerful tools for displaying data. You can create formats for phone numbers, special codes, or conditional text colors. Unlike styles, these formats are stored directly within the workbook file where they are created.

The main issue arises when you copy cells or ranges from one workbook to another. Excel brings all custom number formats from the source workbook into the destination workbook, even if the pasted cells do not use them. Over time, this process can add hundreds of unused formats. Excel must load and manage this list every time the file opens, which can lead to slower performance, larger file sizes, and occasional errors when the internal limit is approached.

How Excel Tracks Custom Formats

Excel maintains a hidden internal list for each workbook. You cannot see this list in a worksheet. The only way to interact with it is through the Format Cells dialog or VBA. Formats are only removed from this list when you manually delete them or when no cell in the entire workbook references them and you use a macro to clean up.

Steps to Manually Find and Delete Unused Formats

For workbooks with a manageable number of custom formats, you can use the built-in dialog. This method is best when you know which specific formats you want to remove.

1. Select any cell and open the Format Cells dialog
   Click on any cell in your workbook. Press Ctrl+1 to open the Format Cells dialog box.
2. Navigate to the Custom number formats list
   Click the Number tab at the top of the dialog. In the Category list on the left, select Custom. The right side of the dialog will show the Type list, which contains all built-in and custom formats available in this workbook.
3. Identify and select an unused custom format
   Scroll through the Type list. Custom formats you have created or imported will appear at the bottom of the list. Their codes will look like ##-### or [Blue]0.0. Select a format you believe is not used in any cell.
4. Delete the selected format
   With the unused format selected in the Type list, click the Delete button located at the bottom-right of the dialog. The format will be immediately removed from the workbook’s list. Click OK to close the dialog.

Using a VBA Macro to Clean All Unused Formats

Manually deleting formats is not practical for large collections. A VBA macro can scan the workbook and delete every custom format that is not actively applied to a cell. This is the most effective cleanup method.

1. Open the Visual Basic for Applications editor
   Press Alt + F11 on your keyboard. This will open the VBA editor window.
2. Insert a new standard module
   In the editor menu, click Insert > Module. A blank code window named Module1 will appear on the right side.
3. Copy and paste the cleanup macro code
   Copy the following VBA code and paste it into the blank module window.

   Sub DeleteUnusedNumberFormats()
Dim fmt As NumberFormat
Dim rng As Range
Dim usedFmts As New Collection
On Error Resume Next

   ' Collect all number formats currently in use
   For Each rng In ActiveSheet.UsedRange
   If Not IsError(rng.Value) Then
   usedFmts.Add rng.NumberFormat, CStr(rng.NumberFormat)
   End If
   Next rng

   ' Delete custom formats not in the used collection
For Each fmt In ActiveWorkbook.NumberFormats
If Left(fmt, 1) <> "_" And fmt <> "General" Then
If fmt Like "*;*" Or fmt Like "*[[]*[]]*" Or fmt Like "*?*" Then
On Error Resume Next
Dim key As String
key = CStr(fmt)
usedFmts.Item key
If Err.Number <> 0 Then
fmt.Delete
End If
On Error GoTo 0
End If
End If
Next fmt
MsgBox "Cleanup complete."
End Sub

4. Run the macro to delete formats
   Close the VBA editor window and return to Excel. Press Alt + F8 to open the Macro dialog box. Select the macro named DeleteUnusedNumberFormats and click Run. The macro will execute and show a message when finished. Save your workbook.

If the Macro Does Not Solve Performance Issues

Excel Still Runs Slowly After Format Cleanup

If performance does not improve, the problem may be unrelated to number formats. Check for other common causes like volatile functions, excessive conditional formatting rules, or links to external workbooks. Use the Formulas > Calculation Options menu to set calculation to Manual and see if speed improves.

Error Message About Too Many Formats Persists

This error indicates the workbook’s internal format limit is still being exceeded. The macro may have missed formats used on hidden rows, columns, or other worksheets. Run the macro once for every worksheet in the workbook. Also, check for formats applied to entire columns or rows, as these are harder for the macro to detect if the cells are empty.

Custom Formats Reappear After Saving and Reopening

This usually means the formats are stored in an Excel template file that loads automatically. Check if the file was saved as an Excel Template (.xltx) or if you have a personal workbook with macros that adds formats. Open Excel in Safe Mode by holding Ctrl while starting the program to test if an add-in is injecting the formats.

Manual Deletion vs. VBA Macro: Key Differences

Item	Manual Deletion via Format Cells Dialog	VBA Macro Cleanup
Best For	Removing a few specific known formats	Cleaning large, unknown collections of formats
Speed	Slow, requires user to review each format	Fast, automated process
Risk of Error	High, user might delete a format still in use	Low, macro checks cell usage before deleting
Skill Required	Basic Excel navigation	Ability to run a pre-written VBA macro
Scope	Formats in the active workbook only	Formats in the active workbook only

You can now efficiently manage custom number formats in your Excel workbooks. Use the manual method in the Format Cells dialog for quick removal of specific formats. For a complete cleanup, run the provided VBA macro to delete all unused formats automatically. To prevent the issue from recurring, be mindful when copying data between files. A useful advanced tip is to use the macro on a regular schedule, perhaps monthly, to keep your key workbooks running at optimal speed.