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

 

[rschissler]

This is the look I'm after. The dollar sign is on the far left, and yes it is important that the 0's line up with the other numbers.

[/URL][/IMG]

 

[thisoldman]

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

 

[thisoldman]

Without the dollar sign for zero values:

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

 

[dave2018]

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

Hi, is there a specific place to really learn and dig into this type of formatting and these rules and charachters? Thanks

 

[thisoldman]

The Microsoft support page: https://support.office.com/en-us/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4

Some of the lesser known formatting codes are left out of that page and it may not be the easiest format to teach yourself some of the tricks. You can Google "excel custom number format" and see if any of the results suit your learning style.

Somewhere on the Internet, I learned about Excel's older style of color specification. To display a dark red dot when the cell value equals 1 and a gray dot when it's not equal to 1:

[Color9][=1]•;[Color48][<>1]•

Google "excel 56 colors" to learn more.

 

[thisoldman]

When I was teaching myself custom formatting, I created a cheat sheet workbook. I started exploring how to achieve "elegance through minimalism" at about the same time. Creating your own cheat sheet is a good way to learn.

This is the current version of the workbook: https://www.dropbox.com/s/z95y224p0a42ige/custom_number_formats.xlsx?dl=0

 

[rschissler]

thisoldman: Thanks, that's really helpful, and I seem to be able to do it now. I even figured out how to get rid of the space at the end, but now I'm leaving it in in for cells that are a (loss) where I need the extra space for the ).

 

[dave2018]

When I was teaching myself custom formatting, I created a cheat sheet workbook. I started exploring how to achieve "elegance through minimalism" at about the same time. Creating your own cheat sheet is a good way to learn.

This is the current version of the workbook: https://www.dropbox.com/s/z95y224p0a42ige/custom_number_formats.xlsx?dl=0

Wow! Thanks so much for Sharing!