How can i display a value of 0
as blank in a pivot table. The column I wish to apply this to is formatted as a date. And anything with a value 0 displays as 00/01/1900
.
I tried conditional formatting to simply display zero with a white font, this worked until the data changes in the pivot,
I tried a custom number format: dd/mm/yyyy;;""
This had the same issues as conditional Formatting.
Are there any other things I can try ?
Bare in mind, the pivot table will change its results every day, so I need something that will have a permanent fix.
P.S I cannot just filter out the 0s, as there is information I need from other columns in the same Row as a 0 etc.
Best Answer
Perhaps the issue is in the File Options.
See if un-checking the
Show a zero in cells that have zero value
helps.Under the
Display options for this workbook
heading (scroll about ¾ of the way down)It helped me before when I had a similar issue. Though, unfortunately, I can't seem to recreate the problem. Anyone know what causes this issue?
Anyhow, PeterH, I hope the Options solution works for you.