0

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.

PeterH
  • 7,377
  • 20
  • 54
  • 82
  • Could you use an IF() statement? IF(A1=0;"";DATE) or similar. Im not to familiar with Pivot Tables. – Kevin Anthony Oppegaard Rose May 04 '18 at 12:49
  • I could probably do that in the source data I suppose, but I don't really want to mess with that as I may not always be the person populating. – PeterH May 04 '18 at 12:51
  • A similar question provides [multiple approaches for dealing with this type of problem.](https://superuser.com/questions/515932/display-blank-when-referencing-blank-cell-in-excel-2010) – Bandersnatch May 04 '18 at 14:48

1 Answers1

1

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.

PeterH
  • 7,377
  • 20
  • 54
  • 82
James
  • 34
  • 2
  • 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 08 '18 at 08:18