How to use the Pivot Table report filter including how to create multiple pivots for each item in the filter.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 02:52
- - Skill Level: 5
- - What's my skill level
Report Filter for Pivot Tables
As if Pivot Tables has not given you enough in terms of column labels and row labels it does give you another method to summarize and categorize called the Report Filter. The best way is just to show you.
You’ll see I’ve got something called “Destination Network” and that’s telling me who I am making my phone calls to. If I click on it and drag it here – if you watch the pivot table- you’ll see I get a new item here with a new drop down and currently it says “All”. When I click on it, you’ll see it itemizes all the different networks that could have been called. For example, if I click on MTN and I say OK – this data now only relates to calls made to that item. I can change that, for example, to be Vodacom. If I click again, you’ll see there is even an option “Select Multiple Items”. When I click on it – you now get these little boxes which means I can now combine things, so maybe I want to compare the first two, and when I say OK this data now consists of those two items. I’ll just put them all on.
In a similar way to row and column labels, you can right click on the Report Filter, go to Field Settings and all the options that existed previously exist here and you can see you can play with them if you want to.
A very useful tool available with the Report Filter is the ability to show all these pages quickly and easily in its own Pivot Table. So just to show you, you’ll see this is “All” and we have these items here. At the moment it is in a sheet called Sheet 3. If I click in here and I activate the Pivot Table Tools Ribbon, in the left here you’ll see something called Options and when I click here will be something called “Show Report Filter Pages” and just again watch you’ll see there are only 3 sheets here and when I say “Show Reports Filter Pages” and I click- it asks me what do you want to filter it on? At the moment there is only one of them, I say OK, watch down here. What it has done It has created a separated sheet for each item it could find, activated that item, and as a result we now have a separate sheet for each of the pages without us having to manually do it. It is quite a nice way of generating these reports.