How to change thousands and decimals separator in Google Sheets ?

👤 Diwas Poudel 🕒 13 Oct 2021 📁 FIX

Sometimes when you import data in CSV, excel, ods files from other sources then GoogleSheets may fail and shows the number as plain text. This may lead to calculations error. General separators used with numbers are thousands separator and decimal separators.

A thousand Separators are normally used for large numbers which improves readability.US, Great Britain, Thailand uses a comma to indicate a thousand places. France, Germany, Italy, and many more uses space to indicate a thousand places.

Decimal Separators are used to separate the integer parts from fraction parts.US, Great Britain, Thailand uses a period (or dot) to indicate the decimal place. France, Germany, Italy, and much more use comma to indicate the decimal places.

Also Read: List all Google Drive files/folders in Google Sheets?

https://i.ibb.co/bNnfJsS/wikipedia-number-separator.png — fig. source wikipedia

Steps:

1 First open Google Sheet with Data as shown below.

2 click on File > Spreadsheet settings

3 Under General Tab, click on Local Dropdown select desire Locale.

This Locale affects formatting details such as functions, dates, DateTime, Time, Percent, and currency.

By default, it has the United States in my case.

Popular locale and example are as follows.

Locale	Large Number
United States-English	1,234,567.89
GB-English	1,234,567.89
Canada (French)	1 234 567,89
Finland	1 234 567,89
France	1 234 567,89
Germany	1 234 .567,89
Norway	1.234.567,89
Sweden	1 234 567,89
Spain	1.234.567,89
Italy	1.234.567,89

4 Click on Save settings

Then it will reload the page and will see the effect. You can add a new number and you can see those effects as soon as you are out of the cell.

Output for Locale: France

locale-france-googlesheets-amount — fig. Locale: France
Thousand Selector: Space
Decimal Selector: comma

Note that Change in Locale does not change your language in Google Sheets.

Note that: If you have generated data as a response sheet for Google Form, then setting the Sheet Locale may not auto-applied the new local formatting to already existing columns. For this, you have to highlight required columns and apply necessary formatting using Format > Number in the toolbar.

Also Read: Change number instead of percent in Google Sheet Pie Chart ?

Set Locale in Android and iOS

Currently, we cannot set the locale using Google Sheet Mobile Application. To change your sheet’s locale settings, open it in Google Sheets on your computer.

Also Read: How to Increase month by 1 in Google Sheet?