So the poor little manager looks at his computer and says, “how to heck am I supposed to separate this data down to individual sheets for each department.” The manager in question had just been handed over a large file containing financial information for all the departments in his organization and was asked to break it down so that each department’s data should end up in a separate sheet.
The manager pondered over this for a while. Had the number of departments been few, he would have done what he had always done – make a pivot table, filter down the list manually and one-by-one copy each department’s data onto new a sheet. But this wasn’t too appealing an option. The number of departments had grown over the years and to get the job done, he would have to repeat this activity a dozen or more times.
All of a sudden in a brilliant flash of inspiration, he discovered the ‘Show Report Filter Pages‘ option and with a single click, he was able to get the job done. How did our poor little manager do it? He selected the original pivot table, clicked on the ‘Options’ tab on the ribbon and then used ‘Show Report Filter Pages’ from under the ‘Options’ button.
The pivot table had now been copied onto a number of sheets. Each sheet had the data filtered out by the department and had also been named properly.
Moral of the story – Outside of a spreadsheet, a dog is a man’s best friend. Inside of it – a pivot table. (Anonymous)