Excel Trick: Modify Your Number Format - Dots Instead of Commas!

Excel Trick: Modify Your Number Format - Dots Instead of Commas!

Paul Lv12

Excel Trick: Modify Your Number Format - Dots Instead of Commas!

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.

01_clicking_file_tab

On the backstage screen, click “Options” in the list of items on the left.

02_clicking_options

The Excel Options dialog box displays. Click “Advanced” in the list of items on the left.

03_clicking_advanced

In the Editing options section, click on the “Use system separators” check box so there is NO check mark in the box.

04_deselecting_use_system_separators

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.

05_changing_separators

The new separators are automatically inserted into all the numbers in your workbook that use them.

06_comma_decimal_separators

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.

  • Title: Excel Trick: Modify Your Number Format - Dots Instead of Commas!
  • Author: Paul
  • Created at : 2024-08-28 05:23:29
  • Updated at : 2024-08-29 05:23:29
  • Link: https://win-able.techidaily.com/excel-trick-modify-your-number-format-dots-instead-of-commas/
  • License: This work is licensed under CC BY-NC-SA 4.0.
On this page
Excel Trick: Modify Your Number Format - Dots Instead of Commas!