Formatting cell data in spreadsheet part VII: currency

Posted by Greten on 05 Dec 2020 under Tools

Currency is one of the most widely used number formats in spreadsheet applications. Spreadsheet applications were first developed as the computerized equivalent of accounting spreadsheets such as journals and ledgers. Hence, its mathematical functions come in handy for those accounts using VisiCalc and Lotus 1-2-3, two of the earliest spreadsheet applications. Since then, spreadsheets have diversified in its functions; you can use it in data analysis, as training for those who will study programming languages, and of course, to build automatic problem generators. Meanwhile, many companies develop specialized accounting software programs accessible through the internet or local area network and have safeguards against fraudulent accounting.

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

Format cells and numbers to default currency

The three spreadsheet applications have a wide variety of how the default format of their currencies. These varieties include whether the currency symbol touches the number or at the far left end of the cell and how negative numbers display (negative sign, red, parentheses). However, you can format a cell as a currency by clicking an icon.

In most cases, the default currency is the US dollar ($). You can change the default currency, which this entry will discuss in the next section. For all three spreadsheet applications covered in this entry, the following default method works.

Method 1: Type and let the application detect the format.

1. Select a cell.
2. Type dollar sign ($) followed by a number.
3. Press Enter.

However, this method works only if you are trying to enter a US dollar value, and your default currency is the US dollar.

• You cannot enter a non-US dollar in this manner unless you are using Calc. Not sure if you can do this with keyboards that have non-US currency symbols.
• In Calc, you can enter only a non-US currency by typing the dollar symbol only if your default currency is set to another currency. In that case, following the steps above will give you the default currency.
• In Excel and Sheets, if you type a dollar sign and a number while the current default currency is not US dollar, the cell entry automatically formats as text.
• In Sheets, this method and the method where you select cells and click the currency icon (which will be discussed next) yields two different results. This method formats a currency without cents or decimal numbers, while the latter method formats the currency to include cents.

Method 2: Select cells and click the currency icon

1. Select a cell or cells. It can be a blank cell or a cell that already has number in it.
2. Click the currency icon.

The currency icon comes in different forms (they are boxed in red rounded rectangles below):

• In Calc, the icon has graphic of a rectangle with cylindrical stack in front, representing bills and coins respectively.

• Excel has a similar icon to that of Calc. However, it appears only when the default currency is not US dollar. If the default currency is US dollar, Excel uses a dollar icon.

• Sheets uses the symbol or initials of the default currency as currency icon.

This method works with the US dollar and any other default currency you configured your spreadsheet application to have. This method will even replicate the currency's traditional number format, such as the number of decimal places and whether the symbol goes to the left or right. Most currencies have two decimal digits, but a few, such as the Tunisian dinar, have three decimal digits. In Calc, if you set the default currency to Tunisian dinar, using either method 1 or 2 will format the number to have three decimal digits. I cannot test if this is also the case in Sheets and Excels because I cannot find the Tunisian dinar among the default currency selections.

Thus, the next question is, how do you change the default currency?

Change the default currency

In all three spreadsheets (Calc, Sheets, and Excel), the default currency is the US dollar ($). You can change the default currency, the method of which varies among the spreadsheets.

Change the default currency in Calc

To change the default currency of Calc:

1. Select Tools » Options on the top menu or press Ctrl + F12.
2. In the Options dialog box, select Language Settings » Languages.
3. Under Formats, open the Default currency dropdown and select the currency you intend to use as default.

The current default currency applies to any new currency within the same or in a separate spreadsheet file in Calc. If you enter a currency using either method 1 or 2 above and then change the default currency, any subsequent currency you entered with either method will use the new default currency. If you open another spreadsheet file in Calc, the new default currency applies to that spreadsheet file.

Change the default currency in Sheets

To change the default currency of Sheets:

1. Select File » Spreadsheet settings on the top menu.
2. In the Settings for this spreadsheet dialog box, select the General tab (or stay there if it's already selected).
3. Open the Locale dropdown menu and select the country whose currency you intend to use as the default currency.

In Calc, each spreadsheet file has its own spreadsheet settings. Any changes you made on the default currency of one spreadsheet does not affect the others. Newly created spreadsheet files revert to US dollars as the default currency.

Change the default currency in Excel

The way a user can change the default currency is different in Excel because the control is outside the spreadsheet application. You don't even need to have Excel running. To change the default currency of Sheets:

1. In the Windows 10 taskbar, click the window icon, or you can also press the Windows key on your keyboard.
2. Type "regional".
3. The Set regional format icon will appear on the menu. Click Set regional format.
4. In the Regional format section, open the dropdown and select English(the country whose currency you want to use as default). For example, select English(Philippines) if you intend to use Philippine peso as the default currency.
5. Open Excel and try method 1 or method 2 above to verify the change in your default currency.

The Regional format dropdown menu provides only countries with English as one of their major languages. I am not aware of any way of setting the currency of a non-English speaking country as default or, perhaps, a way of installing Microsoft Office or Windows to make other countries available.

You cannot change the default currency while Excel is running. If you changed the Locale while an Excel window is open, the default currency before you change the Locale will still apply. For the new default currency to take effect, you have to close Excel and reopen it again

Format cells and numbers to non-default currency

Instead of changing the configuration of your spreadsheet application (or your operating system in case of Excel), an easier and more practical way is to change the currency as necessary. If you notice that you are working with several different currencies in Excel, just change the currency whenever you need to.

The currency icons in Calc and Excel have a small down-pointing arrow that lets you open a dropdown menu with a selection of currencies. Sheets does not have the equivalent dropdown, but you can still select a currency other than the default currency.

Format cells to non-default currency in Calc

1. Select a cell or cells. It can be a blank cell or a cell that already has a number in it.
2. Click the down-pointing arrowhead on the right of the currency icon.
3. Select the currency from the dropdown menu.
4. Click OK or double-click the currency.

Format cells to non-default currency in Sheets

1. Select a cell or cells. It can be a blank cell or a cell that already has a number in it.
2. On the top menu, select Format » Number » More Formats » More currencies.
3. Select the currency from the list in Custom currencies dialog box.
4. Click Apply.

One thing I noticed in Sheets is that some currencies might not be available at first. For example, when I was trying to create a spreadsheet with Philippine peso for the first time, the Philippine peso is not available in Custom currencies. However, when I set the Philippines as the Locale and later set it back to the United States, the Philippine peso remains available in the Custom currencies.

Format cells to non-default currency in Excel

1. Select a cell or cells. It can be a blank cell or a cell that already has a number in it.
2. Click the down-pointing arrowhead on the right of the currency icon.
3. Select the currency from the dropdown menu. The drop down menu is short and lists only a few currencies. If you cannot find the currency that you need, select More Accounting Formats.
   1. Open the Symbol drop down menu.
   2. Select the currency.
   3. Click OK.

Steps 3a to 3c above happen within the Format Cells dialog box. However, this Format Cells dialog box is different from the regular one; only the Number tab is available, and the Accounting category is already selected. Note that above the Accounting category is the Currency category. These two categories work in the same way, except that Currency has additional options on formatting negative numbers. If you select a currency Symbol from the drop down menu while in Accounting and switch to Currency category, the currency Symbol remains the same. The same happens if you select a currency Symbol while in the Currency category and then switch to the Accounting category.

Format codes and accounting conventions

Like other number formats, currencies are also governed by format codes that you can tweak to some extent to modify the way the spreadsheet displays your monetary numbers. Two ways you can alter the display of a currency are as follows:

• Whether the currency symbol is close to the number or on the far left end of the cell
• The way negative currencies are displayed: negative sign, color, parentheses, or a combination of these

In general, the format codes of currencies in spreadsheet applications have the following pattern:

[color][$$]* #,##0.00_);[color][$$]* -(#,##0.00)

Each part of the code does the following:

• Semicolon (;) separates the format code for positive and negative numbers. The code on the left of the semicolon is for positive currencies, while the code on the right is for negative currencies. Note that there is no space before and after the colon.
• The [$$] is the code for the currency symbol. It can be a bare symbol without square brackets or a code representing a special symbol, such as [$$-380A].
• The [color] denotes the color for positive and negative values. A common configuration is to have positive amounts follow the default font color, which is black, while the negative numbers are red. Remove the [color] from the format code if you want the number to follow the default font color. The acceptable colors are [RED], [BLUE], [GREEN], [YELLOW], [CYAN], and [MAGENTA].
• If the asterisk (*) is in the code, the currency sign aligns to the left of the cell. It comes after the code for the currency symbol. There is no space between the currency symbol and the asterisks, while there is a space after it. If you prefer the currency sign to align right with the numbers, remove the asterisk.
• The number code #,##0.00 works in the same as that of regular numbers. See parts II[link] and III [link] for more information.
• The hyphen or minus sign is the traditional way of denoting negative numbers. Hence, it appears only on the right of semicolon. You can place it before the number code or before the currency symbol, depending on where you want to appear the negative sign. You may also remove it if you're going to denote the negative values with parentheses or colors only, as commonly done in accounting.
• The parentheses enclosing the number code on the right of the semicolon is also another way of representing negative money values in accounting spreadsheets. You can remove them if you want to denote the negatives using a negative sign or color only.
• The underscore followed by close parentheses _). Usually found at the end of the positive side (left of semicolon), it allocates space on the right side of a positive number with the same width as a parenthesis. Its purpose is to align the decimal points if parentheses enclose the negative numbers.

