+ Reply to Thread
Results 1 to 12 of 12

Problem with Accounting Formatting Option

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Problem with Accounting Formatting Option

    Hello,

    I've got a problem with trying to format cells with the accounting option.

    I want it to show negative numbers in brackets, but it only will show a minus sign in front. I also tried currency to see if it would do it that way as well, but it won't.

    Has anyone else had this problem? How do I fix it?

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,843

    Re: Problem with Accounting Formatting Option

    If you are formatting as accounting as it's not putting it in brackets, it's probably text and not a number.
    To check (let's say your "number" is in A2), in an empty cell put =ISNUMBER(A2) and see if it's true or false.
    If, it's false, we need to see why. Can you upload the spreadsheet or copy those particular cells into a new spreadsheet and open that? (Go Advanced> Manage Attachments)
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Problem with Accounting Formatting Option

    I tried this and it said TRUE.

    Please see attached spreadsheet anyway.Book1.xlsx

    But since it is a formatting, I'm wondering if it will show up the way I see it anyway. Here is a screenshot as well....Untitled.jpg

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,843

    Re: Problem with Accounting Formatting Option

    Okay, not sure what you did to try to get accounting format but there's two easy ways.

    Select your cells and click on the $ in the Home Tab in the "Number" box
    or
    Select your cells and right click> Format cells> Number> Accounting
    Does that help?

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Problem with Accounting Formatting Option

    I usually click the coma icon and it changes it, but i've tried the $ and selecting accounting from the drop down menu and also by clicking the little arrow thing in the bottom corner of the "number" group and it still has it's own mind...!

    When I click the $ sign, the only thing it does different is add a $ to the number!

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,843

    Re: Problem with Accounting Formatting Option

    Hmmm, I did it both ways that I outlined for you in your book and it worked fine. I'm wondering if there's some option in your book that I'm not aware of. I'm attaching it now. When I open this, I see it in ().
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Problem with Accounting Formatting Option

    When I opened it, it stayed with the brackets, but it says custom instead of accounting or currency. It must be a "random" issue that I'm destined to live with!

    I'll see what the custom setting is and if I can use it on other books. The thing is, it is every book that I open that does it this way!

    Thanks for your help though!!

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    12,843

    Re: Problem with Accounting Formatting Option

    I'll ask around to see if anyone else knows what's going on with this. Good luck.

  9. #9
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Problem with Accounting Formatting Option

    Thanks,

    when I tried the custom format in a new excel spreadsheet, it didn't show up, so it must have just been due to the formatting abilities on yours or something!

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with Accounting Formatting Option

    see if there are any custom formats that have [$-409] or similar at the beginning ,delete all that you see
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,464

    Re: Problem with Accounting Formatting Option

    Accounting for me shows up as: -0.87 ... with several spaces between the sign and the 0.

    If I change to Custom, this shows up as: _-* #,##0.00_-;-* #,##0.00_-;_-* "-"??_-;_-@_-

    A1 is formatted as: _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
    B1 is formatted as: _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)

    I'm using Windows 7, Excel 2007, UK Regional settings


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  12. #12
    Registered User
    Join Date
    09-14-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Problem with Accounting Formatting Option

    martindwilson - there aren't any in a new book - it was only the one he sent me on this post.

    TMShucks - looks like yours is doing it the same way mine is. Maybe my machine mysteriously thinks that it is English, but that doesn't make sense because I have it set as Canada/USA.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1