How to apply accounting number format in Excel ?

Background

In Excel, Accounting Format and Currency format are similar concepts with minor differences. You can learn to apply accounting number format in excel with the help of this tutorial blog.

Whenever you are working with a data set that has currency or money figures, there are two formatting options available – the Currency format and the Accounting number format.

The main point that sets apart the Accounting number format from other number formats is that it displays numbers with the currency symbols and decimal points aligned perfectly in a column. This gives the dataset an aesthetic look, enhanced visualization and makes it easy to read. Refer the image below to understand how a data set can look like – when you apply accounting format in excel –

Methods for applying accounting number format in excel

Before jumping into the tutorials for applying accounting number format in Microsoft Excel, let us first understand what methods are available for this –

• Accounting Number Format With a Ribbon Option
• Accounting Number Format With a Drop-Down Menu
• Accounting Number format by formatting cells option

Now we will briefly discuss each of the methods highlighted above and master the art of applying accounting number while working with dataset that contains money figures or amount –

Apply the Accounting Number Format With a Ribbon Option

Excel has an option in its ribbon tab to help you quickly use the accounting number format in your spreadsheets and excel workbooks. This is the easiest and quickest way to apply the accounting number format.

• To use it, first, open your excel workbook or spreadsheet with Microsoft Excel.
• Now, select the cells containing the numbers that you desire to turn into accounting numbers.

• After selecting the cells, Go to the Home tab in Excel.
• In Home Tab, Under the Number group [Refer the image above], you will see the Accounting number format shortcut button (Symbolized by a dollar sign).
• Now, select the button if you want to apply the dollar ($) sign. If you want to apply any other currency symbol, you can click on the dropdown arrow next to the button and select the currency you need from the dropdown list that appears. By default, there are other currencies available for quick selection like Pound, Dollars, Euro, Yen etc.

• Your selected cells should now be formatted with the Accounting number format. You will notice your currency symbol that you have selected to the extreme left of the selected cells.

• You will also notice the number formatted to two decimal points. If you desire to change the number of decimal places. You can click the ‘Increase decimal’ or ‘Decrease decimal’ buttons to increase or decrease the number of decimal places as per your requirement.

Remember – You will also notice a thousand separators. As long as you’re in the Accounting number format, you cannot remove this separator from your number.

Apply accounting number format with a Drop-Down Menu

In Continuity to the above tutorial on applying accounting number format in excel, the Number group (under the Home tab) provides another easy shortcut to format numbers in your workbook and spreadsheet, and it is through the dropdown menu located right above the Accounting number button.

In order to use this method, you can follow the steps below –

1. First, Select the cells in your workbook and spreadsheet which you want to format.
2. Go to the Home tab.
3. In Home Tab, under the Number group, you will see the format dropdown menu, as shown in the figure below.
4. Selecting the menu displays a list of different formats that you can apply to your selected cells.
5. Now, you have to select the ‘Accounting’ option from the dropdown list.
6. After clicking the “Accounting” option, your selected cells should now be formatted with the Accounting number format. You will notice the currency symbol to the extreme left of the selected cells.
7. Just like in the tutorial above where we used the shortcut to change the currency figures, If you want to apply any other currency symbol, you can click on the dropdown arrow next to the button with the dollar sign (‘$’). Select the currency you need from the dropdown list that appears.
8. Also, You will also notice the number formatted to two decimal points. You can click the ‘Increase decimal’ or ‘Decrease decimal’ buttons to increase or decrease the number of decimal places according to your requirement.

Now, You will also notice a thousand separators. As long as you’re in the Accounting number format, you cannot remove this separator from your number.

Applying accounting number format by formatting cells option

This is another cool method to apply accounting number format in our excel workbook and spreadsheet. The Format cells dialog box is a versatile dialog box that lets you perform all formatting for a cell or range of cells (including alignment, font, borders, etc.) from one place.

In order to apply the Accounting number format to your cells using the Format cells dialog box, you can follow the steps below:

1. First, select the cells in your workbook or excel spreadsheet which you want to format.
2. Then, mouse Right-click on your selection cells or range.
3. From the context menu that appears, select the ‘Format cells’ option.
4. This will open the Format cells dialog box.
5. From the dialog box, select the ‘Number’ tab. (Refer below)
6. In Format Cells Dialog box, Under ‘Categories’, select the ‘Accounting’ option.
7. This will display a sample on the right-hand side of the dialog box so that you can see how your number is going to look after formatting.
8. By default, the number should be formatted with a dollar sign. If you want to apply any other currency symbol, you can click on the dropdown menu next to ‘Symbol’ and select the currency you need from the dropdown list that appears.
9. You will also notice the number formatted to two decimal points. You can increase or decrease the number of decimal places according to your requirement by pressing the up or down arrows next to ‘Decimal places’.
10. Click OK to close the Format cells dialog box.
11. Your selected cells should now be formatted with the Accounting number format.

Why choose accounting number format over currency format ?

This is one important topic which everyone should consider and understand to master the formatting options in excel. Both the Currency and the Accounting Number, in the first look, look like they’re more or less the same. However, if you look closely, you will observe the following differences  –

1. The Currency format displays zero values as ‘0.00’, whereas the Accounting number format displays zero values as a dash ‘-‘.
2. Now, The Currency format displays the currency symbol right next to the number. The Accounting number format, on the other hand, displays the currency symbol at the extreme left of the cell.
3. Also, The Currency format displays negative numbers with a minus ‘-‘ symbol, while the Accounting number format displays negative numbers within parentheses.

The above three differences make the numbers formatted with the Accounting number format better suited for accounting applications.

Refer the visual example below to understand the differences between currency formatting and accounting number formatting in Microsoft excel –