You open an Excel workbook and find that your carefully crafted custom number formats now display dates as text, decimals use commas instead of periods, or currency symbols have changed. This problem occurs because Excel reads regional settings from Windows to interpret number formatting symbols. When you update your Windows region or language settings, Excel automatically reapplies the new separator characters to existing custom formats. This article explains why this happens and provides step-by-step methods to restore your original custom number formats.
Key Takeaways: Restoring Custom Number Formats After Regional Changes
- File > Options > Advanced > Use system separators: Unchecking this option lets you lock decimal and thousands separators independently of regional settings.
- Ctrl+1 > Number > Custom: Directly edit the format string to replace the comma with a period or vice versa based on your preferred locale.
- Quick Analysis tool or Paste Special > Formats: Copy a cell with the correct format and apply it to affected ranges to restore consistency quickly.
Why Regional Settings Change Custom Number Formats
Excel stores custom number formats as text strings that use specific symbols for the decimal separator and thousands separator. The default symbols depend on your Windows regional format. For example, in the United States, a period is the decimal separator and a comma is the thousands separator. In many European countries, a comma is the decimal separator and a period is the thousands separator.
When you update Windows regional settings through Settings > Time & Language > Language & Region, Excel detects the change the next time you open the application. Excel then reinterprets any custom format that uses the old separator symbols. A format that previously displayed 1,234.56 might suddenly show 1.234,56 or even become unreadable as a text string. This behavior is by design: Excel tries to keep your formats compatible with the new locale, but it often breaks existing formats that rely on the old symbols.
The Role of the “Use system separators” Option
By default, Excel checks the box File > Options > Advanced > Use system separators. When this box is checked, Excel reads the decimal and thousands separator from Windows regional settings. Any custom number format you create uses those system separators. If you later change the regional settings, Excel updates the separators in all formats, including custom ones. This is the direct cause of the formatting change you see.
Steps to Fix Custom Number Formats After Regional Settings Update
Follow these steps to restore your custom number formats. The first method prevents future changes. The second method repairs existing formats.
Method 1: Disable System Separators to Lock Your Format
- Open Excel and go to File > Options
Click the File tab in the ribbon, then click Options at the bottom of the left menu. The Excel Options dialog opens. - Navigate to the Advanced category
In the Excel Options dialog, click Advanced on the left sidebar. Scroll down to the Editing options section. - Uncheck “Use system separators”
Find the check box labeled Use system separators. Uncheck it. The Decimal separator and Thousands separator fields below become editable. - Enter your preferred separators
Type the decimal separator you want, such as a period or a comma. Type the thousands separator you want. For US format, use period for decimal and comma for thousands. For European format, use comma for decimal and period for thousands. - Click OK to apply
Click OK at the bottom of the Excel Options dialog. Excel now uses your fixed separators regardless of Windows regional settings. Existing custom formats that already use those separators will display correctly.
Method 2: Edit the Custom Number Format String Directly
- Select the cell with the broken format
Click the cell that shows the wrong number format after the regional update. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. The Format Cells dialog opens with the Number tab active. - Go to the Custom category
Scroll down the Category list and click Custom. The Type field shows the format string, such as #,##0.00 or #.##0,00. - Edit the separator symbols in the Type field
Replace the decimal separator with the correct symbol for your current region. For example, change #,##0.00 to #.##0,00 if you now need a comma as the decimal separator. Also swap the thousands separator if needed. - Click OK to apply the edited format
Click OK. The cell now displays the number using your corrected format string.
Method 3: Copy a Correctly Formatted Cell to Other Cells
- Create a cell with the correct format
In a blank cell, type a sample number. Apply the custom number format you want using Ctrl+1 and the Custom category. - Copy the formatted cell
Select the cell and press Ctrl+C to copy it. - Select the range you want to fix
Click and drag to select all cells that have the broken format. - Paste only the format
Right-click the selected range. Under Paste Options, click the icon labeled Formats (a paintbrush). You can also press Ctrl+Alt+V, then press R, then press Enter. This applies the correct format without overwriting the cell values.
If Excel Still Has Issues After the Main Fix
Custom Format Shows as Text After Regional Change
If a custom format string is not recognized after the regional update, Excel may treat the cell as text. To fix this, select the cells, press Ctrl+1, go to the Number tab, and reapply a standard number format like Number or Currency. Then reapply your custom format by typing the correct string in the Custom category.
Formats Change Back After Closing and Reopening Excel
If you edited the format string but the problem returns after you close Excel, you have not disabled the Use system separators option. Go back to File > Options > Advanced and uncheck Use system separators. Set your preferred separators manually. This prevents Excel from overwriting your format strings on startup.
Regional Settings Update Changes Date Formats
Date custom formats also depend on regional settings. For example, the format string mm/dd/yyyy might become dd.mm.yyyy after a regional change. To lock date formats, use the same method: uncheck Use system separators and edit the date format string in the Custom category. Alternatively, use the built-in Date category formats, which are less prone to breaking.
Disable System Separators vs Edit Format String: Key Differences
| Item | Disable System Separators | Edit Format String |
|---|---|---|
| Scope | Affects all current and future custom formats in the workbook | Affects only the selected cells |
| Prevents future changes | Yes | No |
| Requires reapplication after regional update | No | Yes |
| Works with existing formats | Yes, after you set separators | Yes, after you edit the string |
| Best for | Users who frequently change regional settings | One-time fix for a few cells |
You can now restore and lock your custom number formats after any Windows regional settings update. Start by unchecking Use system separators in File > Options > Advanced to prevent future format changes. Then edit any broken format strings using Ctrl+1 and the Custom category. For a quick fix across many cells, use Paste Special > Formats to copy a correctly formatted cell. If you work with multiple regional formats, consider saving a template workbook with your preferred separators and formats ready to apply.