Quick Links
Both the Currency and Accounting number formats in Excel are used to display monetary values, including the symbol for the chosen currency. However, in this article, I’ll explain their subtle differences, giving you a better idea of which one to use in your spreadsheet.
Their Default Alignment
When you type an unformatted number in Excel, it aligns to the right of the cell. This is so that the decimal points line up with other numbers in the same column, and you can instantly and easily compare the number of digits in each value in a list, especially when they all have one or more decimal places.
This also applies to values formatted with the Currency number format—they align to the right of the cell, with the currency symbol placed directly before the number. Notice, also, how Excel automatically inserts a comma to represent thousands—though you canchange this comma to a periodas is standard for some currencies—and two decimal places.
For the same reasons, values formatted with the Accounting number format are also right-aligned by default, though the currency symbol aligns to the left of the cell. As a result, you can scan the values more quickly, without the currency symbol getting in the way. Similarly, you can also scan the currency symbols to make sure they’re consistent. Again, Excel adds two decimal places, as well as commas to values of 1,000 or more to aid legibility.
Their Alignment Adaptability
As well as behaving like unformatted numbers in their default alignment, values formatted with the Currency number format can also be realigned to the left or center of the cell in the same way that numbers and text can. This flexibility is good forspreadsheets intended for less formal circumstances, where style is more important than number crunching.
On the other hand, values formatted with the Accounting number format maintain their default alignment (the currency symbol on the left, and the value on the right), regardless of the manual alignment you try to apply to those cells.
How Zero Is Displayed
You may have noticed a theme with the Currency number format—it usually behaves in the same way as the Number format, except for the addition of a currency symbol before the value and two decimal places by default. This is also true with neutral values.
However, the Accounting number format displays zeros as a dash, making those cells easier to distinguish from positive or negative values in a large accounting spreadsheet.
How Negative Monetary Values Are Displayed
The Currency and Accounting number formats differ in terms of the options for displaying negative financial numbers, with Currency giving you more choice.
When assigning the Currency number format to the relevant cells, press Ctrl+1 to launch the Format Cells dialog box. There, you can see that negative numbers can be displayed in red, with or without the negative symbol.
The Accounting number format, however, gives you less choice. Negative values formatted this way are displayed purely as negative financial figures, with the negative symbol placed before the left-aligned currency symbol.
Tochange how Excel displays negative values, click “More Number Formats” in the Number Format drop-down menu in the Number group of the Home tab, and click “Custom.” For example, some style guides dictate that negative values should be displayed in parentheses, and this is where you can achieve this.
So When Should You Use Currency vs. Accounting?
You may be wondering whether to use the Currency or Accounting number format in Excel. Here’s a summary of the contexts in which either tend to be used:
This table summarizes their properties and visual differences:
Accounting
Default alignment
The currency symbol and the value are aligned to the right.
The currency symbol is aligned to the left, and the value is aligned to the right.
Alignment adaptability
You can change the symbol and value alignment to the left or center if needed.
How 0 is displayed
Neutral values are displayed as $-.
How negative numbers are displayed
You have the option to display negative numbers in red font, with or without the negative symbol.
Unless you use a custom number format, negative values are displayed with the negative symbol to the left of the left-aligned currency symbol.
How to Use the Monetary Number Formats
First, select the cells, rows, or columns containing or due to contain the financial values. Then, in the Number group of the Home tab on the ribbon, open the Number Format drop-down list. At this point, you have two options. The first is to click the Currency or Accounting number formats in that list to use their default settings, though clicking “More Number Formats” gives you access to more options.
Alternatively, if you know you want to use the Accounting number format, click the “Accounting” icon in the Number group of the Home tab, or click the drop-down arrow to change the currency.
In the Format Cells dialog box, use the left-hand menu to select which number format you want to use. In this example, I’ve clicked “Currency,” and I can then choose the number of decimal places, the symbol, and how negative values are displayed.
you may alsoincrease or decrease the number of decimal places in Excelby clicking “Increase Decimal” or “Decrease Decimal” in the Number group of the Home tab on the ribbon, or setting the default number of decimal places in the Excel Options dialog box (press Alt > F > T, click “Advanced,” and change the “Places” option in the Editing Options section).
When you’re done, click “OK.”
Now that you know the difference between these two number formats, take the time to learn someessential Excel functions for budgeting, so that your spreadsheet both looks the part and helps you manage your money.
