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?
migrated from stackoverflow.comOct 11 '12 at 14:55
This question came from our site for professional and enthusiast programmers.
I am new to pivot tables. When I move a category into the pivot table fields I get an additional line 'blank'. This 'blank' comes with values which are a duplicate of another category. How do I remove this 'blank' category and the value it is picking up? I would suggest instead of getting rid of the blank, which you can filter out from the pivot.
3 Answers
Right click on your PivotTable and select Pivot-Table Options, then use the For empty cells, show
option - like 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
.
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
You can use conditional formatting for whole sheet or pivot area.Set new rule. 'Format only cells that contain' and set 'equal to 0' and set text color to white.Actualy it hides 0 but works.
Not the answer you're looking for? Browse other questions tagged microsoft-excel-2007pivot-table or ask your own question.
Blank values in a Pivot Table look unnecessary and they can be distracting. You will find below the steps to hide Blanks in Pivot Table.
Hide Blanks in a Pivot Table
When you create a Pivot Table and the source data contains empty cells, the empty cells are reported as “Blank” in the pivot table.
Depending on the organization of your Pivot Table, the Blanks can appear in Rows, Columns or in both Columns and Rows.
In most cases, “blank” in a Pivot Table serve no purpose at all and you can easily Hide Blanks in Pivot Table by following the steps below.
1. The first steps is to identify the Blank values that you want to hide in a Pivot Table. In our case, we have Blanks both in Row and Column of Pivot Table and they serve no purpose.
2. To hide blanks in Pivot Table Rows, click on the Down-arrow located next to “Row Labels”. In the drop-down, uncheck the little box located next to blank and click on the OK button.
This will hide the Blank appearing in Pivot Table Rows. Depending on the Layout of your Pivot Table, this step may also hide the Blank appearing in Pivot Table Columns.
3. If you still find Blank appearing in Pivot Table Column, click on the Down-arrow located next to “Column Labels” and Uncheck the little box located next to Blank in the drop-down menu.
4. From now, whenever the Quantity is blank in date source, the data will not be reported as “Blank” in the Pivot Table.