In the US, we use a period, or point, as a decimal separator (1.23). The same is true in the UK and Australia. However, other European countries use the comma as a decimal separator instead. Different delimiters are also used for separating thousands groups (1,000).
Say you’re creating different price lists for different countries. To display the prices correctly for each country, it would be helpful to be able to specify the characters used for decimals in prices and for separating thousands groups so you can keep each number formatted as a currency number for use in formulas if needed. We’ll show you how you can change the decimal and thousands separators in Excel.
RELATED: How to Change the Currency Symbol for Certain Cells in Excel
NOTE: When you change this setting, it affects all the numbers in all workbooks you open from then on, until you change it again. You cannot change this setting only for certain cells. However, when working with different currencies, you can change the currency symbol for certain cells.
To change the types of decimal and thousands separators used in Excel, click the “File” tab.
On the backstage screen, click “Options” in the list of items on the left.
The Excel Options dialog box displays. Click “Advanced” in the list of items on the left.
In the Editing options section, click on the “Use system separators” check box so there is NO check mark in the box.
The “Decimal separator” and “Thousands separator” edit boxes become available. Enter the character you want to use for each in the edit boxes and click “OK”. For our example, we are using a comma as the Decimal separator and a period as the Thousands separator.
The new separators are automatically inserted into all the numbers in your workbook that use them.
RELATED: How to Change Windows’ Default Currency from Dollars to Euros
If you want to change the decimal and thousands separators in Windows for any program (not just Excel) that displays decimal numbers and large numbers, you can change the “Decimal symbol” and “Digital grouping symbol” on the same dialog box (Customize Format) where you can change the currency symbol for Windows. You can also change the number of digits after the decimal point and even how thousands are grouped. In addition to using different thousands separator symbols, some countries group the thousands in number differently.
