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.

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).

common excel table with visibility

Visibility values are made by formula:

=SUBTOTAL(103,A2)

Visibility by SUBTOTAL

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.

Pivot table field list

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.

Pivot Table with visibility filter

Add a comment »One comment to this article

  1. Simple solution of my problem with pivot tables. Thank you for this example.

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress