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 »2 comments to this article

  1. Amazing ! thanks for the help bro. another sources told me to use excel macro. which is i’m not used to it, But using this method is way more simple

    Reply

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