Accounting Number Format in Excel

What is Accounting Number Format in Excel?

The Accounting Number format in excel converts the general numbers to the accounting format by placing a currency symbol at the extreme left end of the cell, displaying up to two decimal places for each number and showing zeros with a dash (-).

For instance, below is a list of items at a supermarket and their cost. Column B shows the cost of the items without applying the Accounting Number format, while Column C displays the same after its application. Notice how a dash has replaced the zero.

Accounting Number Format in Excel Shortcut

We can apply and find accounting number format in Excel in several ways, some of which we will see below. However, first, let us look at the Excel keyboard shortcut keys used.

• One way is to press Alt + H + AN and press Enter.

• To increase the decimal point, press Alt + H + 0.
• To decrease the decimal point, press Alt + H + 9.

Another shortcut to add the accounting number format is to press Ctrl + 1, which opens the Format Cells Dialog box, where you can choose the Accounting option.

How to Apply Accounting Number Format in Excel?

As seen in the accounting number format in Excel definition, you can apply this format to any number simply. Let us look at an example where we must apply the format to some numbers which indicate currency. Below is a table that contains numbers in the currency format. Another column is used to apply accounting number format in Excel.

There are three simple ways in which we can apply the accounting number format to a group of cells.

1. Shortcut Keys  – Alt + H +AN + Enter
2. Through the Format Cells dialog box
3. Drop down in the number group.

Shortcut Keys – Alt + H + AN + Enter

• Step 1: Copy the cells’ data from B2 to B8 into Column C.
• Step 2: Select cells C2 to C8, and press Alt + H(for the Home tab) + AN + Enter to select the Accounting Number Format.

Note the differences between the currency and the accounting format. The accounting format has all the dollar symbols in an even line.

Through the Format Cells Dialog Box

Copy the formatted currency numbers from B2 to B8 into Column C, overwriting the previous Accounting Number formatted cells.

• Step 1: Select the cells from C2 to C8. Right-click and select the Format Cells option. Alternatively, you can open the dialog box by clicking Ctrl + 1.

• Step 2: Select Accounting under the Number tab in the dialog box.

Drop Down in the Number Group

• Step 1: Select the cells in Column C. Go to the Numbers group in the Home tab. Choose Accounting from the options available by clicking the drop-down arrow.

• Step 2: Set the accounting currency.

• Step 3: Choose the desired number of decimals by clicking on Increase Decimal or Decrease Decimal.  

Thus, we can apply the accounting number format in Excel to the cells in an effortless manner.

Examples

Let us look at examples of how the Accounting Number format can be used for calculations regarding currency.

Example #1

Below is an example of the revenue of some companies in March and April. But, first, we must calculate the profit/loss of the companies in these two months.

• Step 1: Our first step is to format columns B, C, and D in Accounting Number Format. Select the three columns from B2 to D6 and use any of the above methods for formatting. We will select the Right-click → Format Cells method.

• Step 2: Format the cells as shown below, with two decimal points and the $ symbol.

• Step 3: Now, to find the profit or loss between March and April, enter the formula =C2-B2 in cell D2 and press Enter.

• Step 4: Copy the formula in cells D2 to D6. Check the results.

You can observe that the results show brackets for negative numbers, that is, for those companies with a loss in the March-April time frame. It is because they have been formatted in the accounting number format.

Example #2

Let us look at another example of accounting number format in Excel. Let us look at how the prices of a few items in a bakery are displayed in currency and accounting formats.

Below, Column A represents the items, Column B represents the price per unit in the currency format, and Column C represents the price for ten units in the accounting format. Note that the formatting for columns B and C is yet to be done.

• Step 1: To get the price of 10 units, enter the formula =B2*10 in C2. Press Enter and drag the Autofill in excel till C6 to get the prices per 10 units for all the items.

• Step 2: Select Column B and choose the Currency format from the Number group of the Home tab. Similarly, select Column C and apply the Accounting format from the Home tab’s Number group. Notice the change in the formatting.

• Step 3: Apply the formula =((B2*5)–C2) in cells B7 and C7 to find the price difference between 5 and 10 units of bread.

Also, enter a new item, Jam, which is not priced in cells B2 and C2 as 0. Notice the difference in the formatting. For example, the accounting number format in Column C is different from that of the Currency format.

This example clearly shows the difference between Currency and Accounting Number format in Excel.

1. Negative numbers are indicated in brackets in accounting while they are displayed with the negative sign near the $ symbol in Currency.
2. Zero is shown as – in accounting, while it is displayed as zero in Excel.
3. All $ symbols are in the same line in accounting but not in a straight line in Currency.

Example #3

Let us consider an example where we must print the gold price of 24 carats per ounce in different countries. We have the numbers but must enter them in the respective currency format. 

• Step 1: We must format each country’s gold price based on their currency. Select cell B2 and choose the Accounting option in the Numbers group of the Home tab.

• Step 2: Now, go to cell B3 and select Kenya’s accounting format and currency. Right-click on cell B3, and in the Format Cells option, choose Accounting and select the currency of Kenya, KES. 

• Step 3: Likewise, choose the respective currencies of Singapore (SGD), France (Euro), and India (Rupee) from the drop-down box by right-clicking after placing the cursors on cells B4, B5, and B6, respectively.

Note: Now, you can perform any calculations on these cells irrespective of their formatting. To find the gold price of any country in grams, divide the ounce price by 28.34. Copy the formula =B2/28.34 in cells C2 to C6 to get the gram price of gold for each country. However, the cells have to be formatted again in the Accounting number format in Excel for their respective currencies.

Important Points to Note

• The $ symbol is aligned to the left of each cell in Accounting Number format in Excel.
• We can apply it several ways to a cell or range, the easiest of which is the shortcut keys using Alt + H + AN + Enter.
• We can even customize the accounting number format to display without the symbol. Zero is shown as a simple dash (-).
• Negative values are displayed in brackets in accounting format.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Accounting Number Format in Excel, with its uses and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to Accounting Number Format in Excel. Here we explain how to use the Accounting Format, with examples & downloadable excel template. You can learn more from the following articles –

• VBA Format Number
• Auto Format In Excel
• Convert Text To Numbers In Excel