How to Apply Accounting Number Format in Excel: A Step-by-Step Guide

If you want to display numbers as monetary values, you must format those numbers as currency. To do this, you apply either the Currency or Accounting number format to the cells that you want to format. The number formatting options are available on the Home tab, in the Number group.

Using the right number formats is essential for presenting financial data clearly in Excel. The accounting number format is designed specifically for monetary values, making it ideal for accounting and financial reports.

In this comprehensive guide, we’ll walk through the steps to apply accounting format to cells in Excel using different methods

What is Accounting Number Format in Excel?

The accounting number format aligns currency symbols and commas to provide better readability of figures. It also displays negative numbers in parentheses.

Here are the key features of Excel’s accounting format

Currency symbol aligned to the left
Decimal places for cents
Commas as thousands separators
Zeros displayed as dashes
Negative numbers wrapped in parentheses

This standardized format improves the look and interpretation of financial statements, avoiding errors in data analysis

Difference Between Accounting and Currency Format in Excel

Both accounting and currency formats handle money and financial data in Excel but have some differences:

Currency format shows the currency symbol before the number and aligns values to the right.
Accounting format aligns symbols to the left and encloses negatives in parentheses.

Accounting format is specialized for accounting and financial presentations. But you can use either format depending on your specific requirements.

Methods to Apply Accounting Number Format in Excel

Let’s go through some easy ways to format cells as accounting numbers in Excel:

1. Use the Accounting Button

This method takes just a few clicks:

Select the cells to format.
On the Home tab, click the Accounting button under Number.
Pick a currency from the dropdown.

The cells now show the accounting format.

2. Use the Number Format Dropdown

Another quick way is using the number format dropdown:

Select the cells to format.
On the Home tab, open the Number Format dropdown.
Choose Accounting.

The accounting format is applied.

3. Use the Format Cells Dialog

For more control, use the Format Cells dialog:

Select the cells and press Ctrl + 1 or right-click and select Format Cells.
Go to the Number tab and pick Accounting from Categories.
Customize decimal places and symbols if needed.

The dialog provides additional options beyond the basic accounting format.

4. Use Keyboard Shortcuts

You can use keyboard shortcuts to activate the accounting button:

Select the cells to format.
Press Alt + H + A + N and pick a currency.

The shortcut applies the accounting format.

5. Use the TEXT Function

To apply accounting format with a formula:

Insert a new column and enter the TEXT formula:

=TEXT(A2,"$#,##0.00")

Replace A2 with your data cell.

Copy the formula down the column.

The TEXT function converts values into the accounting format.

6. Use VBA Macro Code

For batch formatting, use a VBA macro:

Open VBA editor and insert a new module.
Paste this code in the module:

Sub FormatAccounting()   Range("A1:A10").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"End Sub

Run the macro to apply accounting format.

The macro allows formatting multiple cells together.

How to Remove Decimal Places from Accounting Format

The default accounting format shows two decimal places. To remove decimals:

Open Format Cells and select the Accounting category.
Set Decimal places to 0.
Click OK.

This applies accounting format with no decimal places.

How to Customize Accounting Format in Excel

To customize accounting format:

In Format Cells, choose Custom from Categories.
Enter a custom format code in Type:

For example:

$* #,##0_);($* #,##0)

Click OK to apply the custom accounting format.

You can tweak display options like currency symbols, decimal places, and negative numbers.

How to Apply Accounting Format to Only Selected Cells

To selectively format cells as accounting:

Hold down Ctrl and click cells to format.
Right click and choose Format Cells.
Select Accounting on the Number tab.
Click OK to apply.

This methods avoids formatting the entire column.

How to Remove Accounting Format in Excel

To revert cells back to the original format:

Select the cells with accounting format.
On the Home tab, click the Clear button.
Choose Clear Formats.

This removes only the accounting format while keeping cell contents intact.

Troubleshooting Accounting Format Issues in Excel

Here are solutions for common issues with accounting format:

Negative numbers not in parentheses? Use custom format $* #,##0.00_-;($* #,##0.00)` to enclose negatives.
Zeros not showing as dashes? Add a custom code like 0"-" in your accounting format.
Accounting format not working? Try reformatting the cells or restarting Excel. Ensure no other conflicting formats are applied.
Format changes unexpectedly? Number and decimal settings in your Windows Region settings can affect Excel’s accounting display.

Tips for Using Accounting Number Format in Excel

Here are some useful tips:

Add the accounting button on the Quick Access toolbar for quick access.
Use conditional formatting to highlight negatives in red.
Right-align currency symbols for a cleaner look.
Use accounting underline style for totals.
Match column widths to view full numbers.
Be consistent with formatting in financial statements.
Check both positive and negatives values.

Why Use Accounting Number Format in Excel?

There are several benefits to using accounting number format:

Prevents errors: The standardized format minimizes mistakes in data interpretation.
Improves readability: The left-aligned currency symbols and negative parentheses increase clarity.
Adds professional look: Accounting format gives financial statements a polished, formal appearance.
Enhances analysis: Proper data formatting facilitates better analysis and calculations.
Simplifies sharing: Using accounting format ensures consistency when sharing reports with stakeholders unfamiliar with the data.

