Categorized | excel tips, pivot table

Pivot Table Quick Tip – Using Pivot Table to Separate Data

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)

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Tammy wrote:

    Wow, this is awesome! Can’t wait to show my team first thing Monday morning.

  2. BrianR wrote:

    After a number of country-wide one-hour training sessions, my department’s monthly reporting went from individual files (not even sheets) to a single pivot, with Rep and Region as Report Filters. It was then trivial for each Manager/Rep to select the area they were most interested in – but the huge added-value was that THEY started comparing their own performance to that of others.

    Most of the Reps had never even used Excel before, but I quickly got requests from them for training sessions on how to create Pivots so they could use them for their own presentations and reporting !

  3. Tamir wrote:

    How can the poor manager show to each department ONLY their data? Meaning, On each new sheet there is now a new pivot table with ALL the data of everyone.
    Can he do that

  4. PUNEET wrote:

    Thanks a lot, you solved my query


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel