I often tell participants in our Microsoft Excel courses that consistent formatting is a simple but very effective way to make worksheets easier to understand.
When a spreadsheet has a financial focus, many people believe that key figures should be displayed with the accounting number format.
Before we talk about how to apply the accounting number format in Microsoft Excel, let’s define what it is.
What is the Accounting Number Format?
The accounting number format is similar to the currency format in Excel.
Both formats display monetary values with currency symbols that can be customized for various regions (including the dollar sign, euro, pound, etc.) but the accounting number format has specific attributes.
The accounting number format:
- Displays the currency symbol at the left border of the cell.
- Incorporates a thousands separator and two decimal places.
- Indicates zero values with a dash.
- Displays negative values in parenthesis.
If you like all of the attributes outlined above but you don’t need the currency symbol, comma style might be a better fit.
1) Apply the Accounting Number Format with the Ribbon
To format cells with the accounting number format, select the appropriate command from the ribbon in Excel.
In the screenshot below, I selected B6:E6 then clicked the Accounting Number Format button in the Home > Number group.
If you want to eliminate the decimal places to further enhance the display of totals in row 6, click the Decrease Decimal button twice.
Only eliminate the decimal places if your worksheet deals with whole numbers.
2) Apply the Accounting Number Format Using Keyboard Shortcuts
If you prefer to use keyboard shortcuts when applying the account number format, the following sequence will do the trick.
Select the cells you’d like to format, then press Alt, H, A, N, then press Enter. To decrease decimals, press Alt, H, 9.
3) Apply the Accounting Number Format with the Format Cells Command
You can also apply the accounting number format using the Format Cells dialog box.
Once again, you’d select the cells you want to format, then you right-click anywhere in the select range and click Format Cells…
In the Format Cells dialog box, select Accounting in the Category: list, configure Decimal places and Symbol then click OK.
The accounting number format makes it easy for anyone viewing your worksheet to understand that your calculations involve money. Eliminating unnecessary decimal places from whole numbers further simplifies your presentation of financial data.
If you’ve enjoyed learning about the accounting number format in Microsoft Excel, you might want to check out our complete guide to basic formatting in Excel.