Excel Question - Zero Values

Factual Questions
1

My beloved Excel 2003 is being stubborn. The “Tools - Option” menu lets me hide all zero values for a worksheet. However, the workbook has four worksheets in it, and there doesn’t seem to be any method to set this zero option all at once for an entire workbook. So all of the worksheets need their zero values hidden individually, and therefore this option must be set in the tools menu for each sheet. Now this would not be an insurmountable problem, but each time the file is closed and then reopened, the zero values are again displayed and the whole process must be repeated, for each sheet, each time the **###$^!!! ***workbook is opened.

Is there a method where this zero setting can be made permanent? Excel help does show some methods that would appear to do this, but it involves things like IF functions applied to each cell, which, as there are many thousands of cells involved, would vastly complicate the operation.

If any of you Excel gurus can shed some light on this, I would be vastly appreciative.

2

It’s a real hack, but how about using conditional formatting to make all cells = 0 show up the same as the background color?

3

Can you use a custom number format like so:

4

Thanks, guys. I’ll try both these and decide which is the easiest.

Of course the easiest would be if Excel would just remember the zero option once it was set, but being Microsoft, that probably never occured to them.

5

Easier! Easier! Not easiest!!! I knew that.

My English teacher mother is probably rolling over in her grave right now!

6

I tried to reproduce your problem and could not. When I unchecked the “zero values” option, saved and closed the workbook, then opened it again, the option was still unchecked and the zero values were still not shown. You may have some other issue with the installion of the product or a corruption in your workbook.

A workaround would be some VBA that sets this option each time you open the workbook. If you put this code in the ThisWorkbook module it will automatically turn this option off on all worksheets when the file is opened.



Option Explicit
 
Private Sub Workbook_Open()
   HideZeroes
End Sub
 
Private Sub HideZeroes()
   Dim ws As Worksheet
 
   For Each ws In Worksheets
      ws.Activate
      ActiveWindow.DisplayZeros = False
   Next ws
End Sub
 
' This is here just for convenience, if you want to quickly turn the option back on
Private Sub ShowZeroes() 
   Dim ws As Worksheet
 
   For Each ws In Worksheets
      ws.Activate
      ActiveWindow.DisplayZeros = True
   Next ws
End Sub
7

Today Excel decided to be sort of unpredictable regarding zero values. On opening, all the data from yesterday was OK (none of the zero values were shown). However, one lonely column decided to show its zeros - and then hid them when I went to Options etc. Go figure. I think my copy of Excel must be a teenager.

I had reinstalled Excel just last month because of some other issues.

8

Are the zero values the result of formulas or just integer entries? If the former, you can adapt the formula to replace any resulting zero value with a blank instead.

If the latter, then I don’t know. :slight_smile: