Formatting cell data in Calc part IV: currency or money

Posted by Greten on 13 Apr 2013 under Free Instructional Applications

This post assumes that you already selected the cell(s) that you want to format and already opened the Format Cells option window. If not, please visit this prerequisite post on cell formatting.

The format for currency still follows the rules regarding decimals places, leading zeroes, and hash Format code as explained in the post about number formatting. The only difference is that the default setting already has two decimal places, the negative numbers in red are checked by default, and it has complicated string at front, something like [$Php-3409].

Accepted currency symbols

The string [$Php-3409] does nothing but to show the Php, representing Philippine peso, as sign appended before the number. The Format code is something like [$Php-3409]#,##0.00. I am not sure why Calc set this as the default but it's too complicated. Encoding Php#,##0.00 or just P#,##0.00 will work the same way. Except of course, using just P will display the number as P100.50.

The Format Cells option window showing the default configuration for currency

Note however that Calc will not accept all symbols and strings to be included in the format code. It will accept Php, P or even the actual peso sign ₱. It will also accept the Japanese Yen sign ¥ or even just plane Y. However, while it accepts the dollar sign $, it will not accept just S, probably because $ is readily available in the keyboard anyway, unlike ₱ and ¥. I haven't tested if any other letters or symbols are accepted but of course you can try.

Color of the negative currency

The full default Format code for currency is:

[$Php-3409]#,##0.00;[RED]-[$Php-3409]#,##0.00.

The $Php-3409 might differ based on your country or your default settings. On the right side of the semicolon (;) is the configuration for negative numbers.

coins and billsIt is common in book keeping to use red ink for negative numbers and thus this formatting. However, the default of Calc is redundant because it still has the negative (-) sign after the [RED] tag. Thus, negative sign will still appear. Just remove it if you want your negative numbers to be in red without negative sign. If you want the negative numbers to be represented by negative sign, just delete the semicolon and everything in its right.

Even though the checkbox says "negative numbers in red". You can change the actual color of the negative number by changing the color RED inside the square bracket. Accepted colors include red, blue, green, yellow, black an white. There's no need to encode them in all caps, they will still work. I am not sure if there are other colors that are accepted but I tried violet, orange and pink and they were not accepted by Calc.

Last updated on 29 Apr 2013. Tags: , , , , ,

Share your thoughts

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

Free and open source software technology and internet usage guide for teachers and other professionals in the education sector