'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Change Currency, Decimal, and Accounting Separators in Excel
Question details
The user needs to modify the decimal, thousands, and accounting separators in Excel to ensure numeric data is displayed according to their specific formatting requirements.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting numeric data, financial figures, and accounting values in a spreadsheet.
- Observed behavior
- Excel displays unexpected currency or accounting values because the internal decimal and thousands separators do not match the intended format, even when system regional settings are altered.
Verify that your Windows regional settings are configured properly, as Excel relies on them by default. If your system settings are correct but Excel still displays the wrong separators, you will need to override Excel's internal advanced settings.
Customize Separators in Excel Advanced Options
Manually override the system separators directly within Excel's settings to control exactly how decimals and thousands are displayed.
By default, Excel inherits formatting rules from the operating system. Disabling this synchronization allows you to apply unique separators exclusively for your Excel environment without affecting other applications.
Click on the File tab in the upper-left corner of the Excel ribbon, then click Options at the bottom of the left-hand menu.
In the Excel Options dialog box, select Advanced from the left-hand navigation pane.
Scroll down to the Editing options section and clear the checkbox labeled 'Use system separators'.
Enter your desired character in the 'Decimal separator' field and a different character in the 'Thousands separator' field.
Click OK to save your changes, then restart Excel. Check your spreadsheet to confirm the accounting and currency values now display properly.
Update Windows Regional Settings
Adjust the overall system region settings so that Excel and all other applications automatically use the correct standard separators.
Easily Manage Decimal and Accounting Separators with WPS Spreadsheet
WPS Office Spreadsheet provides intuitive options for customizing numeric data. You can quickly change decimal symbols and thousands separators while retaining complete compatibility with your existing spreadsheet files.
- 1. Launch WPS Spreadsheet: Open WPS Office and load the spreadsheet containing your financial or numeric data.
- 2. Open the Options Menu: Click the Menu button in the top-left corner and select Options from the drop-down list.
- 3. Access Advanced Formatting: Navigate to the Advanced tab within the Options dialog box.
- 4. Customize Separators: Uncheck 'Use system separators' and manually input your preferred decimal and thousands separators, then click OK.
Frequently Asked Questions
Why didn't my currency symbols update when I changed the thousands separator?
Changing the decimal or thousands separator only modifies how numbers are grouped and split. To change the actual currency symbol (like $ or €), you must select the cells, open the Format Cells dialog (Ctrl+1), go to the Currency or Accounting tab, and select the correct symbol from the drop-down menu.
Can I use different separators for different workbooks at the same time?
No, modifying the separator settings in Excel's Advanced Options applies globally to the entire Excel application. It is not possible to have different decimal or thousands separators for specific worksheets or workbooks simultaneously on the same device.
Will changing the decimal separator to a comma affect my formulas?
Yes, it can affect how you input formulas. If you change your decimal separator to a comma, Excel may require you to use a semicolon (;) instead of a comma (,) as the list separator within your formula arguments. Existing formulas will recalculate normally, but typing new ones requires matching the updated syntax.
Why did my separators revert back to the original format after restarting?
If the 'Use system separators' box was re-checked, Excel will immediately revert to your Windows regional settings. Verify that this option remains cleared in File > Options > Advanced if you wish to maintain custom manual separators.