Examples of Accounting Format in Excel

Here are a few examples of accounting format applied in Excel:

Financial statements like income statements and balance sheets
Expense reports and cash flow reports
Budget tables listing expenses and revenues
Profitability comparisons and ratio analyses
Inventory value summaries
Depreciation schedules
Pricing list of products and services
Invoice and billing statements

Limitations of Excel Accounting Format

While useful, Excel’s accounting format has some limitations:

Limited customization compared to dedicated accounting software
Requires manual reformatting when pasting new data
Cannot dynamically link cells between worksheets
Does not support additional currency options like Euro
Lacks double underline formatting for reporting standards
Unable to assign format to a cell style for quick reuse

For more complex reporting needs, integrating Excel with accounting systems can help overcome these limitations.

Applying the right accounting number format is crucial for clearly presenting financial figures in Excel. This guide covered simple ways to format cells as accounting numbers using the ribbon, keyboard shortcuts, custom formulas and VBA macros. feel free to tweak the settings to customize the format. Proper use of Excel’s accounting format ensures your financial statements and reports look polished and professional.

how to apply accounting number format in excel

Format numbers as currency

You can display a number with the default currency symbol by selecting the cell or range of cells, and then clicking Accounting Number Format Accounting number format button in the Number group on the Home tab. (If you want to apply the Currency format instead, select the cells, and press Ctrl+Shift+$.)

If you want more control over either format, or you want to change other aspects of formatting for your selection, you can follow these steps.

Select the cells you want to format

On the Home tab, click the Dialog Box Launcher next to Number.

Tip: You can also press Ctrl+1 to open the Format Cells dialog box.

In the Format Cells dialog box, in the Category list, click Currency or Accounting.

In the Symbol box, click the currency symbol that you want.

Note: If you want to display a monetary value without a currency symbol, you can click None.

In the Decimal places box, enter the number of decimal places that you want for the number. For example, to display $138,691 instead of $138,690.63 in the cell, enter 0 in the Decimal places box.

As you make changes, watch the number in the Sample box. It shows you how changing the decimal places will affect the display of a number.

In the Negative numbers box, select the display style you want to use for negative numbers. If you dont want the existing options for displaying negative numbers, you can create your own number format. For more information about creating custom formats, see Create or delete a custom number format.

Note: The Negative numbers box is not available for the Accounting number format. Thats because it is standard accounting practice to show negative numbers in parentheses.

To close the Format Cells dialog box, click OK.

If Excel displays ##### in a cell after you apply currency formatting to your data, the cell probably isnt wide enough to display the data. To expand the column width, double-click the right boundary of the column that contains the cells with the ##### error. This automatically resizes the column to fit the number. You can also drag the right boundary until the columns are the size that you want.

If you want to remove the currency formatting, you can follow these steps to reset the number format.

Select the cells that have currency formatting.
On the Home tab, in the Number group, click General. Cells that are formatted with the General format do not have a specific number format.

Create a workbook template with specific currency formatting settings

If you often use currency formatting in your workbooks, you can save time by creating a workbook that includes specific currency formatting settings, and then saving that workbook as a template. You can then use this template to create other workbooks.

Create a workbook.

Select the worksheet or worksheets for which you want to change the default number formatting. How to select worksheets

To select	Do this
A single sheet	Click the sheet tab. If you dont see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab.
Two or more adjacent sheets	Click the tab for the first sheet. Then hold down Shift while you click the tab for the last sheet that you want to select.
Two or more nonadjacent sheets	Click the tab for the first sheet. Then hold down Ctrl while you click the tabs of the other sheets that you want to select.
All sheets in a workbook	Right-click a sheet tab, and then click Select All Sheets on the shortcut menu.

Tip When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets.

Select the specific cells or columns you want to format, and then apply currency formatting to them.

Make any other customizations that you like to the workbook, and then do the following to save it as a template:

How to Apply the Accounting Number Format in Excel

How do I format accounting numbers in Excel?

To apply the Accounting number format to your cells using the Format cells dialog box, follow the steps below: Select the cells you want to format. Right-click on your selection. From the context menu that appears, select the ‘Format cells ’ option. This will open the Format cells dialog box. Under ‘Categories’, select the ‘Accounting’ option.

How do I apply the accounting number format?

You can follow these steps to apply the accounting number format from the Home tab. Select the range of values to which you want to apply the accounting format. Click on the format dropdown menu found in the Number section of the ribbon. Choose the Accounting option. Your selected numbers will now appear in the accounting format!

How do I use accounting numbers in Excel?

Excel has an option in its ribbon to help you quickly use the accounting number format in your spreadsheets. To use it, first, open your spreadsheet with Microsoft Excel. In your spreadsheet, select the cells containing the numbers that you want to turn into accounting numbers. In Excel’s ribbon at the top, click the “Home” tab.

How to format a number in Excel?

Right-click on the selected cells, and from the context menu, choose Format Cells. Select Number tab > Category list > Accounting. Customize the number of Decimal places and Symbol field (if needed). Click OK to apply your number formatting. While working with financial data, learning the methods of number formatting in Excel is a crucial skill.