How to Center Accounting Format in Excel (2 Easy Methods)

In the precise world of Excel, where accuracy is essential, aligning and displaying financial data in an Accounting Number Format (ANF) is a vital skill. In some cases, centering Accounting format is used to change the visual consistency of spreadsheets and to give financial documents a refined appearance.

In this Excel tutorial, the steps to effortlessly center Accounting Number Formats (ANF) in Excel are explained with two methods and their illustrative images. To enhance the clarity and effectiveness of their numerical presentations, this article presents the use of Format Cells dialog box and a VBA macro to center Accounting format in Excel.

Take a moment to view the following image. The image gives an overview of this Excel tutorial. On the right side of the image, you can see the outcome of the methods explained in this article for centering the Accounting format to the chosen cells of the left side.

Remember that, centering the Accounting format is not a preferred formatting option because the Accounting number format is traditionally designed for right-aligned numbers, ensuring consistency and ease of comparison. Opting for the center alignment in the Accounting number format reduces readability and does not comply with established conventions, hampering clear and standardized presentation of financial data. Financial professionals and analysts hardly rely on the center Accounting format as it lacks visual consistency and hampers effective communication of financial information.

2 Methods to Center Accounting Format in Excel

For a better understanding of the steps of each method, assume the following sample data named as Employee Data. The sample data have some employee names in the Employee column and their respective salary in the Salary column. The salary amounts are in the Accounting Number Formats (ANF). Take a short view of the above sample data. Remember that, the purpose of this article is to center the Accounting format to the amounts of Salary column in Excel. Below, two methods to center Accounting format in Excel are explained.

Using Format Cells Dialog Box

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

Follow the steps below to center the Accounting format in Excel using the Format Cells dialog box:

1. Select the cells where you want to center the Accounting format.
   In our sample data, the cells selected for formatting are C5:C12.
2. Right-click on the selected cells, and from the context menu, choose Format Cells.
   Or, use the Home tab > Font group > Dialog Box Launcher button ().
   Shortcut: Press CTRL + 1 (from the keyboard, not from the numeric keypad) to directly open the Format Cells dialog box.
   The Format Cells dialog box will appear.
3. Navigate to the Number tab > Custom category > Type dropdown.
4. Select the following type:_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
5. Remove all the Asterisk symbols (*) from the Type field.
6. Click OK to center align the Accounting format to the selected cells.

Following the above steps will change the alignment formats of the selected cells (Salary column) into a center-aligned Accounting format.

Note: No dedicated keyboard shortcut exists for applying center alignment to Accounting format in Excel. However, there exists a Center option in the Alignment group of the Home tab of Excel, which does not apply to the Accounting Number Format (ANF). Or, to apply a center format to any other cells, select your data ranges and press Alt + H + AC sequentially to apply a center alignment format (not the center-aligned Accounting format).
The functionality of keyboard shortcuts may vary among Excel Versions. This tutorial uses Excel 365 for all of its purposes.

Using Excel VBA

Using Excel VBA (Visual Basic for Applications) for applying the center alignment of Accounting format not only simplifies repetitive formatting tasks to ensure consistency but also reduces the risk of errors, making it a valuable tool for users handling large datasets or regularly updating financial reports.

Using the NumberFormat property of the Selection object, a VBA macro can be developed to apply center alignment of Accounting format to a specified range of cells in Excel. Follow the steps below to apply center alignment to Accounting format in Excel using VBA macro:

1. Open the Visual Basic Editor by clicking on Visual Basic tool from the Developer tab.
   Shortcut: Press Alt + F11 to open the Visual Basic Editor directly.
   
2. 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.
   
3. Copy and paste the following codes into the module:

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

   Change $ from the code above as per your currency choice.

4. Press Ctrl + S and save the workbook as Excel Macro-Enabled Workbook (*.xlsm).
   
5. Select the cells where you want to apply the center alignment of Accounting format.
   Here, the selected data range is C5:C12.
6. Run the VBA macro.
   To run the macro, follow the steps below:
   • Press Alt + F8 to open the Macro dialog box.
     Or, from the Developer tab, click on Macros.
   • Select Center_Accounting_Format from the list of Macro name.
   • Click Run.

   

   Shortcut: You can also run the macro from the VB Editor. To do so, select your cells; return to the VB Editor, and press F5, a function key of your keyboard.

Following the above steps will change the alignment formats of the selected cells (Salary column) into center alignment of Accounting format. You can change the macro name (Center_Accounting_Format) from the given code as per your requirement.

Download Practice Workbook

Download this practice workbook to better understand while reading or exercising this tutorial.
After downloading the file, don’t forget to Unblock the properties of the file and click on Enable Editing after opening the file.

Conclusion

In this Excel tutorial, the main point is that aligning and displaying financial data in an Accounting Number Format (ANF) is important for accuracy and a professional appearance. The tutorial provides two methods, using the Format Cells dialog box and Excel VBA, to center the Accounting Number Format in Excel. Use these methods in your real dataset and feel free to reach out to us in the comments section for any questions, corrections, comments, or technical assistance.

Frequently Asked Question

What are the potential challenges faced when centering Accounting formats?

The center alignment of Accounting format, with its center-aligned currency symbols and decimal points, faces the challenges of reduced consistency and readability, conveys a lack of professionalism, decreased precision and comparison, increased complexity of analysis, and does not comply with industry standards and regulations.

Is there a keyboard shortcut for quickly centering Accounting format in Excel?

No, there exists no dedicated keyboard shortcut for applying center alignment of Accounting format in Excel. However, there exists a Center option in the Alignment group of the Home tab of Excel, which is not applicable to the Accounting Number Format. Or, to apply a center format to any other cells, select your data ranges and press Alt + H + AC sequentially to apply a center alignment format (not the center alignment of Accounting format).

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 you want to modify, right-click to open the context menu, and choose Format Cells. In the Format Cells dialog box, navigate to the Number tab > Accounting Category. Choose your Decimal places, select currency in Symbol, and Click OK to apply the default Accounting Number Format (ANF), removing any specific alignment formatting previously applied.

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