This tip is one my most favorite time savers when using Pivot Tables- the ability to create many reports with just a few mouse clicks.
The scenario for this example is the national sales manager want to meet with all regional sales managers and look at their year to date sales. We have extracted the data from our database and have saved it in .xlsx format.
We have created the the Pivot Table and the national sales manager can easily interactively change the filter on the table to focus on the individual regional managers figures.
All good yes?. But the national sales mangers really want each regional manger to have their own report. But that is not a problem!
Create your usual Pivot Table and Region to the report filter.
All we need to do then is hit
- Pivot Table Tools
- Pivot Table
- Options
- Show Report Filter Pages
We have one Filter in this Pivot table ….
You will see see all of the Regions now appear in different sheets in your Excel workbook automatically, your national sales ,manager now has all of the reports they need individually to discuss with the regional sales managers.
Job Done!
jim enright says
what version of xl? cannot replicate in 2007. thx
jim enright says
never mind! got it – my bad.
manpreet says
Hi,
This is an awesome article. Now I am facing another situation in which I want to create multiple charts based on the Report Filter criteria. Currently I am generating multiple reports and then creating chart for each report.
Please suggest how to automate chart and it can help a lot.
Thabang says
This is fantastic