# Excel Question - Zero Values
**URL:** https://boards.straightdope.com/t/excel-question-zero-values/550336
**Category:** Factual Questions
**Created:** [August 15, 2010, 4:48pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336 "2010-08-15T16:48:25Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Daylate](https://boards.straightdope.com/u/Daylate)
#### Post date: [August 15, 2010, 4:48pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/1 "2010-08-15T16:48:25Z")
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.
---
### Author:  [@Reply](https://boards.straightdope.com/u/Reply)
#### Post date: [August 15, 2010, 4:59pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/2 "2010-08-15T16:59:23Z")
It’s a real hack, but how about using conditional formatting to make all cells = 0 show up the same as the background color?
---
### Author:  [@beowulff](https://boards.straightdope.com/u/beowulff)
#### Post date: [August 15, 2010, 5:09pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/3 "2010-08-15T17:09:43Z")
Can you use a custom number format like so:
> [@](#):
>
> Use a number format to hide zero values in selected cells
> Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.
>
> Select the cells that contain the zero (0) values that you want to hide.
> On the Format menu, click Cells, and then click the Number tab.
> In the Category list, click Custom.
> In the Type box, type 0;-0;;@
> NOTES
>
> The hidden values appear only in the formula bar — or in the cell if you edit within the cell — and are not printed.
> To display hidden values again, select the cells, click the Cells command on the Format menu, and then click the Number tab. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.
---
### Author:  [@Daylate](https://boards.straightdope.com/u/Daylate)
#### Post date: [August 15, 2010, 11:43pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/4 "2010-08-15T23:43:46Z")
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.
---
### Author:  [@Daylate](https://boards.straightdope.com/u/Daylate)
#### Post date: [August 15, 2010, 11:45pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/5 "2010-08-15T23:45:38Z")
Easier! Easier! Not easiest!!! I knew that.
My English teacher mother is probably rolling over in her grave right now!
---
### Author:  [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)
#### Post date: [August 16, 2010, 12:27am UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/6 "2010-08-16T00:27:57Z")
> [@Daylate](#):
>
> …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.
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.
```auto
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
```
---
### Author:  [@Daylate](https://boards.straightdope.com/u/Daylate)
#### Post date: [August 17, 2010, 1:37am UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/7 "2010-08-17T01:37:55Z")
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.
---
### Author:  [@amaguri](https://boards.straightdope.com/u/amaguri)
#### Post date: [August 17, 2010, 8:14pm UTC](https://boards.straightdope.com/t/excel-question-zero-values/550336/8 "2010-08-17T20:14:42Z")
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. 🙂