Brackets for minus figures in M/Excel

System
System Posts: 100,534 🤖 Admin 🤖
edited June 18 in AAT member discussion
Can I use brackets in Excel for negative figures rather than the 'minus' symbol which keeps appearing? I can't seem to get around the problem.

Thanks in advance.

Comments

  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    This is how I format my numbers to give brackets:

    1. Select the cells you want to format (or the whole sheet if appropriate)
    2. Right click and select 'Format Cells'
    3. The number tab should be chosen, so click on 'Custom' in the 'Category' box.
    4. Scroll down to the bottom where you will see:

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

    Alter it so that it looks like this:
    _-* #,##0.00_-;* (#,##0.00);_-* "-"??_-;_-@_-

    (You can copy and paste the above into excel if you want.)

    You may also find these ones helpful:

    Zero decimal places with brackets
    _-* #,##0_-;* (#,##0);_-* "-"_-;_-@_-

    Note that the above two show "-" when the cell value is zero. If you do not want this use:

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

    Zero decimal places
    _-* #,##0_-;* (#,##0);_-* " "_-;_-@_-

    Hope this helps

    Tom
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    Thanks, Tom, exactly what I wanted!
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    Tom - as you seem to be a whizz on XL - I have used this formula for ages, but cant seem to "save" it anywhere, so that it is always the default.

    Any help much appreciated.

    Claudia
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    Hi Claudia

    The way to do it is to create a template. Start with a blank Excel document, then enter all the formats you want stored.

    One you have done this, you can either do one of the following:

    1. Re-format all cells to general; or
    2. Format certain (or all) of the cells to the formats you want.

    I would recommend 1. to for now. You can always create numerous templates with multiple formats later.

    Then save the workbook as follows:

    1. 'Click file' and 'Save as' (sorry for stating the obivious, but its helping me remember where I am)

    2. In the 'Save as type' box choose "Template"

    3. In the save in box, you should be able to follow the following path:
    c:\Program Files\Microsoft Office\Office 11\XLSTART

    4. Now choose a suitable name for the template and click save.

    When you start a new Excel document, you will have to click 'File' and 'New'. On the Box which pops, under the templates heading choose the 'On my computer' option and you should be able to see your template. This will contain all your formats.

    You will have to open Excel like this each time, but this is usually quicker than re-typing all your formats everytime you open a new document.

    Hope this helps. If not let me know, and I'll see if I can work out anything else.

    Regards

    Tom
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    You're a star Tom thanks - will try and do it tomorrow.

    Many thanks

    Claudia

    PS - a star smilie would be great!
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    Those formats seem a bit long-winded.

    I use:

    #,##0.00 ;(#,##0.00);-


    Is there anything that they will do that mine currently do not?



  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    that's what i use dean, the only difference I can spot is that ours don't have a gap between the number and the right hand side of the cell where as tom's do.
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    I use the longer ones because when you underline, it underlines the full length of the cell, not just the length of the number.

    I tend to use underline instead of using cell borders, so this is helpful.
  • System
    System Posts: 100,534 🤖 Admin 🤖
    Re:Brackets for minus figures in M/Excel

    Ah, I see.

Categories

Privacy Policy