Pivot table reports are one of the best features of Excel. They really are a life saver for users who have to summarise or process large amounts of data from Excel tables or lists. Excel Pivots out perform Excel tables or lists in many ways including the below- just to name a few –
- They display the exact data you want to analyse by dragging and dropping
- You can pivot the data to view it from different angles (hence it’s name)
- You can apply focus on specific data details by expanding or collapsing data or simply by applying filters to the Pivot
- Make data comparisons really quickly and easily
- Identify data patterns, relationships, and data trends really quickly
So that is great, we see why we should be using Pivot Tables, but one really great feature of Pivot Tables is the ability to output multiple reports with just a click of your mouse.
The scenario for this example is our 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 a standard Pivot Table for the national sales manager. He or she can easily interactively change the filter on the table to focus on the individual regional managers figures.
All good yes? Of course all of the data summaries are visible for the national sales manager, but they really want each regional manger to have their own report so they can look at their own individual areas.
But that is not a problem! We can easily fulfill this request for multiple reports with the click of a mouse.
Create your usual Pivot Table and add Region to the report filter. All we need to do then is hit
- Pivot Table Tools
- Pivot Table
- Then hit Show Report Filter Pages
- We have one Filter in this Pivot table- Region (one for each Sales Manager)
- Just select it and then hit OK.
You will see all of the Sales Regions now appear in different sheets in your Excel work book automatically. All of your national sales managers now have all of the reports they need individually to discuss with the regional sales managers.
