Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got two tables and two pivot tables for each of them respectively. In the first table, the duration of each task is calculated on a column called "duration", but in the second one, there is no such column and duration of each task is a calculated field in the pivot table. As you can see, in the first pivot table, tasks with zero time are not shown. But in the second pivot table, where durations are calculated fields, zero time tasks are shown as "0:00". How can I hide zero times like in the first table?

enter image description here

share|improve this question

migrated from Oct 11 '12 at 14:55

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

Right click on your PivotTable and select Pivot-Table Options, then use the For empty cells, show option - like here:

enter image description here

Ok, in case there is a Number like 0 or 00:00 in your Data and you don't want to display this too, you can solve it with a custom number format - like # or hh:mm:ss;hh:mm:ss;"";.

In excel's help you find this: <POSITIVE>;<NEGATIVE>;<NULL>;<TEXT> as the basic form for user-formats - so, this format here [h]:mm;[h]:mm;"";@ hides 00:00.

share|improve this answer
It's already checked but it does not seem to do the trick. I guess the problem is that those cells are not empty and therefore are shown! (even though their value is 0:00) – Meysam Oct 11 '12 at 8:17
Ok, this was not obvious from your example. Then you can solve this with a custom Number-Format - for usual Numbers i.e. with # as number format. Just a sec, i'll look how to do it for time. – Jook Oct 11 '12 at 8:26
@Meysam - try my format, worked fine on my example. – Jook Oct 11 '12 at 8:32
well, my suggestion based on setting up the format for the zero-case - you find this in excel help as <POSITIVE>;<NEGATIVE>;<NULL>;<TEXT> - when you use only two parts, like you did, then it is interpreted as <POSITIVE&NULL>;<NEGATIVE> - try this maybe, can't hurt to try: [h]:mm;[h]:mm;"";@ – Jook Oct 11 '12 at 8:45
This worked too: [h]:mm;[h]:mm; – Meysam Oct 11 '12 at 9:05

You can create a Calculated field in the formula box type : =IF(Value=0, "NA",Value) Now when there is a zero value it will force an error

Right click on your PivotTable and select Pivot-Table Options, then check the For error values, show option is ticked and blank

The added benefit of this method is the 0 value is not included in any totals or averages and the points will not print on any of the pivot charts

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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