How to Apply the Accounting Number Format in Excel? (3 Best methods)

Note: This tutorial on how to apply the accounting number format in Excel is suitable for all versions of Excel including Office 365.

Excel is one of the most used spreadsheet applications cutting across industries. But it has a very special and popular status in business and accounting. 

This is because it has many built-in accounting friendly features that add up to a very smooth user experience. 

One such important feature is the Accounting Number Format. 

Related:

How to Enable Excel Dark Mode? 3 Simple Steps

How to Add Error Bars in Excel? 7 Best Methods

How to Superscript in Excel? (9 Best Methods)

In this tutorial, I’ll show you how to use the accounting number format in the proper and easy way. I’ll cover:

1. What is the Accounting Number Format in Excel?
2. Accounting Number Format in Excel vs Currency Format
3. How to Apply the Accounting Number Format in Excel?
   • Accounting Number Format Button
   • Dropdown in the Number Group
   • Accounting Format Using the Format Cells Dialogue Box
4. Important Note on the Accounting Number Format in Excel

You can download the practice Accounting Number Format Excel sheet here and follow along with me.

Watch this short video to learn how to apply the accounting number format in Excel

What is the Accounting Number Format in Excel?

Now, you may be wondering what is so special about this accounting number format that it demands a separate guide. 

Let me explain. 

Excel has built-in formatting options for various data types like numbers, texts, dates etc. For data variables dealing with money, it has the following two formatting options: 

1. Currency Format
2. Accounting Number Format. 

Though they may appear similar at first glance, upon closer examination, they have some important differences. 

What sets the accounting number format apart is its perfect sense of alignment. That is the currency symbol and the decimal points are perfectly aligned in the whole column, making it easier to read. 

Accounting Number Format in Excel vs Currency Format

Both the accounting and currency formats add a currency symbol, commas, and decimal points. 

However, there are some differences that separate these two. Here is the list of differences between the currency format and the accounting number format in Excel. 

1. The currency format displays negative values as it is, whereas the accounting format displays them inside brackets. This is in accordance with the international accounting standard. 

2. The currency format displays zero values as it is, whereas the accounting format replaces them with a ‘-’ as per the International Accounting Standards (IAS). 

3. The currency symbol appears right next to the number in the currency format. Whereas, it appears at the extreme left end in the accounting number format. 

4. The decimal points and comma separators are perfectly aligned in the accounting number format. That is not the case with the currency format. 

Also Read:

How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)

How to Shade Every Other Row in Excel? (5 Best Methods)

VLOOKUP for Dummies (or newbies)

How to Apply the Accounting Number Format in Excel?

There are three ways to  apply the accounting format in Excel:

1. Accounting Number Format Button
2. Dropdown in the Number Group
3. Format cells dialogue box. 

Now, let us see each of these methods one by one.

How to Apply the Accounting Number Format in Excel using the Accounting Number Format Button?

Using the accounting number format button is the easiest and most straightforward method to apply the accounting number format in Excel. Here’s how to do it:

Step 1 – Select the Range of Cells

Select the cells you want to format. You can select more than one range and even the entire sheet.

Step 2 – Use the Accounting Number Format Button

Locate and click on the Accounting Number Format button in the Number group under the Home tab. This will apply your regional currency to the format. The keyboard shortcut is Alt+H+AN.

Step 3 – Set the Accounting Currency

If you need some other currency to be applied, click on the dropdown button next to the Accounting Number Format button and click on the suitable currency.

Step 4 – Increase or Decrease the Decimals

Now, increase or decrease the decimal values as per your requirements. Please note that you cannot change the appearance of the comma separators. Keyboard shortcuts are Alt+H+0 or Alt+H+9 to increase or decrease the decimal values respectively.

Dropdown in the Number Group

Another way to access the accounting format option is to use the dropdown menu in the number group. 

1. Select the cells you want to format. 

2. Locate and click on the Dropdown menu button in the Number group under the Home tab. 

3. Now, select Accounting from the list of formats appearing in the Dropdown menu. This will apply your regional currency to the format. The keyboard shortcut is Alt+H+N+A, followed by an Enter. 

4. If you want to change the currency, click on the dropdown button next to the Accounting Number Format button and click on the suitable currency. g

5. Now, increase or decrease the decimal values as per your requirements. Please note that you cannot change the appearance of the comma separators. 

Accounting Format Using the Format Cells Dialogue Box

The format cells dialogue box is another effective way to apply the accounting format in Excel. 

To do this, just follow these steps: 

1. Select the cells you want to format. 

2. Right-click on the selection and click on the Format cells options in the right-click menu. Alternatively, you can use the keyboard shortcut Ctrl+1. 

3. In the Format Cells dialogue box, select the Number tab.

4. Select ‘Accounting’ under ‘Category’. 

5. Choose your currency symbol and the number of decimal places and click OK. 

Important Note on the Accounting Number Format in Excel

1. The accounting format is applicable only to numerical values. 
2. Applying the accounting format to a blank cell will convert any future values of the cell to the accounting format. 
3. The accounting format has no effect on the original cell values. It simply changes the appearance of the values. 

Suggested Reads:

How to Group Worksheets in Excel? (In 3 Simple Steps)

Easily Make a Bullet Chart in Excel—2 Examples

How to Easily Create an Excel Histogram?—2 Best Methods

How to Add Subscript in Excel? (6 Best Methods)

Closing Thoughts

That’s all folks. These are the different methods to apply the accounting number format in Excel. If you have any questions regarding this or any other Excel feature, please let us know in the comments. We are always happy to help. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!