Excel number formats for accounting & finance you NEED to know

Excel’s default number formats don’t really work for accounting and finance reports. There is no way any accountant in their right mind would use the Accounting format. Instead, we must develop our own custom number formats with the brackets, decimals, and alignment in exactly the right place. So, I want to share with you the number formats I use. Then, you too can apply the perfect Excel number formats for accounting & finance in your reports.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube

Why Excel’s accounting number format is rubbish

To apply Excel’s default Accounting number format, select the cells, then click the Accounting format from the drop-down in the Number section of the Home ribbon.

The format looks like this:

Unfortunately, there are many things wrong with this style:

• The currency symbol appears on every line. This is unnecessary, as we could add that in the column header.
• The negative symbol is to the far left. This is a long way from the number and could easily be missed.
• The negative symbol is a minus sign. In accounting, brackets/parentheses are more common for negative numbers.
• The dash for the zero appears to be below the decimal place. What’s that about? Surely it should either be below the last whole number or below the last decimal.

I’m sure we can do much better with our own number formats.

Understanding number formats

Let’s start by ensuring we have a basic understanding of Excel’s number formats and how they work.

Where to apply number formats

All the number formats in the drop-down menu are constructed from number format codes. As they are provided in a helpful list, we don’t see the formats which Excel applies in the background.

When we click the icons to change the currency symbol, percentage, add/remove commas or increase/decrease decimals, the changes are applied to the underlying number format.

Let’s open the Format Cells dialog box and look at the number formats. Click Home > Number (drop-down) > More Number Formats… (or press Ctrl + 1).

In the Number > Custom section, we find a list of number format codes.

In the Type box at the top, we can enter the code for our own number formats.

Number format codes

Number format codes define how Excel displays values in cells. There are four cell value types: positive, negative, zero, and text. Number format codes provide the style for each of these.

The codes are constructed of 4 sections, separated by a semi-colon ( ; ). The code in each section determines how the numbers are displayed.

We will look at the specific codes in the next section. However, before that, we need to understand how the sections work.

Section 1 ; Section 2 ; Section 3 ; Section 4

While there are 4 value types and 4 section codes, there is not a 1-to-1 relationship. Section 1 is required, Sections 2-4 are used where they are needed for specific value types.

	Positive	Negative	Zero	Text
Section 1 only	Section 1	Section 1	Section 1	Section 1
Sections 1 & 2	Section 1	Section 2	Section 1	Section 1
Sections 1, 2, & 3	Section 1	Section 2	Section 3	Section 1
Sections 1, 2, 3 & 4	Section 1	Section 2	Section 3	Section 4

The table above shows that Section 1 behaves as a default and is applied to all values, unless overridden by the code in a specific section.

Number formats for accounting and finance

Now let’s move on to look at the most useful Excel number formats for accounting and finance.

Base format

In this section, we look at the base number format. This is the start point for all customizations later in the post.

To display numbers for accounting and finance, there are specific conventions that we want to apply (see screenshot above; the grey dotted line was added to show alignment):

• Negative numbers are contained in brackets. ✅
• Numbers in equivalent positions are always shown above/below each other. Therefore, any brackets should be outside of the alignment. ✅
• A dash used for zero is below the whole number (this is my preference). ✅
• Numbers only be shown to the required level of precision (we’ll see how to adjust the precision later in this post). ✅

To apply this number format, enter the following into the Type box, then click OK.

#,##0_);(#,##0);-_);@_)

What do the icons in the code mean?

• 0 (Zero) : A forced digit. Even if there is nothing to display, a zero is always visible.
• # (Hash / Pound) : An unforced digit. A number is displayed only if there is a number in that position
• , (Comma) : The separator for thousands, millions, billions, etc. This may be a period depending on your local settings.
• _ (Underscore) : The space modifier. Creates white space the equivalent size of the character directly after. Therefore _) creates white space the size of a close bracket.
• ( ) (Brackets / Parentheses) : Text values forced into the code at the appropriate location. We use this to represent negative numbers.
• – (Dash) : A text value forced into the code at the appropriate location. We use this to represent zero.
• @ (at symbol) : Represents any text value.

Let’s breakdown this code to improve our understanding:

Section 1: #,##0_)

• The default format includes at least one visible digit
• Other digits are displayed with thousand separators where necessary
• White space the size of a close bracket is included on the right

Section 2: (#,##0)

• Negative numbers include at least one visible digit
• Other digits are displayed with thousand separators where necessary
• Opening and closing brackets are displayed around the value

Section 3: -_)

• Zero values display a dash character
• White space the size of a close bracket is included on the right

Section 4: @_)

• Displays any text
• White space the size of a close bracket is included on the right

Decimal places

To add decimal places to the base number format, we add the period ( . ) character (This may be a comma depending on your region settings).

#,##0.00_);(#,##0.00);-_._0_0_);@_)

In the code above, we display 2 decimal places. The period is followed by zeros; therefore, these are forced even if there are no decimal values.

To ensure the zero dash is below the whole number, white space is added. _._0_0 is included in section 3 of the code to force white space the size of .00.

Thousands / Millions / Billions

To display numbers as thousands, millions, or billions, include thousand separators after the last 0 or # character.

The following displays numbers as millions.

#,##0.0,,_);(#,##0.0,,);-_._0_);@_)

Thousands require a single comma after the last digit, billions require 3.

Colors

Colors to help indicate positive and negative numbers.

#,##0.0,,_);[Red](#,##0.0,,);-_._0_);@_)

In the screenshot above, negative numbers are shown in red.

There are two ways to apply color.

• Default color names: Excel provides 8 color names [black], [white], [red], [green], [blue], [yellow], [magenta], [cyan].
• Color index codes: Use [ColorX], where X is a number from 1 to 56 to represent the color from the workbook’s color palette.

Adding currency symbol and units

For accounting and finance, where numbers in a column are the same unit of measure, we usually include units in the column header. However, a KPI table may consist of mixed measures; therefore, we may show a currency symbol and signify the size of an individual cell.

£#,##0.0,,\m_);(£#,##0.0,,\m);-_._0_m_);@_)

In the screenshot above:

• £ is added into the Positive and Negative formats for the currency symbol (change this for your currency symbol)
• \m is added into the Positive and Negative formats to show that numbers are in millions.
• For the zero number, we do not want to show an m character, but want to include white space the size of m. Therefore, we add _m.

Adding direction symbols

When looking at variance, using an arrow rather than brackets can present an easier understanding for the reader.

[Green]▲#,##0.0,,;[Red]▼#,##0.0,,;-_._0;@

The code above displays the up arrow for positive numbers and the down arrow for negative numbers. In addition, color has been added to provide more visual clues.

Conclusion

Excel contains a rich variety of number format codes. However, these are not always available as the defaults. Therefore, in this post, we covered the base number format code for accounting and finance professionals and the options to customize that code to your requirements.

Related Posts:

• Change number format based on a cells value
• Office Scripts – Reverse number signs
• Highlight specific rows in a table