Search Not Just Numbers

Tuesday 13 October 2015

Excel Tip: Show negative numbers in brackets

Working with accountants, one of the requirements I often get asked for, is to show negative numbers in brackets.

Surprisingly, this is not one of the standard number formats in Excel, not even if you choose the Accounting format!

Fortunately, however, this can be remedied using a custom number format.

It is usually easier to start with a format that is close to what you want. We will start with the Accounting format, to 2 decimal places, and no currency symbol.

To select this, right-click the cell, choose Format Cells and, on the Number Format tab, selecting Accounting. In the information to the right, select 2 decimal places and None (for the currency symbol).

Click OK to store this as the number format for that cell (or the range of cells selected).

Now follow the same sequence again, but this time choose Custom as the Number Format. You will see the code for the existing format (as previously selected) in the box at the top of the section on the right:

_-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-

We will edit this code to give us our brackets.

The first thing that we need to be aware of is the different sections of the code. This format uses the maximum four sections, each section being separated by semicolons.

The sections are as follows:

  1. Positive Numbers
  2. Negative Numbers
  3. Zero
  4. Text
It is not necessary to have all of these.  Any sections not included will follow the formatting set in section 1.

In this case, however, we have all four sections, but we are only interested in changing the first three - in particular Section 2 for negative numbers.

From above, we can see that the current formatting for negative numbers as follows:

-* #,##0.00_-

The first character is simply the minus sign.

This is followed by an asterisk (*) and a space. The asterisk tells Excel to repeat the character that follows it, to fill the remainder of the cell. As this is followed by a space, this tells Excel to pad out the area between the minus sign and the number with spaces, so that the whole cell is occupied. This is why the minus sign is shown on the far left in the Accounting format.

The #,##0.00 tells Excel that we want to see the number to two decimal places, with commas to mark thousands.

Finally, the _- creates a space the width of the minus sign. The underscore (_) symbol, inserts a space the width of the character that follows it.

To edit this to show our brackets, we can do the following:
  • Delete the leading - sign, as the brackets will denote the negative
  • Place an opening bracket immediately before the number, assuming that we want the opening bracket to be placed after the leading spaces
  • Replace the _- with a closing bracket. We no longer need the minus width space at the end, but we do want a closing bracket here
The new negative format should now look like this:

* (#,##0.00)

This is not the end of the story, however. We want to also slightly tweak the positive and zero sections, so that they line up correctly with the bracketed negatives.

The positive section currently looks like this:

_-* #,##0.00_-

We need to ensure that we have a space the width of a bracket in the same places that we have the brackets in the negative number. We also don't need the space the width of a - at the start and end, as these were removed from the negative.

The new positive format looks like this:

* _(#,##0.00_)

Finally, we need to make the same changes to the zero format which currently looks like this:

_-* "-"??_-

But, applying these changes, becomes:

* _("-"??_)

So, the complete new number format is:

* _(#,##0.00_);* (#,##0.00);* _("-"??_);_-@_-

We could trim this down even further if we like.

We don't really require the leading spaces, as there is nothing to the left of them, so we could remover the Asterisk and the space at the start of the positive, negative and zero sections.

We also, don't really need the text section, as this was only adding the leading space to coincide with the minus on the negative numbers, as well as the trailing space that was against all of the numbers.

Our streamlined format now looks like this:

_(#,##0.00_);(#,##0.00);_("-"??_)

And if you don't care how we got here, you can just select Custom Format and paste the above code in!




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

25 comments:

  1. All well and good but somewhat over the top for 99% of day to day requirements.
    My preferred format is:-
    #,##0.00_);[Red](#,##0.00)
    using this gives you negatives in brackets and in red; looks nice on your screen and caters for B/W printing with brackets

    ReplyDelete
    Replies
    1. You can certainly simplify it to that, but this will not show the dash for zero as in the accounting format. I wanted to show how to edit your existing preferred format to include brackets for negatives.

      Delete
    2. Maybe over the top for 99% of day to day but useful if that is what you are looking for - hence I found the page - good job.

      Delete
  2. How please do I set your format as the default format for the Comma style on Number section of the ribbon?

    ReplyDelete
    Replies
    1. If you right-click the Normal style on the Home ribbon and select modify, you can set this as the standard format for this particular spreadsheet. You can use the tip from anonymous below, to include this in the template used for all new workbooks.

      Delete
  3. One way to do that would be to incorporate the format into your default "SHEET.XLTX" and default "BOOK.XLTX" templates so that whenever you open a new workbook or worksheet the format is there.

    ReplyDelete
    Replies
    1. Would you show the complete coding for the addition to the format that anonymous added please?

      Delete
    2. Unkown - These are the default Excel templates where you can change how every new workbook or sheet is formatted. There is a good post here on the subject:
      https://www.excel-easy.com/examples/default-templates.html

      Delete
  4. Can you not strip it down further to #,##0.00 ;(#,##0.00);- (that is, 1 space after the zero in the positive format and 3 spaces after the dash in the zero format? Or #,##0.00 ;[red] (#,##0.00);- if you're showing negatives also in red? Tim

    ReplyDelete
    Replies
    1. The _) works like your space, but ensures that the width of the space is exactly the width of the closing bracket on the negative numbers.

      Delete
  5. The reason why there is an underscore and a closed bracket following the first part of the format, (the positive number), is so that when you have a mixed column of both positive and negative numbers the digits are aligned properly.

    ReplyDelete
  6. Thanks for this Tip, will be useful for a better presentations of my FS.

    ReplyDelete
  7. Glen--
    Very valid point -- brackets are much easier to spot than minus signs ("-"). But my version of Excel (2008) DOES have the option of using brackets for negative numbers, in the "Accounting" and "Currency" format choices. No need for a lot of custom formatting.

    By the way, I for one disagree strongly with the notion of putting negative numbers in red in most cases. Red is a powerful distinguisher, and why should a tiny, irrelevant negative number get more attention than a very significant positive one? If you want to use colors to distinguish numbers, better to use Conditional Formatting.

    Randall Bolten, "Painting with Numbers"

    ReplyDelete
    Replies
    1. My version of 2013 doesn't! Maybe it's only on the Mac!

      Good point about the red for negatives.

      Delete
    2. My version of Excel 2007, shows bracketed negative numbers for the Number, Currency and Accounting categories. I don’t understand the fuss.

      Delete
    3. Wyn. See my reply to Steve below. This is why we see different!

      Delete
  8. I agree with @Wyn - all versions I have used up to 2007 have had this formatting option as a standard feature

    ReplyDelete
    Replies
    1. Mystery solved. It depends on the regional settings on your PC. Thanks to Artem Paramonov for pointing this out on LinkedIn. It is in the standard US regional settings, but not in the standard UK ones. The following article relates to Excel 2003, but it is still the same in 2013!

      https://support.microsoft.com/en-us/kb/832285

      Delete
  9. Thank you very much for the great help: bracket for negative value in Excel

    ReplyDelete
  10. _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

    ReplyDelete
  11. I want to format my values in parenthesis to show as negative values but I also need to apply my custom formatting of [h]:mm;@ - So I basically need negative values shown but in a time format. Any help please...?

    ReplyDelete
    Replies
    1. Alan - there is more than the format at issue here, as the standard 1900 date format in Excel does not recognise negative times. There are a couple of alternatives in this post by Allen Wyatt (if you use the formula approach you would obviously need to tweak it to use brackets rather than a minus sign):
      https://excelribbon.tips.net/T006239_Displaying_Negative_Times.html

      Delete