How to Apply Accounting Number Format in Excel

Excel has many formatting options to give your spreadsheet the look it’s meant for. A simple number can be formatted to completely change what it represents. From a considerable list of number formats, we have two formats pertaining to money; the Currency format and the Accounting Number Format, and today we will talk about the latter.

This tutorial will teach you how to apply the Accounting Number Format with its pros and cons, the differences between the two money formats, and some quick pointers on dealing with the Accounting Number Format. Time to dish the Excel tea.

How To Apply Accounting Number Format In Excel

What is the Accounting Number Format?

The Accounting Number Format applies to cells with numbers. It formats the number in the cell with a chosen currency lined to the left of the cell, sets the number with 2 decimal places, and adds thousands separators (a comma sign separating the number at every thousands digit). Other conditional additions are enclosing negative numbers in parentheses instead of a minus sign and changing zeroes into dashes “ – “.

What is the Accounting Number Format?

Do note that in the last instance, the blank cell is showing as a dash because cell D8 refers B8. If the format was applied to B8, it would remain a blank cell since the format only applies to numeric values.

Difference between Currency and Accounting Number Formats

Both the Currency and Accounting Number Formats are number formats and currency formats. Let’s write up their features against each other to eye-down the differences:

Accounting Number FormatCurrency Format
Lines the currency sign to the left of the cell with the number to the right of the cellLines the currency sign right along the left of the number
Encloses negative numbers in parenthesesShows negative numbers with a minus sign
Shows zero as a dashShoes zero as it is
Indents the number slightly to the left from its place on the rightKeeps the number as it is to the right

As for similarities, both formats add currency signs and set the number to 2 decimal points. Here’s the difference in visual:

Difference between Currency and Accounting Number Formats

How to Apply Accounting Number Format

The Accounting Number Format can be applied in one of three easy ways; 2 of them are sitting on the ribbon menu and the third is from the Format Cells dialog box which is also accessed from the ribbon menu. Let’s have a look at them.

Using Accounting Number Format Button

We will start with the most obvious method of applying the Accounting Number Format and it only takes a click or two.

The cells we want to change to the Accounting Number Format are highlighted below:

How to Apply Accounting Number Format

  • Select the cells to be formatted.
  • From the Home tab, in the Number group, click on the $ button, which is the Accounting Number Format button, to apply the format.

How to Apply Accounting Number Format

  • To select the currency to be applied with the format, click on the arrow of the Accounting Number Format button and choose currency from the list.

How to Apply Accounting Number Format

  • Once applied, you will see the currency sign to the left of the cell with the number, thousands separator(s), and the number in 2 decimal points.

How to Apply Accounting Number Format

The decimal point can be adjusted from the buttons in the Number group. The thousands separator, however, is a special feature of the Accounting Number Format and you will have to change the format of the cell to remove them.

Using the Number Group Drop-down

This option sits just above and a click further than the previous one. A plus point of applying the Accounting Number Format from the Number drop-down is that you can view different formats for the cell along with a little sample text of the format in the drop-down menu. Here’s what to do:

  • Select the cells you want to format.
  • Go to the Home tab, Number group, and click on the arrow in the Number menu to view the drop-down list.

How to Apply Accounting Number Format

  • From the list, select the Accounting

How to Apply Accounting Number Format

  • The applied Accounting Number Format will add a currency sign, thousands separator(s), and set the number with 2 decimal points.
  • The currency and decimal points can be adjusted from the Number group in the Home

How to Apply Accounting Number Format

Using Format Cells Dialog

This method involves the Format Cell dialog because the Accounting Number Format is a cell format, after all. Below we have the steps to apply the Accounting Number Format using the Format Cells dialog:

  • Select the cells for formatting.
  • Click on the dialog launcher in the Home tab’s Number group (or press Ctrl + 1) to launch the Format Cells dialog box.

How to Apply Accounting Number Format

  • In the Number tab of the Format Cells dialog box, select Accounting from the left pane.

How to Apply Accounting Number Format

  • Select the currency and decimal places of choice.
  • Click OK when done.

The Accounting Number Format will be applied:

How to Apply Accounting Number Format

Advantages/Disadvantages of Using Accounting Number Format

Using the Accounting Number Format comes with a few pros and cons that we have listed below for you.

Advantages

  1. It changes a simple number entry with a preset format without much input from the user.
  2. It is a quick feature done in a few clicks.
  3. The format is easily customizable where the currency and decimal points can be edited to choice.

Disadvantages

  1. It always applies the default currency and 2 decimal points, even if you don’t require the decimal points, meaning you may have to adjust the results every time.
  2. Thousand separators are a part of this feature that cannot be edited out.

Notes on the Accounting Number Format

Here are some helpful pointers to keep in mind about the Accounting Number Format, the last 4 pointers are true for all number formats:

  • The format only applies to cells with numbers. Applied on text, the format does nothing to it other than indent it slightly as it does to numbers (unless the text is center-aligned).
  • The format only applies to cells with a single numeric value (there will be no effect on numbers entered in multiple lines by Alt + Enter or on numbers with spaces).
  • A blank cell with the format applied will remain a blank cell. However, a blank cell referred to in a cell with the format applied will show as a dash " – ".
  • Instead of selecting a range of cells to apply the format on, you can select the whole column or row and apply the format to it.
  • A number entered in a cell with the format applied beforehand, will take onto the format.
  • Applying the format only changes the look of the cell; the cell value entered remains unchanged.
  • Removing the format from the cell restores it to the default General

And with a number of pointers, we end this guide. Here’s our takeaway; the Accounting Number Format is a quickly applicable feature, instantly changing the representation of a plain number into accounting format. The rendered format can be easily modified and if it’s still not your cup of Excel tea, there are other number formats to choose from. Otherwise, the components of the feature can also be individually applied. With more worlds of possibilities, we’ll be back to explore a small town from the Excel cosmos!

Compiled by - Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.