How to Apply Accounting Number Format in Excel (6 Effective Ways)

  1. Select the cells that you want to format.
  2. Right-click on the selected cells, and from the context menu, choose Format Cells.
  3. Select Number tab > Category list > Accounting.
  4. Customize the number of Decimal places and Symbol field (if needed).
  5. Click OK to apply your number formatting.

Get FREE Advanced Excel Exercises with Solutions!

While working with financial data, learning the methods of number formatting in Excel is a crucial skill. Among the various formatting options, the Accounting Number Format (ANF) is favored by Excel users for its ability to present numbers in a professional and standardized manner, commonly used in financial statements and reports. Understanding how to apply the Accounting Number Format (ANF) will make financial data more readable and contribute to a satisfactory and consistent look for financial documents.

In this Excel tutorial, six effective ways are discussed with easy steps to apply the Accounting Number Format (ANF) in Excel, helping you simplify your financial data presentation and enhance the clarity of your spreadsheets. From ribbon-based navigation in Number Group, Number Format dropdown, Format Cells dialog box, the TEXT function, and Keyboard Shortcut, to advanced VBA Macro automation, this article explores diverse approaches to apply the Accounting Number Format.

The following image provides a clear overview of the purpose of this Excel tutorial. The right side of the image presents the result of the methods discussed in this article to apply the Accounting Number Format (ANF) in Excel.


What is Accounting Number Format (ANF)?

Accounting Number Format (ANF) in Excel is a specific formatting option that makes numbers or monetary values easier to read for accounting and financial purposes. It adds dollar signs ($), commas for thousands, and parentheses for negative values. This formatting helps prevent errors and makes the financial data easier to interpret.

Difference Between Accounting Number Format and Currency Format in Excel: For money and currency-related cell values, Excel usually offers two types of number formatting: Currency format and Accounting Number Format (ANF). Both types are almost similar and have some common features: show a currency symbol (usually a “$” sign by default), two decimal points, and comma separators. The uniqueness of the accounting number format is that it puts the dollar sign and aligns the number perfectly in a column.

Plus, the Currency Format displays the currency symbol before the number, right-aligning figures, and allows for customization of decimal places. In contrast, the Accounting format aligns the currency symbol at the leftmost position for a neat presentation, uses parentheses for negative numbers, and is specifically featured for financial documents, offering a standardized and professional appearance.

While both Currency and Accounting formats are suitable for financial presentations in Excel, the Accounting format offers additional features for accounting and financial statement purposes, providing a more structured and polished appearance. The choice between the two depends on the specific requirements of your data presentation and the visual style you aim to achieve.


6 Ways to Apply Accounting Number Format (ANF) in Excel

The methods of applying Accounting Number Format (ANF) in Excel involve some easy and straightforward steps. In this comprehensive Excel guide, six effective methods to apply the Accounting Number Format in Excel will be explored with illustrative images.

To better understand the steps of the diverse approaches, from ribbon-based navigation to advanced macro automation, let’s assume the following sample data named as Dataset of Fixed Asset. The sample data includes simple two columns with the headers Asset Type and Amount.
Sample Dataset
Take a slight view of the above data. Remember that, the purpose of this article is to apply the Accounting Number Format to the Amount column. Below, the six effective ways to apply the Accounting Number Format (ANF) in Excel are discussed in detail.

An important disclaimer to mention, this tutorial uses Microsoft Excel 365 for presenting the methods to apply Single Accounting Underline format, other Excel versions are also compatible to adopt the methods discussed here. If any method is found not working appropriately, feel free to share it in the comment section at the end of the article.


Using Number Group

By using the Number Group of the Home tab, you can quickly apply the Accounting Number Format to your selected data in Excel.

Follow the steps below to apply the Accounting number format using the Number group in Excel:

  1. Select the cells or range of cells that you want to format.
    Here, the selected cells for formatting are C5:C10.
    Apply ANF Using Number Group
  2. Go to the Home tab in the Excel ribbon at the top of the window.
  3. Click on the Accounting Number Format symbol () within the Number group.
    Apply ANF Using Number Group

The above steps will convert the formats of the selected cells into Accounting Number Format (ANF), making them suitable for displaying financial data in a clear and organized manner.Apply ANF Using Number Group


Using Number Format Dropdown

The Number Format dropdown menu provides an easy way to apply various number formats, including the Accounting number format, to your data in Excel.

Follow the steps below to apply Accounting Number Format (ANF) in Excel using the Number Format dropdown from Excel Ribbon:

  1. Select the cells or range of cells that you want to format.
    Here, the selected cells for formatting are C5:C10.
  2. Go to the Home tab in the Excel ribbon at the top of the window.
  3. Find the Number Format dropdown.
  4. Select Accounting from the category list.Apply ANF Using Number Drop-down

