Formatting cell data in spreadsheet part III: decimal digits and thousand separators

Posted by Greten on 17 May 2020 under Tools

A decimal separator separates a number between whole number digits and decimal digits. A thousand separator appears after every third digit to the left of the decimal point, thus indicating thousand, million, and so on.

In most English-speaking countries, the thousand separator is a comma, and the decimal separator is a period. However, hair space is getting popular as a thousand separator. In some countries, such as the Netherlands and Belgium, it's the opposite; comma for decimal separator and period for thousand separator.

This entry is the part III of the post series: Formatting cell data in spreadsheet software programs. The features and functions discussed in this entry are based on Calc 6.4.3, Excel 365, and the version of Google Sheets as of May 2020.

Enable the thousand separator

In several spreadsheet applications, such as LibreOffice Calc, Microsoft Excel, and Google Sheets, the default is that there is no thousand separator. To see the thousand separator, you need to format the cell to show it.

Enable the thousand separator in Calc

To enable the thousand separator in a Calc spreadsheet:

  1. Select the cells where you want the numbers to show a thousand separator.
  2. Select the Format as Number icon or press Ctrl+Shift+1; this step automatically adds a thousand separator and two decimal places.
  3. Modify the number of decimal places using the Add Decimal Place and Delete Decimal Place icons. For example, you want to display whole numbers only, click Delete Decimal Place twice or more until you can no longer see a decimal place digit.

Display the "ribbon" that contains Format as Number icon, Delete Decimal Place icon, and everything in between.

An alternative method involves the use of the Formal Cells window:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Press Ctrl+1. You can also right-click on the cell or one of the selected cells and select Format Cells. The Format Cells window opens.
  3. Under Options of the Numbers tab, enable the Thousands separator.
  4. Enter the number of Decimal places you want this number to display.
  5. Select OK.

Screen capture of Format Cells window in Calc with the Thousands separator checkbox highlighted in red.

Enable the thousand separator in Excel

To enable the thousand separator in an Excel spreadsheet:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Open the Format Cells window. Right-click on the cell or one of the selected cells and select Format Cells.
  3. In the Numbers tab, select Number under Category.
  4. Under Options, enable the Use 1000 Separator checkbox.
  5. Enter the number of Decimal places you want to display.
  6. Select OK.

Screen capture of Format Cells window in Excel with the Use 1000 Separator checkbox highlighted in red.

You can also use the ribbon's Number group:

  1. Select the cells where you want the numbers to show thousand separators.
  2. In the ribbon's Number group, select Comma Style. This step adds a thousand separator and configures the number to two decimal places.
  3. Use the Increase Decimal and Decrease Decimal icons to set the number of decimal places to display.

Screen capture: ribbon's Number group in Excel 365. Highlight in red the Comma Style and the Increase/Decrease Decimal icons.

However, the second method configures the number to Accounting even though it does not display any currency symbol. Then, the format changes to Custom when you work on step 3. I cannot determine how this would work as compare to the first method. I suggest you use the first method (the one that opens the Format Cells window) if formatting it just as a regular number is essential to you.

Enable the thousand separator in Sheets

To enable the thousand separator in a Google Sheets spreadsheet:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Select Format » Number » Number; this automatically adds a thousand separator and two decimal places.
  3. Use the Increase decimal places and Decrease decimal places buttons to set the number of decimal places that you want your selected numbers to display.

The Increase decimal places and Decrease decimal places icons shown on a small section of the top menu of Sheets.

Displace decimal places as needed: use Format Code

In the previous section, once you added a thousand separator, you will need to decide on how many decimal digits you want to display. If the number has more decimal digits than the number you configured to display, it will round up. If the number has fewer decimal digits, trailing zeroes are added. For example, set the number of decimal digits to three digits; the number 5.503 displays as 5.503, 6.7826 displays as 6.783, and 8.9 displays as 8.900.

What if you want to display a thousand separators, but you also want to display decimal digits only as needed?

This is the use of the hash in the Format Code. Hash represents a number that will appear only when needed.

Display decimal places as needed in Calc

In Calc, to display thousand separators while allowing decimal digits only to appear as needed:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Press Ctrl+1. You can also right-click on the cell or one of the selected cells and select Format Cells.
  3. Under Options of the Numbers tab, in the Decimal places field, enter the maximum number of decimal places you want to display.
  4. Check the Thousands separator.
  5. The Format code field has something like #,##0.0000 in it. The number of zeroes on the right of the decimal separator depends on the number you enter on step 3. Replace these zeroes with hash.
  6. Select OK.

Format Cells window with #,##0.#### in the Format code. The Format code is highlighted in red.

 

Display decimal places as needed in Excel

In Excel, to display thousand separators while allowing decimal digits only to appear as needed:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Open the Format Cells window. Right-click on the cell or one of the selected cells and select Format Cells.
  3. In the Numbers tab, select Number under Category.
  4. Under Options, enable the Use 1000 Separator checkbox.
  5. Enter the number of maximum of decimal places you want to display in the Decimal places field.
  6. Select Custom under Category.
  7. You will see something like #,##0.0000 in the Type field. The number of zeroes on the right side of the decimal separator depends on the number you entered in step 5. Replace these zeroes with hash.
  8. Select OK.

Format Cells window with #,##0.#### in the Excel Type field. The Type field is highlighted in red.

