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

common excel table with visibility

Visibility values are made by formula:


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

  1. I got this great idea to work perfectly as long as I put the pivot table in a new worksheet. For some reason it breaks down if I put the pivot table in the same worksheet. Not sure why.


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


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


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