Excel Pivot Table: Hide rows where all measures are blank or zero

0
votes

I'm using powerpivot as datasource for my pivot table. Using Excel 2013.

Just want to hide rows where all measures are blank or zero, like the one selected in the following image:

Trying to hide the selected row

1
excelpivot-tableexcel-2013powerpivot
Use filters, maybe? – teylyn
can you explain with some detail? I can filter out zeros in a column, but I need to hide only those rows that have zeros or blank in all columns. If at least one column has a value, it must be shown – Jack Casas
Go back to your data table and add a new column. In this column you add a function like if(A2="";0;if(B2="";0;if(A2=0;0;if(B2=0;0;1)))) this should show "1" if there is something in A2 or B2 and show "0" if there is nothing or 0. Then you create the pivot table and you can now filter on your new column which has values 1 or 0 depending on if there is something in the row or not. – Andreas
I have this approach in mind. The problem is that maybe A2 has a value and B2 does not. But the user only puts B2 in the pivot table. So the row must be hidden and it won't. – Jack Casas
Here is a start... I don't have the time to do more. It reacts to empty and all zeros but not a mix. hoppvader.nu/Bok1.xlsx I had to translate the formulas to English so I'm not sure it actually works – Andreas

1 Answers

0
votes

I wanted the same in my report. I just filtered out the (blank) value, and Save.

enter image description here

No more blank rows -

enter image description here