Search Not Just Numbers

Tuesday, 13 October 2015

Excel Tip: Show negative numbers in brackets









We will edit this code to give us our brackets.


The sections are as follows:

  1. Positive Numbers
  2. Negative Numbers
  3. Zero
  4. Text



-* #,##0.00_-

The first character is simply the minus sign.




The new negative format should now look like this:

* (#,##0.00)


The positive section currently looks like this:

_-* #,##0.00_-


The new positive format looks like this:

* _(#,##0.00_)


_-* "-"??_-

But, applying these changes, becomes:

* _("-"??_)

So, the complete new number format is:


We could trim this down even further if we like.



Our streamlined format now looks like this:





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

    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.

    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.

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

    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.

  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.

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

    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:

  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

    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.

  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.

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

  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"

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

      Good point about the red for negatives.

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

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

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

    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!

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

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

  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...?

    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):