The Accounting Number Format in Excel is used to work with data related to money. The format helps you create financial reports, financial strategies, analyze data, and more.
This tutorial will guide you through the steps to apply the accounting number format to financial values. The format looks like the Currency Format but is different from it. The differences are:
| Accounting Number Format | Currency Format |
| Currency Symbol: Displays the currency sign in a column to the left of the cell. | Currency Symbol: Displays the currency sign next to the number. |
| Zero values: Zero values display as ‘-’ (dash). | Zero values: Zero values display as 0. |
| Negative Numbers: Negative numbers display in parentheses (). | Negative Numbers: Negative numbers display with a ‘-’ (minus) symbol. |
Note: Excel does not display negative numbers in parenthesis by default. It is an optional format which many accountants prefer and has to be selected.
3 Methods of applying accounting number format in Microsoft Excel
- Use the accounting number format icon
- Use the number format drop-down list
- Use the format cells dialog box
Let us begin.
Method #1: Use the Accounting Number Format Icon
The ribbon provides visual access to many Excel features, and for visual learners, it is the best way to learn how to change the format.
To illustrate this method, we will use an existing spreadsheet with a list of assets and values.
Step #1: Open your Excel spreadsheet
You can open an existing spreadsheet or create a new sheet.
By default, Excel applies the General format to numbers. You will know this by clicking on a cell with numbers in it, and looking at the ribbon. On the Home tab ribbon, in the Number Format box, you will see the word General.
Step #2: Select the cells
Select the cells whose format you want to change.
Click with your mouse on the first cell. While you hold the mouse button down, drag your cursor to the bottom of the column, and release the button. The selected area will be gray.
In our example, it is range B2:B9.
Step #3: Change the format
Click on the Home tab to show the Home tab ribbon.
In the Numbers section, click on the small down arrow next to the Accounting Number Format icon. This icon looks like notes and coins.
Tip: Hover with your mouse over the different icons to see their names.
Select the currency option 
The accounting number format is applied to the column you have selected.
Read the last section below to see how to change the negative number to parenthesis.
Method #2: Use the Number Format Drop-down List
Very similar to the previous method, only with this method more options are available.
Step #1: Open your Excel spreadsheet
You can open an existing spreadsheet or create a new sheet.
By default, Excel applies the General format to numbers. You will know this by clicking on a cell with numbers in it, and looking at the ribbon. On the Home tab ribbon, in the Number Format box, you will see the word General.
Step #2: Select the cells
Select the cells whose format you want to change.
Click with your mouse on the first cell. While you hold the mouse button down, drag your cursor to the bottom of the column, and release the button. The selected area will be gray.
In our example, it is range B2:B9.
Step #3: Apply the format
Click the Home tab.
On the Home tab ribbon, in the Numbers Section, you will find the Number Format box, with the word General.
Click the small arrow to the right of this box, for a drop-down menu to appear.
Click on Accounting.
The numbers that were previously selected now display in accounting number format.
Read the last section below to see how to change the negative number to parenthesis.
Method #3: Use the Format Cells Dialog Box
Accessing the Format Cells dialog box can be done either with a keyboard shortcut or by clicking right. The Format Cells dialog box has a wide range of options, and it is important to know how to access and use it.
Step #1: Open your Excel spreadsheet
You can open an existing spreadsheet or create a new sheet.
By default, Excel applies the General format to numbers. You will know this by clicking on a cell with numbers in it, and looking at the ribbon. On the Home tab ribbon, in the Number Format box, you will see the word General.
Step #2: Select the cells
Select the cells whose format you want to change.
Click with your mouse on the first cell. While you hold the mouse button down, drag your cursor to the bottom of the column, and release the button. The selected area will be gray.
In our example, it is range B2:B9.
Step #3: Apply the format
Right-click on the selected cells.
A pop-up menu will appear.
Select Format Cells… to display the Format Cells dialog box.
Or
Press Ctrl and 1 (Ctrl+1) on your keyboard to display the Format Cells dialog box.
In the Format Cells dialog box, click on the Number tab.
Under the Category section, select Accounting.
In the Decimal Places box, choose the decimals to display. By default, Excel applies two decimals.
In the Symbol box, select the symbol to display.
Click the OK button.
Column B now displays the values in accounting number format.
Read the last section below to see how to change the negative number to parenthesis.
Change the display of negative numbers
In our example, cell B8 is not displaying the negative values in parenthesis (). Excel does not assign parenthesis to negative numbers by default. But representing negative numbers in parenthesis is an accounting standard.
There are two ways to change this.
Option A: Format negative numbers using custom format
Select the cell displaying a negative value.
Right-click on it.
From the drop-down menu, choose Format Cells…
The Format Cell dialog box opens.
Click on the Number tab.
In the Category section, select Custom.
The Sample box displays various options.
Click on the option where the number is displayed in parenthesis.
Click on the OK button to save the new format.
The Custom format displays the negative number in parenthesis.
Note: The Custom option applies the format only to the number in the selected cell of the current spreadsheet.
Note: The currency sign depends on your geographic location. You can change it to other currency formats as required.
Option B: Windows Operating System settings
From the Start menu, open the Control Panel.
Under Clock, Language, and Region, click Change date, time, or number formats.
Select Additional settings at the bottom of the Format tab.
On the Numbers tab, for the Negative number format, choose (1.1).
On the Currency tab, for Negative currency format, choose (₹1.1).
Click the OK button, and click the next OK button on the Format tab to save and close the dialog box.
Changing the Windows Operating System settings displays the negative number in parenthesis. It does not change the currency symbol alignment.
Note: The changes made in the operating system settings will reflect in all programs and apps on your device.
Conclusion
The Accounting Number Format works only for numerical values. Use the various options to format your accounting applications with ease.
Take note that negative numbers will not be displayed in parenthesis by default. You either have to change the system settings or change settings on a cell-by-cell basis.