New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Suppress Or Change Display Of Zero Values

Free Excel Tips and Tutorials

Suppress Or Change Display Of Zero Values

Postby Sisyphus » Sun Jul 15, 2012 9:48 pm

There are many instances in Excel where one prefers a zero not to be displayed, in particular, where Excel substitutes a zero for a blank such as would occur as the result of a formula like =Sheet2!A1. Actually, Excel provides quite a number of ways of dealing with this problem.
  1. The easiest one may be to hide all zeros in your sheet. You can set your workbook Options to either display or not display zeros. (Options -> Advanced in 2007 forward).
  2. You can set a conditional format on the cell where you don't want the zero displayed:
    If the cell's value = 0 then change the font color to the background color.
    The zero will be there, but you won't see it.
  3. Little known is the solution involving a custom number format. Use this formatting mask 0;-0;;@
    (Set this mask at Format/Cells -> Number -> Custom -> "Type" field)
    Instead of 0 and -0 you can use other formats that you may require to display positive and negative numbers.
    Actually, it is the element following the second semicolon that determines how to deal with zero values and you can do more with this than just leave it blank. You can let zeros be displayed as dashes or, as in this mask, 0;-0;"no data";@, as a comment like "no data".
  4. Of course, if you are indeed copying from another sheet you can also deal with the zero at the formula level.
    =IF(LEN(Sheet1!A1),Sheet1!A1,"") would effectively prevent Excel from converting a blank space into a zero, displaying a blank instead, while still displaying a zero as zero if it exists at the source.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 3 guests