Have you ever tried to create a Pivot Table from a list that has a filter applied? If so you’ll know that it doesn’t work. Excel ignores any filters applied to the source data and uses the entire list or Table.
In this video I show you a simple trick to create a Pivot Table from a filtered list. I also explain why my two original ideas to solve this problem were not suitable.
Oh, and my new Excel t-shirt makes it’s debut
Links to the video and individual sections below:
Introduction(https://youtu.be/lzGKDPGdzCw?t=0)
Explanation of the Problem(https://youtu.be/lzGKDPGdzCw?t=35)
Apply a Filter to a Pivot Table(https://youtu.be/lzGKDPGdzCw?t=97)
My Chosen Solution: Add a Column to the Source Data(https://youtu.be/lzGKDPGdzCw?t=198)
First Idea: Manual Copy and Paste(https://youtu.be/lzGKDPGdzCw?t=331)
Second Idea: Use the FILTER Function(https://youtu.be/lzGKDPGdzCw?t=383)
Another option for your video — create pivot table from filtered list — using the FILTER function.
1. add a worksheet (call it FilteredDataSheet) and using FILTER function, copy the table headings into Row 1; using FILTER function , filter the original data (which is an excel table) — this will always be updated by excel when your table changes.
2. using the name manager, create a formula for a variable (FilteredData) which adjusts dynamically — use this: =offset(FilteredDataSheet!$A$1,0,0,counta(FilteredDataSheet!$A:$A),counta(FilteredDataSheet!$1:$1))
3 create your pivot table using FilteredData as your source (place your pivot table in any worksheet except FilteredDataSheet as you can create circular references)
I believe this can be extended with the FILTER function to filter on more than one field
Let me know if this works for you.
Hi Bob. Great suggestion and it works well. You just have to remember to Refresh the Pivot Table if you change the criteria or add more rows to the source data