The above steps will change the formats of the selected cells into Accounting Number Format (ANF), making them suitable for displaying financial data in a clear and organized manner.Apply ANF Using Number Drop-down


Using Format Cells Dialog Box

The Format Cells dialog box in Excel enables control over the formatting options, allowing you to adjust the Accounting Number Format to your specific requirements.

Follow the steps below to apply Accounting Number Format (ANF) in Excel using the Format Cells dialog box:

  1. Select the cells that you want to format.
    In our sample data, the cells selected for formatting are C5:C10.
  2. Right-click on the selected cells, and from the context menu, choose Format Cells.
    Shortcut: Press CTRL + 1 to directly open the Format Cells dialog box.Apply ANF Using Format Cells
    The Format Cells dialog box will appear accordingly.
  3. Click on the Number tab, and select Accounting from the Category list.
  4. Customize the number of Decimal places and Symbol field, if needed.
  5. Click OK to apply your number formatting.
    Apply ANF Using Format Cells

The above steps will convert the cell formats of the selected cells into Accounting Number Format (ANF), making them suitable for displaying financial data in a clear and organized manner.Apply ANF Using Format Cells

The Format Cells dialog box is particularly useful when you need to make detailed adjustments beyond the standard formatting options available in the ribbon or drop-down menus.


Using Keyboard Shortcuts

Basically, there is no dedicated keyboard shortcut for applying the Accounting Number Format (ANF) in Excel. So, the keyboard shortcuts to apply Accounting Number Format (ANF) directly activates the Accounting Number Format button () on the Home tab of the ribbon.

Follow the steps below to apply Accounting Number Format (ANF) in Excel using keyboard shortcuts:

  1. Select the cells that you want to format.
    Here, the cells selected for formatting are C5:C10.
  2. Press ALT + H + A + N from the keyboard.
    The most used currency list will pop up.
    Apply ANF Using Keyboard Shortcut
  3. Press ENTER after confirming your currency choice.

The cell formats of the selected cells will be converted into Accounting Number Format (ANF). Apply ANF Using Keyboard Shortcut


Note:
Remember that, this keyboard shortcut (ALT + H + A + N) only changes currency format for the selected cells, if your Home tab is activated. Don’t confuse it with the shortcuts like Alt + H + N + A or Alt + H + A + R. The Alt + H + N + A shortcut opens the Data tab while Alt + H + A + R shortcut aligns cell values to the right.


Using TEXT Function

Follow the steps below to apply Accounting Number Format (ANF) in Excel using the TEXT function:

  1. Insert a new column to apply the Accounting number format.
    Here, the new column is Amount (ANF) where Accounting Number Format will be applied to the Amount column.Apply ANF Using TEXT Function
  2. Select the first cell in the new column where you want to apply the Accounting number format.
    Here, D5 is the first cell in the Amount (ANF) column where Accounting number format will be applied.
  3. Insert the following formula:=TEXT(C5, "$#,##0.00")
    Apply ANF Using TEXT FunctionChange cell reference (C5) as per your dataset. Press Enter to apply the formula.
  4. Click and drag the Fill Handle down to apply the formula to the entire column.

The above steps will change the formats of the selected cells into Accounting Number Format (ANF), making them suitable for displaying financial data in a clear and organized manner.Apply ANF Using TEXT Function


Using VBA Macro

Follow the steps below to apply Accounting Number Format (ANF) in Excel using VBA macro:

  1. Open the Visual Basic for Applications (VBA) editor by clicking on Visual Basic tool from the Developer tab.
    Shortcut: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor directly.
    Apply ANF Using VBA Macro
  2. Insert a new module.
    To insert a new module, Click on Insert > Module. Or, right-click on any item in the project explorer on the left, and select Insert > Module.Apply ANF Using VBA Macro
  3. Copy and paste the following codes into the module.
    Sub ApplyAccountingNumberFormatToColumn()
    
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Using VBA Macro")
    ' Change "Using VBA Macro" to your sheet name
    
    ' Set the range to the entire column A
    Set rng = ws.Range("C5:C10")
    
    ' Apply Accounting Number Format to the range
    rng.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"
    
    End Sub
  4. Close the VBA editor.
  5. Run the macro. Or, Assign the created macro (ApplyAccountingNumberFormatToColumn) to a button or shortcut.
    To run the macro, follow the steps below:
      • Press Alt + F8 to open the Macro dialog box.
      • Select ApplyAccountingNumberFormatToColumn from the list.
      • Click Run.
        Shortcut: Before closing the VBA editor, you can also run the macro by pressing F5, a function key of the keyboard.

    Apply ANF Using VBA Macro

That’s all. The above steps will convert the cell formats of the specified cells into Accounting Number Format (ANF), making them suitable for displaying financial data. Apply ANF Using VBA Macro

