Do you work in finance and need to apply the accounting number format to your data?

This post is going to show you how to add the accounting format to your numbers in Excel!

Excel offers users a variety of number formatting options. This is because there are many types of numbers such as dates, percentages, or currencies. Different number formatting allows you to present the data appropriately.

The accounting format is appropriate to use with currency values and has several great features that make comparing numbers super easy.

• Zeros are shown as a dash. This makes 0 value very easy to spot.
• A currency symbol such as $ is added.
• The currency symbol is aligned to the left of the cell.
• The numbers are aligned to the right of the cell.
• The currency symbol and decimal are aligned across a column.

Get your copy of the example workbook used in this post to follow along.

Add Accounting Format from the Home Tab

The accounting number format is a very commonly used format. Because of this, it is made easily accessible from the ribbon commands. You can find this in the Home tab.

You can follow these steps to apply the accounting number format from the Home tab.

1. Select the range of values to which you want to apply the accounting format.

2. Go to the Home tab.
3. Click on the format dropdown menu found in the Number section of the ribbon.

4. Choose the Accounting option.

Your selected numbers will now appear in the accounting format!

Add Accounting Format from Format Cells Menu

If you are applying any sort of format in Excel, you should familiarize yourself with the Format Cell menu.

This is where you will be able to access any type of cell format such as borders, fill color, font color, etc. You will also find all the available number formatting options here.

Follow these steps to apply the accounting format with the Format Cells menu.

1. Select the range of numbers in your sheet.
2. Open the Format Cells menu. There are a couple of ways to do this.
   • Right-click and choose Formal Cells.
   • Press the Ctrl + 1 keyboard shortcut.
   • Go to the Home tab and click on the Launch icon in the lower right corner of the Numbers section.
3. Go to the Numbers tab in the Format Cells menu.
4. Select the Accounting option in the Category list.
5. Choose the number of Decimal places to include and the currency Symbol to display.
6. Press the OK button.

Your numbers are now formatted with the accounting format!

📝 Note: Using the Format Cells menu gives you the option to select other currency symbols that aren’t available in the Home tab.

Add Accounting Format from the Quick Access Toolbar

If you work in the accounting or financial industry, then you might be using the accounting format constantly.

Having this easily accessible will save you some significant time!

You can add the format drop-down selector to the Quick Access Toolbar, and this way it will be available wherever you are in the ribbon.

Right-click on the dropdown button and select Add to Quick Access Toolbar from the options.

This will add a small dropdown list into the Quick Access Toolbar area and you will be able to use this anytime!

Add Accounting Format with a Keyboard Shortcut

There is no dedicated keyboard shortcut for applying the accounting format in Excel.

But you can use the Alt hotkeys to access the format from the Home tab ribbon.

Press the sequence Alt, H, N, A. This will select the Accounting format from the dropdown in the Home tab. Then you will need to press the Enter key to accept the selection.

Add Accounting Format with a Custom Format

Another option you can use is a Custom Format to create your own accounting format.

Why create a custom format when there is already an accounting format?

The accounting format doesn’t allow you to show negative numbers with a red font or inside parentheses. Since there is only a small dash character before the currency symbol, negative numbers can be easy to miss.

Follow these steps to create a custom accounting format with red negative numbers inside parentheses.

1. Select the numbers to format.
2. Press the Ctrl + 1 keyboard shortcut to open the Format Cells menu.
3. Go to the Numbers tab.
4. Select the Custom option in the Category list.

_ $* #,##0.00 _ ;[Red] $* (#,##0.00)_ ;_ $* "-"?? _ ;_ @_ 

5. Paste the above format string into the Type input.
6. Press the OK button.

⚠️ Warning: The custom format string has a space character at the end and this needs to be included!

positive;negative;zero;text

The format syntax consists of 4 parts separated by a ; character. They determine what the positive, negative, zero, and any text values will look like.

Each part contains an asterisks character followed by a space $* . The asterisk is a repeater character and it means the space after it will be repeated to fill the cell.

You’ll also notice the [red] prefix has been added to the negative section. This will result in any negative values displaying in red font.

The ?? in the zero section ensures the dash character - will line up with any decimals in other cells.

This results in a custom accounting format where the negative numbers are immediately obvious!

If you look very closely at the results, you will see the decimal points don’t line up exactly across the column though.

This is because the positive part of the format string uses a space as a placeholder while the negative part uses a closing parentheses ). These characters are slightly different sizes and result in misalignment!

This can be fixed by using a monospaced font such as Courier New. This ensures each character is the same width and everything will line up nicely across the column.

Select the numbers and go to the Home tab and select Courier New from the dropdown in the Font section.

Add Accounting Format with the TEXT Function

Suppose you want to cast your numbers to text values and include the accounting format. This is possible using the TEXT function.

The TEXT function uses the same format string syntax to convert a number to a formatted text value.

The process will be more complicated than the custom format though as the TEXT function doesn’t allow the use of the asterisks * to repeat characters. This means you will have to calculate how many spaces to insert for each number to format!

=LET(
    maxlength, 15,
    text, TEXT(D3," $ #,##0.00__ ; $ (#,##0.00)_ ; $ ""-    """),
    length, LEN(text),
    diff, MAX(0,maxlength-length),
    newtext, SUBSTITUTE(text,"$ ","$"&REPT(" ",diff)),
    newtext
)

The above formula will convert the number in cell D3 to a text value in accounting format where the negative numbers are in parentheses.

This will also require that you change to a monospaced font such as Courier New to get the decimal points to line up across the column.

This formula will calculate the length of the formatted number with a single space and then determine how many extra spaces to insert to make sure there are a total of 15 characters.

You will need to update the maxlength, 15, section of the formula to accommodate formatting larger numbers.

📝 Note: Using the [red] prefix in the format syntax is not possible in the TEXT function. But you could use conditional formatting to achieve this same effect.

Conclusions

Account format is a common number format used across the financial industry to help make the numbers easier to read.

There are several ways to add the accounting format to your selected numbers. The Home tab or Format Cells menu will be the most straightforward way.

But if you need to use the accounting format with negative values in parentheses, then you will need to create a Custom Fformat or use the TEXT function.

Do you use the accounting format for your numbers? Do you know any other ways to apply this format? Let me know in the comments below!