Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com Oct 11 '12 at 14:55

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

add comment

1 Answer

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
    
I am currently using this custom number format: [h]:mm;@. But still am seeing 0:00 values. Are you suggesting I use another format? Please note that I have chosen this format so that I can correctly see the hour values exceeding 24 stackoverflow.com/q/12834115/69537 –  Meysam Oct 11 '12 at 8:40
    
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
show 3 more comments

Your Answer

 
discard

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.