In July 2013, I wrote about using custom scripts to improve currency formatting in Google Spreadsheets (Tuenti worked in English, but operated in Euros, which was not well-supported by Google’s locale options). Over the last two months, Google has launched a new version of spreadsheets which exposes many more currency formatting options – although they’re a bit buried in the UI. Here’s how to find them:
Step 1: Format → Number → More Formats … → More Currencies
Step 2: use auto-complete dialog to find Euros
or whatever your preferred currency happens to be.
Step 3: select the specific formatting variant you want
Google now offers numerous options: the symbol (€) or the code (EUR), position before or after the amount (this is language, not currency dependent; in English, currency symbols go before the amount), and rounding. Previously, most of these variants would have required writing custom Apps Script code.
Bonus: Google Sheets remembers recently used formatting options at the bottom of the Format → Number menu
This makes subsequent use of a given format much simpler. Unfortunately, recent formats are only remembered in the context of a given spreadsheet. You’ll have to repeat the above steps for each new sheet you wish to format.. Ideally, I’d like Google to remember recent number formats across sheets.
This functionality eliminates the need for writing custom number formatting functions in Google Apps Scripts, as I showed in my previous post – although that can still be convenient in some circumstances, or for older Google Spreadsheets. There’s not (yet) a way to automatically convert an old Google Spreadsheet to the latest version; you need to copy content out of the old version, into a new one.