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.


Perhaps the issue is in the File Options.

See if un-checking the Show a zero in cells that have zero value helps.

File > Options > Advanced 

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.

  • Thanks @James, this is the work around i had used, only down side is the anywhere outside the pivot that is a 0 now displays as blank also, but i can live with that, – PeterH May 8 '18 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.