Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative amounts (2003)

    How can I set up excel to show all negative amounts in red and in brackets without having to format the cells every time when I open up a brand new sheet?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative amounts (2003)

    Simplest:

    Open a blank workbook.
    Make sure it has the number of sheets you want.

    Format, style
    change the number format of the Normal style to what you need.

    Now save-as your workbook as filetype temmplate, name Book.xlt in your XLSTART folder.
    Remove all sheets but the first one.
    Repeat the save-as with a name of sheet.xlt
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative amounts (2003)

    I thought of that too, but it has a serious disadvantage (in my humble opinion).

    The default number format of the Normal style is General, i.e. the number of decimals displayed is flexible. Whole numbers are displated without decimals, fractions with more or less decimals as needed (it depends both on the value itself and on the column width).
    If you set the number format of the Normal style to a specific number format, you lose this flexibility. All numbers will be displayed with the number of decimals you specify, whether they are needed or not, and if the column is too narrow, you'll see ###.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Negative amounts (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 18-Mar-05 07:32. fixed the formatting due to [red])</P>What if you use a format like:
    General;<!t>[red]<!/t>"("General")"

    Then it is general for postive, it is "general" for neagative, but makes them red and in parentheses...

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Negative amounts (2003)

    I was playing with this, and altered the normal styles number to "General;(red)-General". (The round brackets must be square though!). This worked well with whatever integer or decimaled value you feed into a cell. Would this still cause problems????
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative amounts (2003)

    Yep, that is a good idea. Thanks, Steve and Rudi.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative amounts (2003)

    I considered to mention that drawback, but since the OP wanted all numbers that way, ....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •