How to display accounting format as 0, not - ?

rschissler

New Member
Joined
Mar 25, 2018
Messages
5
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
 
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.

ExcelCapture_zpshsxoxtjr.jpg
[/URL][/IMG]
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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

Hi, is there a specific place to really learn and dig into this type of formatting and these rules and charachters? Thanks
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 ).
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top