It is quite tricky to set custom number formats for a given cell in Google sheets. The way number formats are defined is explained here in the dropdown Custom number formatting. I wanted to use custom currency formats for several sheets that always show two digits after the decimal point and have a separator for every multiple of one thousand like it is common. In contrast to the USA German standard for numbers uses the comma as decimal separator and the point as separator for steps of thousands.
Type of numerical separators
My first pitfall was that I wanted to define the kind of separators (point versus comma) via the number format. But in Google sheets this is set automatically by the language defined for the sheet. It can be found via File -> Sheet settings.
The number format definition
The currency number format I wanted to use is defined by: #,##0.00 €. You can generally use 0, # or ? as placeholders for a digit in your number format. Every 0 shows an insignificant 0 in the number. The # (hash) does not display an insignificant 0 at the corresponding digit. The ? displays a space at the corresponding digit if it is an insignificant 0. This might be a bit confusing, but you can use these placeholders to fill-up numbers with leading zeros or ensure that you always have a certain number of zeros being display after decimal separator. The point is the decimal separator here.
Thus a format of 0.00 means that you always see one digit before the decimal point and two digits after it. So if you just enter 0 as number in a cell with this format it will show up as 0.00. Note that in a Google sheet with German language it will in contrast show up as 0,00 using the comma as separator according to German number standard, but the number format behind this is equal.
You cannot change the position of the thousands separator. You can only enable/disable the thousands separator by using the thousands separator somewhere in your definition or by leaving it out respectively.
Decimal precision
It is interesting that Google sheets seem to store a number in full precision, even if the number format does not show that precision. Assume you have a number format like 0.00. It will show exactly two digits after decimal points. If you enter an example number like 1.234 you will only see 1.23 in the cell. So the precision of the number is cut by the format. If you change the number format now to 0.00# you will see the correct value of 1.234 in your cell. So the # hash can be used to set precision after decimal separator but avoid more insignificant zeros being shown. If you enter 1.23 into this cell now you only see 1.23 and not 1.230. The latter would be achieved with a number format of 0.000.
Author: Michael Keutel | 2015-11-11