How to display accounting format as 0, not - ?
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: How to display accounting format as 0, not - ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to display accounting format as 0, not - ?

    I'm brand new to Excel, and trying to convert Wordperfect Quattro Pro files to Excel 2016.

    In Format Cells under Accounting, I want a $ on the left and a 0 on the right. What I am getting is a - instead of a 0. How can I display a 0 instead of the dash?

    Thanks,
    Randy

  2. #2
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,446
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    Format the cell as Currency; this will allow you to select the currency symbol. Just rightclick the cell, chose Format cell.

    Bye
    Anthony (GMT+1)

  3. #3
    Board Regular
    Join Date
    Mar 2018
    Posts
    222
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    Wait! u can still keep the accounting format! just highlight the cells/column/row or wherever you want to see that type of formating, and right-click and choose Format Cells (or click CTRL+1 for the shortcut...).

    on the left hand side where you can choose different formatting options, choose custom.

    in the Custom Format Bar (or whatever its called... see the red arrow in this pic: [i cant figure out how to add pics to these posts ] https://ibb.co/hRRtX7) type this in:

    _($* #,##0.00_);_($* (#,##0.00);_($* 0_);_(@_)

    (it's a slight modification from the regular accounting format...)

    Enjoy!

    (if it doesnt work, try again, or let me know)

  4. #4
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,446
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    I suggest that a newbie first learn how formatting the standard way; the second lesson might be "advanced formatting"
    Anthony (GMT+1)

  5. #5
    Board Regular
    Join Date
    Mar 2018
    Posts
    222
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    True Point . But it may also help "rookie" users who visit this site...

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    Quote Originally Posted by dave2018 View Post
    Wait! u can still keep the accounting format! just highlight the cells/column/row or wherever you want to see that type of formating, and right-click and choose Format Cells (or click CTRL+1 for the shortcut...).

    on the left hand side where you can choose different formatting options, choose custom.

    in the Custom Format Bar (or whatever its called... see the red arrow in this pic: [i cant figure out how to add pics to these posts ] https://ibb.co/hRRtX7) type this in:

    _($* #,##0.00_);_($* (#,##0.00);_($* 0_);_(@_)

    (it's a slight modification from the regular accounting format...)

    Enjoy!

    (if it doesnt work, try again, or let me know)
    Dave: That works, except it puts a space at the end, after the 0.

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    Quote Originally Posted by Anthony47 View Post
    Format the cell as Currency; this will allow you to select the currency symbol. Just rightclick the cell, chose Format cell.

    Bye
    The problem with Currency, is that it puts the $ next to the 0. And adds a space to the end.

  8. #8
    Board Regular
    Join Date
    Mar 2018
    Posts
    222
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    You're right... .

    I stick to currency, but i'm sure theres a way to fix that...

  9. #9
    Board Regular
    Join Date
    Mar 2006
    Location
    Italy
    Posts
    1,446
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    This is what I get after formatting the cell as "Currency"


    I want a $ on the left and a 0 on the right. What I am getting is a - instead of a 0. How can I display a 0 instead of the dash?
    It has a "$" on the left and the value 0 on the right; if you wish more or less decimals just increase or decrese them.

    Anyway, I don't see why you warry about a possible "space" at the end: we are talking about how display the result, and a "0" is always 0 even if it is displayed mid of the cell.

    Bye
    Anthony (GMT+1)

  10. #10
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,073
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to display accounting format as 0, not - ?

    I'm working with a US English locale. It uses a comma, ',', as the thousands separator, and a period, '.', as the decimal separator.


    • Select the cells you want to have custom formatted. Bring up the 'Format Cells' dialoq with Ctrl+1.
    • On the 'Number' tab, select 'Accounting'. Do not click OK.
    • Select 'Custom'. The current 'Accounting' format should be highlighted in the lower right Combo Box.
    • In the Text Box under 'Type:', in the third field, select "-"??
    • Replace it with 0.00 (or 0,00 if you use a comma as a separator).
    • Now click 'OK'


    Code:
    _($* #,##0.00_);_($* (#,##0.00);_($* 0.00_);_(@_)
       [nums > 0]  ;  [nums < 0]   ;  [zero]   ; [text]
    
    where:
        _(  means insert a space equal in width to '('
        $   is a literal dollar sign
        *   star+space used to fill the cell with spaces to the cell width limit
        #   digit place holder
        ,   thousands separator
        0   required digit
        .   my literal decimal point
        _)  space equal to width of ')'
        ;   field separator
        @   text string
    Last edited by thisoldman; Mar 26th, 2018 at 10:59 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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