display zero as "- -"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks Andy & Frank ! I neglected to mention one other critical factor. The Lotus feature applies to all cells on a particular worksheet in the file but retains the format - be it a percent with x decimals, currency with 2 decimals, a number with x decimals & comma separator etc. Seldom do I have a worksheet restricted to only one format. Both of your methods create a single format for all cells on the worksheet. Is there another way ? P.S. I sent this as a new thread as I wasn't sure whether a reply would be noted by your monitoring systems.

I am just converting to Excel (2002) from Lotus5 (1995). In Lotus, there is an option to globally (for the specific worksheet in the file) display a zero as any thing your heart desires. My choice has always been to use "- -" since I find that a blank may indicate a lack of data or formula and a displayed zero makes the eye stop and evaluate every cell before moving to the next ( ie. it clutters the report). With Lotus, the "- -" will display whenever the value of the cell is zero, regardless of whether the cell is data or any type of calculation. With Excel, I have only found the option to display as "0.0" or blank or to use a formula to achieve this - neither method achieves the equivalient functionality of Lotus on this issue. Surely there has got to be a way. Can anyone help ?
 
Format the cells as Accounting --- It will display the zeros as -


: Thanks Andy & Frank ! I neglected to mention one other critical factor. The
Lotus feature applies to all cells on a particular worksheet in the file but
retains the format - be it a percent with x decimals, currency with 2 decimals,
a number with x decimals & comma separator etc. Seldom do I have a worksheet
restricted to only one format. Both of your methods create a single format for
all cells on the worksheet. Is there another way ? P.S. I sent this as a new
thread as I wasn't sure whether a reply would be noted by your monitoring
systems.
:
: I am just converting to Excel (2002) from Lotus5 (1995). In Lotus, there is
an option to globally (for the specific worksheet in the file) display a zero as
any thing your heart desires. My choice has always been to use "- -" since I
find that a blank may indicate a lack of data or formula and a displayed zero
makes the eye stop and evaluate every cell before moving to the next ( ie. it
clutters the report). With Lotus, the "- -" will display whenever the value of
the cell is zero, regardless of whether the cell is data or any type of
calculation. With Excel, I have only found the option to display as "0.0" or
blank or to use a formula to achieve this - neither method achieves the
equivalient functionality of Lotus on this issue. Surely there has got to be a
way. Can anyone help ??
:
 
Hi Ron
You could also use a custom number format of

[=0]"- -";0.

You can replace the 0.0 with whatever format you typically want to show

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Ron Blakeney wrote: ----

Thanks Andy & Frank ! I neglected to mention one other critical factor. The Lotus feature applies to all cells on a particular worksheet in the file but retains the format - be it a percent with x decimals, currency with 2 decimals, a number with x decimals & comma separator etc. Seldom do I have a worksheet restricted to only one format. Both of your methods create a single format for all cells on the worksheet. Is there another way ? P.S. I sent this as a new thread as I wasn't sure whether a reply would be noted by your monitoring systems.

I am just converting to Excel (2002) from Lotus5 (1995). In Lotus, there is an option to globally (for the specific worksheet in the file) display a zero as any thing your heart desires. My choice has always been to use "- -" since I find that a blank may indicate a lack of data or formula and a displayed zero makes the eye stop and evaluate every cell before moving to the next ( ie. it clutters the report). With Lotus, the "- -" will display whenever the value of the cell is zero, regardless of whether the cell is data or any type of calculation. With Excel, I have only found the option to display as "0.0" or blank or to use a formula to achieve this - neither method achieves the equivalient functionality of Lotus on this issue. Surely there has got to be a way. Can anyone help ?
 
Back
Top