Formatting Issues

How to Fix Excel Custom Number Formats Removed After Saving

Maira Mehtab Sep 27, 2026 873 views

Question details

The user needs to resolve an issue where custom number formats containing specific text strings are removed after saving and reopening the workbook, triggering a file repair prompt.

Product: Excel
Device & OS: not provided
Scenario: Saving and reopening a spreadsheet containing cells with custom number formats that include text, such as liters per day.
Observed behavior: Excel deletes the custom number format upon reopening the file and displays a repair dialog indicating that the file needs to be recovered.

Before you start

Ensure that your custom format syntax is structurally correct by checking for properly enclosed quotation marks around text strings and avoiding unintended escape characters before modifying the document.

Solution 1Recommended

Correct Invalid Characters in the Custom Format

Fix syntax errors in the custom number format string, such as missing spaces inside quotes or unintended escape characters, which cause Excel's XML validation to fail.

Excel is strict about the syntax used in custom number formats. Misplaced spaces, missing quotation marks, or accidental escape characters (like the backslash) can corrupt the file's formatting schema, prompting Excel to strip the format and trigger a repair dialog upon reopening.

Open Format Cells

Select the affected cells in your worksheet and press 'Ctrl + 1' to open the Format Cells dialog box.

Locate the Custom Format

Navigate to the 'Number' tab and select 'Custom' from the Category list on the left.

Adjust Quotation Marks and Spacing

Inspect the format code in the 'Type' input field. Ensure spaces are inside the quotation marks. For example, change '0.0 "liters/d"' to '0.0" liters/d"'.

Remove Accidental Backslashes

Check the entire format string for any unintended backslashes (\). Delete them, as Excel treats them as escape characters which can break the format logic.

Apply and Save

Click 'OK' to apply the corrected format. Save the workbook, close it, and reopen it to verify that the repair dialog no longer appears.

Formatting Tip: Always enclose literal text completely within double quotation marks in custom formats to ensure Excel interprets them correctly.

Solution 2

Test the Workbook in Excel Safe Mode

Launch Excel in Safe Mode to determine if third-party add-ins or extensions are interfering with the file saving process and causing the formatting loss.

Seamless Spreadsheet Formatting

Use WPS Spreadsheet for Error-Free Custom Number Formats

WPS Spreadsheet offers robust formatting options that perfectly support complex custom number formats, ensuring your .xlsx files save and reopen reliably without annoying repair dialogs.

1. Open File in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet file.
2. Access Format Cells: Select the target cells, right-click, and choose 'Format Cells' from the context menu.
3. Apply Custom Format: Navigate to the 'Number' tab, click 'Custom', and enter your desired format code (e.g., 0.0" liters/d") in the Type box.
4. Save Safely: Click 'OK' and save your document. WPS ensures your specific formatting remains intact.

Fully compatible with Microsoft Excel .xls and .xlsx file formats.Advanced custom number format support without file validation or corruption issues.Free, lightweight, and fast to load large datasets with complex formulas.Familiar ribbon interface requires zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a repair dialog when opening my workbook?

Excel triggers a repair dialog when it detects corrupted XML data or invalid properties within the file structure. Invalid custom number format strings, such as misplaced escape characters or poorly enclosed text quotes, cause this validation to fail upon opening.

What is the escape character in Excel custom number formats?

The backslash (\) serves as the escape character in Excel format codes. It forces Excel to display the immediately following character as literal text. Unintended backslashes can break the format code syntax and lead to errors.

How do I delete a corrupted custom number format from my file?

Select any cell and press 'Ctrl + 1' to open the Format Cells dialog. Go to the 'Number' tab, select 'Custom', scroll through the 'Type' list to find the problematic custom format, click on it, and then click the 'Delete' button below the list.

Excel & Data Problems