This VBA macro will apply the Accounting number format to the range C5:C10 in the specified worksheet. Make sure to change “Sheet1” to the name of your actual sheet if it’s different. Also, change the range (“C5:C10”), if you need to apply Accounting number format to a different range.


How to Apply Accounting Number Format with No Decimal Places

The application of Accounting Number Format (ANF) automatically adds two decimal places to the numbers. After applying Accounting number format, you can use the Decrease Decimal button () twice to remove decimal places. Or, follow the steps below to remove decimal places in Accounting Number Format:

  1. Select the cells you want to format.
  2. Right-click on the selected cells.
  3. Choose Format Cells from the context menu.
    The Format Cells dialog box will appear.
  4. Go to the Number tab, and select Accounting from the Category list.
  5. Set the Decimal places field to 0.
    Or, you can choose any other desired decimal points and currency symbols.How to Apply Accounting Number Format with No Decimal Places
  6. Click OK to apply the Accounting number format with no decimal places to the selected cells.

This manual process allows you to remove decimal places in Accounting number format and customize the Accounting number format according to your specific requirements.


How to Customize the Application of Accounting Number Format (ANF) to Meet Your Requirements

Accounting Number Format (ANF) in Excel can be customized to meet specific requirements and preferences of a user. To customize Accounting Number Format (ANF) in Excel, select Custom from the Category list of Format Cells dialog box. You can select or enter any desired Accounting format code in the Type field. The format code determines how the cell contents will be displayed, and there are many options to choose from.Customizing the Application of Accounting Number Format (ANF) to Meet Your Requirements

Some common format codes used in Accounting Number Format (ANF) include adding currency symbols, decimal places, and negative number formatting. For example, to display negative numbers in parentheses, you can use the format code: "($#,##0.00);($#,##0.00)" Moreover, conditional formatting enables the application of distinct ANF formats according to specific criteria, such as highlighting cells containing negative values in red. By customizing ANF in Excel, you can ensure that your financial data is presented clearly and professionally.


Download Practice Workbook

Download this practice workbook to better understand while reading or exercising this tutorial.


Conclusion

In conclusion, the Accounting Number Format (ANF) in Excel is a crucial skill for working with financial data, as it presents numbers in a professional and standardized manner. There are six effective ways to apply the Accounting Number Format (ANF) in Excel, including using the Number group, Number Format dropdown, Format Cells dialog box, keyboard shortcuts, TEXT function, and VBA macro. Customization options are also available to meet specific formatting requirements. Use these methods in your dataset and feel free to reach out in the comments for any questions, corrections, comments, or technical assistance.


Frequently Asked Questions

How to apply Accounting Number Format to the selected cells?

From the perspective of this article, using the format cell dialog box is a more practical way to apply the Accounting Number Format to the selected cells. To apply the Accounting Number Format to the selected cells, select the specific cells holding the CTRL key and right-click on any of the selected cells. Choose Format Cells > Number > Accounting > OK. You can choose your desired requirements from the Decimal Places box and Symbol list. Or, if you frequently need to perform this task, you might consider using the Accounting format button (Accounting Number Format Button), as mentioned in previous examples.

How to simultaneously apply Accounting Number Format in Excel?

To simultaneously apply the Accounting Number Format to multiple cells or a range of cells in Excel, select the specific cells holding the CTRL key and right-click on any of the selected cells. Choose Format Cells > Number > Accounting > OK. You can choose your desired requirements from the Decimal Places box and Symbol list. You can choose your desired requirements from the Decimal Places box and Symbol list. Or, if you frequently need to perform this task, you might consider using the Accounting format button (Accounting Number Format Button), as mentioned in previous examples.

How to fix negative numbers in parenthesis while Accounting Number Format is applied?

Excel provides you with the flexibility to showcase negative numbers either within parentheses or with a minus sign, based on your personal preference. If you face any issues regarding the proper display of negative numbers, you can customize the formatting options by selecting Custom from the Category list in the Format Cells dialog box.  To remove parentheses from negative numbers, copy and paste the format code: ($* #,##0.00_);$* "-"#,##0.00; $* "-"??_);_(@_)
This format code instructs Excel to display negative numbers without parentheses and with a minus sign.

How to add Accounting format button to Quick Access Toolbar?

To add Accounting format button to the Quick Access Toolbar, right-click on the Accounting format button (Accounting Number Format Button)  from the Home tab. Click on Add to Quick Access Toolbar. This allows quick access to your formatting option without navigating through multiple steps. Also, you can move it to the first position in the toolbar.

How do I clear Accounting Number Format in Excel?

To remove Accounting Number Format from the selected cells in Excel, select the cell or range of cells from which you want to clear formatting. Click on Home tab >  Styles group > Clear button. From the drop-down menu, choose Clear Formats.


<< Go Back to Accounting Number Format | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

1 Comment
  1. Excellent tutorial. Thanks for sharing the valuable content.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo