Apply Accounting Number Format to Cells in Excel & Google Sheets
In this article, you will learn how to apply the accounting number format to cells in Excel and Google Sheets.
Apply the Accounting Number Format to Cells
Say you have the following list of numbers in Column B and want to format them as accounting numbers.
The accounting number format has a fixed currency symbol (left-aligned) and numbers with two decimal places. All currency symbols and decimal points in the column are lined up.
To apply the accounting format, select the range with numbers (B2:B8) and in the Ribbon, go to Home > Accounting Number Format (dollar icon in the Number group).
As a result, all numbers in the range are formatted as accounting numbers.
The default setting for accounting numbers in Excel is dollar currency with two decimal places. If you want to change the currency to euros, for example, with one decimal place, follow these steps:
- Select the range of values and in the Ribbon, under the Home tab, click the down arrow next to the Accounting Number Format icon. Choose More Accounting Formats…
- In the Format Cells window, set Decimal places to 1, and Symbol to “€ Euro (€ 123)”. Then click OK.
Now you have accounting numbers shown as euros with one decimal point.
If you compare the accounting number format to the currency format, the main difference is in the currency symbol position. The accounting format uses a fixed dollar sign; it’s in the same position for each cell in the column. The currency format uses a floating dollar sign; the symbol is right next to the number, as the first character. In contrast, the general format has no symbol and a variable number of decimal places, based on the value entered.
Format Zeros as Accounting Numbers
Apart from the already mentioned differences between accounting, currency, and general format, there is also a difference in displaying zero as a number. Let’s see how it is appears in these three formats.
As you can see in the picture above, zero is displayed as a dash (-) in the accounting format, with a fixed currency symbol.
Apply the Accounting Number Format in Google Sheets
Everything explained about the accounting format in Excel also applies to Google Sheets. To format cells as accounting numbers in Google Sheets, select the range of numbers (B2:B8) and in the Menu, go to Format > Number > Accounting.
As a result, all numbers in Column B now have the accounting format, just as in Excel.
