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**

Contents

## 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 [Color**X**], 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

**About the author**

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

**Do you need help adapting this post to your needs?**

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

**What next?**

Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: