44. Videotutorial – Deal with Pivot Tables 4 – Page field

Another episode of tutorials Deal with Pivot Tables continues with the topic Page field. It serves not only for filtering, this can produce several detailed Pivot tables according to the selected criteria. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Please note that this tutorial is presented in czech language, with english subtitles.

Source file can be downloaded here.

In previous episodes we have shown how to create a basic model of Pivot table, how to change summaries and how to use the Design tab. Now comes the time to use the so-called Report filter or earlier Page field.

We continue with our Pivot table showing various amounts of travels for personnel in each country. From section Rows drag the Name (Jméno) field to the Filter section. Above Pivot table it will show the new filter box. Filtering is one of the two uses of this section. Basic filtering can be set up in other parts of the table, but this has a top priority.

We’ll use it for detail calculations only for a specific employee, or more employees if we tick Select multiple items. Finally, once again we display the information for all employees. We could go on dragging other information in the section Filter and specify our search.

The second, less known, purpose of this Pivot section lies in the fact that with its help we create new worksheets for each individual value in the Filter field. It gives us an easy new Pivot tables with a selected detail. Execute this command on the Analysis tab, select arrow next to the Options button and select Show report filter pages. We choose what field we use, in our case there is only a Name (Jméno). After confirming with the OK button it is already creating new sheets with detailed Pivot tables. We can now for example send detail of every employee to see their travels. Surely you will agree that without this tool would copy of Pivot table and filtering each separately took much more time. This is what is Filter section used for.

Next time we will look at another great functionality, and that is automatic grouping of dates and numbers in intervals.