How to Apply Accounting Number Format in Excel

Excel spreadsheets are used for a number of applications, but its most common application is in areas of business and accounting.

It only makes sense then for Excel to have an option that quickly formats numbers to make them suitable for accounting.

In this tutorial, we will discuss how to apply the Accounting number format in Excel, how it is different from the currency format, and three different ways to apply the format to numbers in your spreadsheets.

What is the Accounting Number Format?

Excel provides a number of formatting options for numbers, text, dates, and other types of data.

To work with data related to money, there are two formatting options available – the Currency format and the Accounting number format.

Both these formats are fairly similar, in that, both add a currency symbol (usually the dollar symbol by default), two decimal points, and comma separators. There are, however, a few subtle differences between the two.

The main thing 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 and makes it easy to read through, as shown below:

Accounting Format in Excel

Difference between Currency and Accounting Format

At first glance, both the Currency and the Accounting Number formats 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. 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. 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.

You can see a visual of the differences between the Currency and Accounting number formats in the screenshot below:

Visual difference between currency and accounting format

Also read: How to Add Leading Zeros in Excel?

How to Apply Accounting Number Format in Excel

In Excel there are three ways to apply the Accounting number format to numbers in your spreadsheet:

  • Using the Accounting Button
  • Using the drop-down in the Number group
  • Using the Format Cells dialog box

Let us take a look at each of the above methods one by one. We will use the dataset shown below to demonstrate all three methods:

Dataset to apply the accounting format

Using the Accounting Button to Apply Accounting Number Format

This is the easiest and quickest way to apply the accounting number format. To use this method, follow the steps below:

  1. Select the cells you want to format.
  2. Select the Home tab.
  3. Under the Number group, you will see the Accounting number format shortcut button (represented by a dollar sign).Click on Home and then on Dollar sign
  4. 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.Select the currency you want to show
  5. Your selected cells should now be formatted with the Accounting number format. You will notice your currency symbol to the extreme left of the selected cells.Dollar sign at the left of each cell
  6. 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.Click on decrease increase decimal
  7. 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.

Note: If you apply the format to a blank cell, any number subsequently added to the cell will automatically get formatted in the Accounting number format.

Also read: Format Numbers to Show in Millions in Excel

Using the Drop-down in the Number Group to Apply Accounting Number Format

The Number group (under the Home tab) provides another easy shortcut to format numbers in your spreadsheet, and it is through the dropdown menu located right above the Accounting number button.

To use this method, follow the steps below:

  1. Select the cells you want to format.
  2. Select the Home tab.
  3. Under the Number group, you will see the format dropdown menu.Click on the General drop down
  4. Selecting the menu displays a list of different formats that you can apply to your selected cells.
  5. Select the ‘Accounting’ option from the dropdown list.Click on Accounting option
  6. 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.Dollar sign at the left of each cell
  7. If you want to apply any other currency symbol, you can click on the dropdown arrow next to the button with the dollar sign (‘$’) and select the currency you need from the dropdown list that appears.Select the currency you want to show
  8. 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.Click on decrease increase decimal
  9. 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.

Using the Format Cells dialog box to Apply Accounting Number Format

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. To apply the Accounting number format to your cells using the Format cells dialog box, follow the steps below:

  1. Select the cells you want to format.
  2. Right-click on your selection.
  3. From the context menu that appears, select the ‘Format cells’ option.Right click and then click on Format cells
  4. This will open the Format cells dialog box.
  5. From the dialog box, select the ‘Number’ tab.Make sure you are in the Numbers tab
  6. Under ‘Categories’, select the ‘Accounting’ option.Click on Accounting opton in the dialog box
  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.Sample Preview of accounting format
  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.Select the currency symbol
  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’.Specify the decimals you want
  10. Click OK to close the Format cells dialog box.
  11. Your selected cells should now be formatted with the Accounting number format.Accounting format result

Points to Note about the Accounting Number Format in Excel

The Accounting number format provides a very easy way to format your numbers so that they can be used for accounting applications. There are, however, a few points that you need to keep in mind when working with the Accounting number format:

  1. If you apply the format to a blank cell, any number subsequently added to the cell will automatically get formatted in the Accounting number format.
  2. As long as you’re in the Accounting format, you cannot remove the thousands separator from your number. If you do not want the separator, then you will need to apply some other format to your numbers.
  3. Applying the Accounting number format (or any other format for that matter) does not change the original value in the cells. It simply changes how the cells look on your screen.
  4. The format only works with numeric values.

In this tutorial, we provided a general guideline on how to use the Accounting Number format in Excel.

We discussed what the format does, how it is different from the Currency format, and three ways to apply the format to your data. We hope you found this tutorial helpful.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment