How to Center Accounting Format in Excel – 2 Methods

This is an overview.

The sample dataset showcases employee names and their respective salary.

The salary amounts are in Accounting Number Format (ANF).

Method 1 – Using the Format Cells Dialog Box

• Select the cells to center the Accounting format. Here, C5:C12.
• Right-click the selected cells and choose Format Cells.
  Or, go to the Home tab > Font > Dialog Box Launcher ().
  Shortcut: Press CTRL + 1 to open the Format Cells dialog box.
  
• Go to the Number tab > Custom  > Type.
• Select the following type:_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
• Remove all the Asterisk symbols (*) in Type.
• Click OK to center align the Accounting format in the selected cells.

This is the output.

Method 2 – Using Excel VBA

Using the NumberFormat property of the Selection object, a VBA macro can be developed to apply center alignment to Accounting format.

• Open the Visual Basic Editor by clicking Visual Basic in the Developer tab.
  Shortcut: Press Alt + F11 to open the Visual Basic Editor.
  
• Click Insert > Module.
  Or, right-click any item in the project explorer on the left, and select Insert > Module.
  
• Enter the following codes in the module:

  Sub Center_Accounting_Format()
  Selection.NumberFormat = _
  "_(""$"" #,##0.00_);_(""$"" (#,##0.00);_(""$"" ""-""??_);_(@_)"
  End Sub

  Change $ in the code above to any other currency.

• Press Ctrl + S and save the workbook as Excel Macro-Enabled Workbook (*.xlsm).
  
• Select the cells to apply the center alignment of the Accounting format. Here, C5:C12.
• Run the VBA macro:
  • Press Alt + F8 to open the Macro dialog box.
    Or, in the Developer tab, click Macros.
  • Select Center_Accounting_Format in Macro name.
  • Click Run.

  

  Shortcut: You can also run the macro in the VB Editor. Select the cells, go back to the VB Editor, and press F5.

This is the output.

Download Practice Workbook

Download the practice workbook.

Frequently Asked Question

How do I get back to the default Accounting Number Format (ANF)?

To get back to the default Accounting Number Format (ANF) in Excel, select the cells or range to modify, right-click to open the context menu, and choose Format Cells. In the Format Cells dialog box, go to the Number tab > Accounting. Choose the Decimal places, select the currency in Symbol, and click OK.

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