Displaying zero as - in excel | AccountingWEB

Displaying zero as - in excel

I am doing a spreadsheet in excel for a client who likes zero to be displayed as a dash rather than blank or zero. I can't seem to get it to display a dash and there is nothing in "help" that I can find. Any ideas?
Nicola Stone

Comments

Tony

neileg | | Permalink

I think you'll find that your suggestion is remarkably similar to my earlier posting, except you don't need all those quote marks.

I forgot... suggestion

AnonymousUser | | Permalink

To align positive and negative numbers and display a zero as a - I think you will find this format will work:
#,##0_);"("#,##0")";"-"_)

A techies answer

AnonymousUser | | Permalink

The format of numbers and text within excel is determined by the format of the cell.
To alter the format of a cell (or range of cells, or page depending upon what is selected) you need to go to (Format) (cells) (number). If you select (custom) you will be able to look at and if relevant change how the cell will look.
At the bottom of the (default)list are four formats showing the codes used to create the number layout.
By altering the codes you can alter the position of the number in the cell, reverse negative numbers to display as positives, change the colour of the + or - numbers, display a currency symbol before or after the number, align the decimal point, apply brackets, etc etc.
Oh yes, you can also determine how the machine shows zeros.
For information on what the dashes, underscores, hashes, and brackets do within the code there is a useful page on the MS Excel help which I accessed by going to (Help)(MS Excel Help)(answer wizard) and typing 'custom number format codes' and selecting 'number' from the list at the bottom of the help screen.
The zeros layout is determined by the code which lies between the second and third semi-colons.
To save you looking it up the code to replace a zero with a dash placed two characters in from the left is _-*"-"??_-
If you find a format you like you can save this as a style and have this style adopted for all cells whenever you open a worksheet.
I hope this helps

How about this

neileg | | Permalink

This one seems to work for me:
#,###_);(#,###);-_)

I guess you missed off the zero definition from your custom format.

Try this

AnonymousUser | | Permalink

I had a similar problem myself and eventually found this work around:

Choose custom format (it sounds like you already have) and set the format as:

#,##0.00_ ;(#,##0.00);_-* "-"??_-

I'm not sure why it works but it does!

Success!

nicstone | | Permalink

Thanks
that has fixed it!
Nic

birdman's picture

Dashes as zeros

birdman | | Permalink

I just press the spacebar before and after the dash! It can then be left, right or centre justified.
I use this for accounts etc., it leaves one space between dash and edge of box, so you can't use if this is a problem for your presentation.

I forgot...

nicstone | | Permalink

Thanks for the suggestions but I should have mentioned that the rest of the spreadsheet has a defined number format which includes brackets for negative numbers (and an extra space for positive numbers so they align correctly). So I can't use either of those options unfortunately!

Use Accounting number format

Julian Tomsett | | Permalink

First of all highlight the range you need to alter. Go to the Format menu and select Cells. Select Accounting from the category list. The symbol section automatically comes up with £. Change this to None and click OK.