How to exclude hidden rows in pivot table
Normally, pivot table counts the hidden rows. We have to add a flag to each row to determine if it is hidden or not. How to do that shows this example.
We have this common table including some items, categories and number of items. In the table is column visibility (value 1 is for visible row, value 0 is for hidden row).
Visibility values are made by formula:
The first argument of SUBTOTAL function causes that SUBTOTAL will count number of cells that are not empty. Therefore, the second argument has to be the value that is always filled (for example ID, name, title or something like that). SUBTOTAL can ignore hidden values, so when the row is hidden the result of the formula is 0.
Now we create the Pivot Table and we put Visibility into Report Filter field.
Try hide some rows and use filter above the Pivot Table. Maybe, you will have to refresh the Pivot Table to see Visibility values 1 and 0.