Formatting Issues

How to Identify and Remove Custom Number Formats in Excel

Guest Writer Sep 28, 2026 869 views

Question details

The user needs a method to reliably identify custom number formats within a workbook and delete the ones that were created accidentally or are no longer needed.

How to Identify and Remove Custom Number Formats in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up a workbook's formatting list by removing unwanted or accidentally created custom number entries without affecting system defaults.
Observed behavior: The workbook contains a mix of built-in and custom number formats. Because built-in formats cannot be deleted, it is difficult to distinguish which ones can be safely removed.

Before you start

Before making changes, keep in mind that deleting a custom number format will cause any cells currently using that format to automatically revert to the default 'General' format.

Solution 1Recommended

Identify and Delete Custom Formats by Comparing Workbooks

The most effective way to determine which formats are custom is to compare your current workbook's format list against a completely new, blank workbook.

Since Excel does not explicitly label formats as 'built-in' or 'custom', comparing two workbooks side-by-side visually highlights the additions.

Built-in formats are hardcoded into Excel and are protected from deletion. You will notice the 'Delete' button becomes unresponsive when a built-in format is selected.

Open a blank workbook

Create a completely new, blank Excel workbook and open it alongside your affected workbook.

Access the Format Cells dialog

In both workbooks, select any cell, right-click, and choose 'Format Cells', or press 'Ctrl + 1' on your keyboard.

Navigate to the Custom category

Go to the 'Number' tab and click on the 'Custom' category at the bottom of the list on the left side.

Compare the format lists

Scroll through the Type list in both workbooks. Any formatting codes that appear in your affected workbook but not in the blank workbook are custom formats.

Delete the unwanted formats

In the affected workbook, select the custom format you identified and click the 'Delete' button to remove it.

Identify and Delete Custom Formats by Comparing Workbooks

Delete Button Disabled: If you select a format and the 'Delete' button is greyed out, that means it is a built-in Excel format and cannot be removed by design.

Efficient Data Formatting

Easily Manage Custom Number Formats with WPS Spreadsheet

WPS Spreadsheet offers a highly intuitive formatting interface, allowing you to quickly spot, manage, and delete unused custom number rules while fully supporting existing Excel workbooks.

1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing the custom formats you wish to manage.
2. Open Format Cells: Right-click on any cell in your worksheet and select 'Format Cells' from the drop-down menu.
3. Select Custom format: Click on the 'Number' tab and navigate to 'Custom' in the category list.
4. Delete unwanted formats: Scroll through the list to locate your manually added formats, select the one you do not need, and click 'Delete'.
5. Save changes: Click 'OK' to close the dialog and save your workbook to apply the formatting changes.

Seamless and perfect compatibility with Microsoft Excel (.xlsx and .xls) formatsClear, user-friendly interface for managing custom cell formattingLightweight application design for faster workbook loading and editing

Frequently Asked Questions

Why is the delete button greyed out when I select a format?

The delete button becomes disabled when you select a built-in Excel format. The software protects these default formats from being removed. You can only delete custom formats that were manually added by a user.

Will deleting a custom number format erase my cell data?

No, deleting a custom format does not alter the underlying data or numbers in your cells. However, the visual display of any cell previously using that format will revert to the default 'General' number format.

Can I bulk delete all custom number formats at once?

Excel does not have a native feature to bulk-delete all custom number formats simultaneously. You must select and delete them one by one in the Format Cells dialog. Alternatively, macro (VBA) scripts can be used for bulk removal.

How do custom number formats get into my workbook accidentally?

Custom formats are often accidentally imported when you copy and paste cells, sheets, or charts from another workbook that contains custom formatting rules.

Document & File Issues