How to display accounting format as 0, not - ?

[rschissler]

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

 

[Anthony47]

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

Bye

 

[dave2018]

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)

 

[Anthony47]

I suggest that a newbie first learn how formatting the standard way; the second lesson might be "advanced formatting"

 

[dave2018]

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

 

[rschissler]

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.

 

[rschissler]

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.

 

[dave2018]

You're right... .

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

 

[Anthony47]

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

 

[thisoldman]

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'

_($* #,##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