See all How-To Articles

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 accounting number format initial data

 

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.

 

apply accounting number format initial data

 

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).

 

apply accounting number format

 

As a result, all numbers in the range are formatted as accounting numbers.

 

apply accounting number format 2

 

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:

  1. 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…

 

accounting number currency decimal places

 

  1. In the Format Cells window, set Decimal places to 1, and Symbol to “€ Euro (€ 123)”. Then click OK.

 

accounting number currency decimal places 2

 

Now you have accounting numbers shown as euros with one decimal point.

 

accounting number currency decimal places 3

 

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.

 

accounting vs currency vs general format

 

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.

 

zero formatted as a dash

 

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.

 

google sheets format accounting number

 

As a result, all numbers in Column B now have the accounting format, just as in Excel.

 

google sheets format accounting number 2