Blogger
Share this content
0
9
145224

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

Replies

Please login or register to join the discussion.

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

Thanks (0)

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

Thanks (0)

A techies answer
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

Thanks (0)

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

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

Thanks (0)

Try this
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!

Thanks (0)

Success!
Thanks
that has fixed it!
Nic

Thanks (0)

Dashes as zeros
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.

Thanks (0)

I forgot...
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!

Thanks (0)

Use Accounting number format
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.

Thanks (0)