Display decimal places as needed in Sheets

In Sheets, to display thousand separators while allowing decimal digits to appear only as needed:

  1. Select the cells where you want the numbers to show thousand separators.
  2. Select Format » Number » More Formats » Custom number format.
  3. Select the code #,##0 or #,##0.00.
  4. On the text field on top, edit the code so that the number of hashes on the right of the decimal separator is the maximum number of decimal places you want to appear. For example, you want the cell to display up to five decimal digits; you can encode #,##0.#####.
  5. Select Apply.

Custom number format window with #,##0.##### in the top text field. The field is highlighted in red.

The hash on the decimal places works like this. For example, you format your cell as #,##0.###. It has three hashes on the right of decimal separator, and thus, can display up to three decimal places without trailing zeroes. Enter 4.4, and it displays 4.4; enter 4.45, and it displays 4.45; and enter 4.456, and it displays 4.456. However, if you enter 4.4567, a number with more than three decimal places, it will round off to 4.457.

Maximum in the number of decimal places

In all three spreadsheet application, one of the steps involves deciding the maximum number of digits you want to display. You're probably wondering why there should be a maximum number of decimal digits. Is there any way to not set any maximum, just like in General (Excel and Calc) and Automatic (Sheets)?

Actually, even these default number formats have a limit. Calc, Excel, and Sheets can display a maximum of 15 digits in a cell, including both whole numbers and decimal places.

If you encode more than 15 digits, their behavior varies:

  • Calc rounds off the rightmost and smallest decimal places. If the digits are mostly whole number digits, it changes to scientific notation.
  • Excel truncates (not round off) the rightmost and smallest decimal places. If the digits are mostly whole number digits, it changes to scientific notation. Moreover, you can see all remaining 15 digits only in the Formula Bar. In a cell, only seven or ten digits can be displayed depending on whether the number is converted to scientific notation or not, respectively.
  • Sheets truncates the rightmost and smallest decimal places. If the digits are mostly whole number digits but have at least one decimal place, it changes to scientific notation. If the digits are all whole numbers, Sheets seem to convert it to text—the number is automatically aligned to the left—but mathematical calculations can still be done with it.

Now, why are we discussing the limitation on the number of digits? Well, this is to demonstrate that spreadsheet programs have a limit on how many decimal digits they can display, so you cannot rely on the General and Automatic formats to display decimal digits as needed. You need to decide on how many decimal digits you want to display if, for example, you are going to build problem generators or use spreadsheets to keep class records.

Changing the decimal separator; you can't

You cannot change the decimal separator by using only the format function of the spreadsheet application. You cannot change it unless you change the language or local settings of your spreadsheet, which is beyond the scope of this entry.

You cannot change the decimal separator by simply replacing the decimal separator in the Format code, Type, or Custom number field. For example, your language or local setting is configured to Philippine language, where the decimal separator is period, the system recognizes the period as the decimal separator. If you alter the code and use the period as, say a thousand separator, and comma as decimal separator, it might ruin the code, and the resulting format is not what you expect.

Changing the thousand separator

As mentioned earlier, even though the comma is the traditional thousand separator in several English-speaking countries, hair space is becoming acceptable. Unlike the decimal separator, you can replace the thousand separator with different characters. However, it's not a simple direct substitution.

A Google Sheets workbook showing three sets of seven-digit numbers, one set with no separator, one set with comma as thousand separator, and the last set with hair space as thousand separator.

Revisit the second section, Displace decimal places as needed: use Format Code. The steps to open and alter the code for custom format are similar, except that now, you will alter the left side of the decimal separator; this applies to Calc, Excel, and Sheets. If you already enabled the thousand separation, you will find something like #,##0 in it. Even though the code is only up to thousand, the system understands it as applying every third whole number place. For example, it will display one million as 1,000,000.

A very long number written along a spiral.However, if you replace a comma with another character, it will only apply to the thousand digit. For example, replace a comma with space, and it is going to appear as 1000 000 instead of 1 000 000. To have it apply to every third digit, enter the code so that there are several hashes with the new character every third hash; the code is going to be like # ### ##0.

For the same reason as decimal digits, you need to decide the maximum number of digits you want to show with thousand separators. The character will only apply if it has equivalent places in the code. If you code # ### ##0 and your number is 1,000,000,000, it displays as 1000 000 000.

The spreadsheet applications do not accept any character as thousand separator. For one, it will not accept the character that is already used as decimal separator. Calc, Excel, and Sheets accept character space, hairspace, and hyphen as thousand separator. For other characters, one spreadsheet may accept it but not the others.

Conclusion

In most English-speaking countries, the comma is used as the thousand separator, and period as the decimal separator. You cannot change the decimal separator without changing your local or language settings. You can change the thousand separator by replacing the comma (or the default thousand separator in your locality) with certain characters like hair space, space, and hyphen, but you have to increase the hashes on the whole number parts of the code.

The code, known as Format code in Calc, Type in Excel, and Custom number format in Sheets, is useful in configuring the decimal places and thousand separators. They are made of hashes and zeroes; zeroes leave trailing and leading zeroes while hashes only display digits as needed.

Last updated on 25 May 2020.

Share your thoughts

* Required. Your email will never be displayed in public.

Instructional design and educational technology for effective learning