The exact codes across the three spreadsheets may differ, as well as their default formats. However, it is not difficult to recognize these patterns and pinpoint the part of the format code you need to change.

Format codes for currency in Calc

The screen capture below shows different currency formats in Calc.

The set in the upper-right is the default currency format with its corresponding format code shown above the sample amounts. Three additional sets of amounts also display with their corresponding format codes, which are all modified from the default format code.

To see and modify the format code:

1. Select the cells or numbers to which you want the modified format code to apply.
2. Press Ctrl+1, or right-click to any of the selected cells and select Format Cells to open the Format Cells dialog box.
3. Modify the Format code field.
4. Select OK to close the Format Cells dialog box and have the new format code take effect.

Download the OpenDocument spreadsheet (ODS) file here.

Format codes for currency in Google Sheets

The screen capture below shows different currency formats in Google Sheets.

The set in then upper-right is the default currency format with its corresponding format code shown on top. Notice that it only has one code and not two codes separated by a semicolon. In this case, Sheets just added the negative sign before the number. Since the format code includes the dollar sign, the negative sign is placed before the dollar sign.

Three additional sets of amounts also display with their corresponding format codes. Format 1 is not a default but available from among the selections in Custom number formats dialog box. Formats 2 and 3 are modified format codes from Format 1.

To see and modify the format code:

1. Select the cells or numbers to which you want the modified format code to apply.
2. On the top menu, select Format » Number » More Formats » Custom number format.
3. Modify the Custom number formats field. You may also select from the choices if one of them is the currency format code, or close to the code, that you need.
4. Select Apply to close the Custom number format dialog box and have the new format code take effect.

View or download the Google Sheets file here.

Format codes for currency in Excel

The screen capture below shows different currency formats in Calc.

The set in then upper-right is the default currency format with its corresponding format code. Notice that Excel's default format code has additional elements that include underscores and additional codes and colons. The underscores followed by open or close parentheses correspond to spaces with the same width as the parentheses. I do not know the use of codes on the right of the second semicolon didn't investigate.

In Samples 2 and 3, I reduced the format codes of Default and Sample 1 to make them as close to the pattern/general code above. When I removed the underscores and the parentheses next to them, the decimal points are no longer aligned because the numbers are aligned to the right, and the close parenthesis occupies width.

To see and modify the format code:

1. Select the cells or numbers to which you want the modified format code to apply.
2. Right-click to any of the selected cells and select Format Cells to open the Format Cells dialog box.
3. In Category, select either Accounting or Currency. Currency provide you options on how to format negative amounts without tinkering with the format code. See first if what you need is there.
4. From the Currency or Accounting, select Custom category.
5. Edit the format code in the Type field as needed.
6. Select OK to close the Format Cells dialog box and have the new format code take effect.

Download the Excel spreadsheet (XLSX) file here.

Conclusion

You can format numbers in spreadsheet applications into currencies since accounting is one of the main uses of spreadsheets. Set the default currency by tinkering on the language or regional settings, use non-default currency, and alter other attributes of displayed currency by editing its format code.

Last updated on 05 Dec 2020.

Share your thoughts

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