How To Format Multiple Currencies

Spreadsheets,Tips 15 March 2011 0 Comments

We’ve already shown you how to convert between different currencies in a spreadsheet. But how do we format the resulting figures such that 100 US dollars is shown as $100 and 100 British pounds is shown as £100? Google docs spreadsheets allows you to set a global setting for the currency, and then override that setting on specific ranges of cells. Here’s how:

  • To set the global settings for the spreadsheet, click on the File menu and select Spreadsheet settings…:

  • The Spreadsheet Settings dialog will appear. In the Locale drop down list select the country which you’d like to use for the default currency:

  • In this example we’ve selected South Korea. Pick whatever country you would like for your default currency. Click the Save Settings button on the Spreadsheet Settings dialog to save your choice and close the dialog.
  • The Format as Currency button on the toolbar will now show the currency symbol for the country you chose above. In our case we see the symbol for the South Korean Won on the Format as Currency button on the toolbar. If we select a range of cells in the spreadsheet and click on the Format as Currency button, the values in these cells will be represented as South Korean Won:

Thats’s pretty good, but you’ll also notice that any other values in the same rows will also be formatted as the same default currency! What if other values in the spreadsheet need to be formatted as a currency other than the default currency? It’s easy, and here’s how:

  • Select the range of cells which you’d like to format as another currency. Click on the More Formats button on the toolbar (it’s the one with the 123 on it). Select More currencies from the menu that appears. That will cause another menu listing other currencies to appear. Select the currency with which you would like to format the range you had selected. In the example below, we’re selecting British Pounds:

Continue formatting in other currencies using the same technique. Here’s an example where we set the default currency to US dollars and overrode the currency format for columns C (pounds) and D (euros):

You can find the spreadsheet used in this example here.

Tagged in , , , , , , , , ,

Leave a Reply