# Formatting zeros as dashes

A

#### Arun

I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no \$
0 or \$ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?

B

#### Bernard Liengme

Ann:
If you think about it there is no way for Excel to do this. How close to
zero must the value be to be shown as a dash? Excel cannot make that guess.

You could use a helper column with ROUND and hide the original column
best wishes

×

#### ×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ

If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General

×

#### ×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ

If the presence of the \$ sign is essential - you might consider something
like:
[<1]"-";[>=1]\$ #,##0;General
Micky

×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ said:
If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General
---------------------------------------
The first will display a dash for every value smaller than 1
The second will display a dash for every value smaller than 0.05
So, what you have to decide is the exact threshold of your needs.
Micky

Arun said:
I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no \$
0 or \$ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?

×

#### ×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ

Not to mention:
[<1]"-";[>=1]\$ #,##0.00;General
in order to display 2 decimal digits.
Micky

×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ said:
If the presence of the \$ sign is essential - you might consider something
like:
[<1]"-";[>=1]\$ #,##0;General
Micky

×ž×™×›××œ (×ž×™×§×™) ××‘×™×“×Ÿ said:
If I understtod you correct - it is very simple to accomplish your request.
Use "custom Format" - such as:
[<1]"-";[>=1]#,##0.00;General
[<0.05]"-";[>=0.05]#,##0.00;General
---------------------------------------
The first will display a dash for every value smaller than 1
The second will display a dash for every value smaller than 0.05
So, what you have to decide is the exact threshold of your needs.
Micky

Arun said:
I have a worksheet with the data formatted as currency with no decimal
places. I would like all my zeros to show up as dashes. For example, no \$
0 or \$ (0). I understand that if the number is not exactly zero, it won't
use the dash, ie. a .25 will show up as a 0... but this is what I would like
to override. I also don't want to actually change the value in the cell
using the rounding functions.

Simply put, how do I get it to choose the format based upon the display
accuracy of the value versus the true value?