Select the numbers and click the Accounting Number Format button ($) in Home → Number. It adds your system’s currency symbol at the left edge of the cell, thousands separators and two decimals. Click the arrow next to the button to pick another symbol, such as $, £ or €.
For any currency, press Ctrl + 1, choose Accounting and pick from the Symbol list. Choose None for the same layout without a symbol, which is what Comma Style gives you.
Accounting and Currency are different formats. Accounting lines the symbol up at the left edge, shows zeros as a dash and puts negatives in brackets (in US settings). Currency, applied with Ctrl + Shift + $, places the symbol next to the number and shows zeros as $0.00.
The Accounting category has no option for how negatives look. To show them in red, switch to Custom and edit the second section of the code, for example _($* #,##0.00_);[Red]_($* (#,##0.00);_($* "-"??_);_(@_).
If a cell doesn’t change, the number is stored as text. Select the cells, click the warning icon, choose Convert to Number, then apply the format.
Microsoft Excel is widely used for business and accounting purposes, and when users use Excel for accounting purposes, their data sets usually have a lot of numbers.
And they might want to convert the numbers from standard number format to accounting number format to make the data set clearer and more suitable for accounting.
The accounting number format changes the general number format by adding the currency symbol at the extreme left side of the cell, thousands separators, and two decimal places (0.00) at the end of each number.
The accounting number format also displays the zero (0) value as a dash “-” and shows negative numbers in parentheses, like (1,234.50).
We have some quick and easy steps below for you to apply the accounting number format in Excel.
Apply Accounting Number Format using Accounting Number Format Button
- First, select the cells or range with the numbers that you want to format.
- After that, go to the “Home” tab and then click on the drop-down arrow next to the “Accounting Number Format” button in the “Number” group.
- Now, click on the accounting format with the currency symbol that you want to add from the drop-down list.
- At this point, your selected cells or range have changed to “Accounting Number Format” by adding the currency symbol, thousands separators, and two decimal places to the number values.
- To increase or decrease the decimal places of the numbers, you can use the “Increase Decimal” and “Decrease Decimal” buttons in the “Number” group on the ribbon.
Apply Accounting Number Format Using the Drop-Down in the Number Group
- First, select the cells or range with the numbers that you want to format.
- After that, go to the “Home” tab and then click on the “Number Format” drop-down (it usually shows “General”) in the “Number” group on the ribbon.
- Now, click on the “Accounting” option from the drop-down list.
- At this point, your selected cells or range have changed to “Accounting Number Format” by adding the currency symbol, thousands separators, and two decimal places to the number values.
As you saw in this method, we don’t have an option to select the accounting format with the choice of currency symbols.
So, the accounting number format from this drop-down will display the default currency symbol of your system, which comes from the region settings of your computer (Windows or macOS).
Apply Accounting Number Format Using the Format Cells Option
With this method, you can change the default currency symbol of your system and select the currency symbol from a long list of currencies of different countries.
- First, select the cells or range with the numbers that you want to format, and then right-click on your selection (or press Ctrl + 1 and skip the next step).
- After that, select the “Format Cells” option from the menu, and you will get the “Format Cells” dialog box opened.
- Now, select the “Number” tab, and under “Category”, select the “Accounting” option.
- Next, click on the “Symbol” drop-down arrow, scroll down the currency options and select the one that you want. Select “None” if you want the accounting alignment without any symbol.
- In case you want to increase or decrease the decimal places of the numbers, you can do the same using the “Decimal places” up and down arrows.
- Once done, click OK.
- At this point, your selected cells or range has changed to “Accounting Number Format” by adding the currency symbol you just selected, with the thousands separators and two decimal places, to the number values.
Accounting vs Currency Format
Both formats add a currency symbol, a thousands separator and two decimal places, so they look alike. But there are a few differences:
- Symbol position: Accounting puts the symbol at the left edge of the cell, so all the symbols in a column line up. Currency puts the symbol right next to the number.
- Zero values: Accounting shows a zero as a dash (-). Currency shows it as $0.00.
- Negative numbers: Accounting shows them in parentheses (with US regional settings), and you can’t change that from the dialog box. Currency lets you choose between a minus sign, red, and parentheses.
- Shortcut: Ctrl + Shift + $ applies the Currency format, not Accounting. There is no default keyboard shortcut for Accounting, so use the ribbon button or Ctrl + 1.
Keep in mind that the dash only appears for a real zero. A value like 0.004 still shows as 0.00, as it is not zero. And a number formatted as Accounting is still a number, so you can use it in any formula.
Accounting Format with a Custom Number Format
If you open Format Cells → Number → Custom after applying the Accounting format, you will see the code Excel uses behind it. For US dollars, it looks like the code below. It has four parts, for positive numbers, negative numbers, zero and text:
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
You can copy this code and change it. For example, replace “0.00” with “0” in all the parts to show the accounting format without decimals, or replace the $ with another symbol.
And if you need the accounting look inside a text (for example, to join an amount with a label), use the TEXT function. The result is text, so use it for display only:
="Total: "&TEXT(A1,"$#,##0.00;($#,##0.00);-")
Remove the Accounting Number Format
To remove the format and get your numbers back as they were, select the cells and choose “General” from the “Number Format” drop-down on the Home tab. You can also use Home → Clear → Clear Formats, but that removes all the other formatting (fonts, colors, borders) as well.
If you see #### in a cell after applying the format, the column is too narrow for the number with its symbol and decimals. Double-click the right border of the column header to fit the column.
Frequently Asked Questions
Why does the Accounting format show a dash instead of 0?
That’s how the format is built: the third part of its code shows a dash for zero values. If you want to see 0.00, use the Currency format, or change the third part of the custom code to show 0.00.
How do I apply the Accounting format on a Mac?
The steps are the same. Use the Accounting Number Format button on the Home tab, or press Command + 1 to open Format Cells and select Accounting under Category.
The button uses the currency from your computer’s region settings, so you can change it there (Control Panel → Region on Windows). For one file, it’s easier to pick the symbol from the button’s drop-down list or from the Symbol list in Format Cells.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.