Suppose you have Products in Filter area and Pivot Table shows Regional Sales by Month. There are 5 products. You want five sheets for each product separately- this is called Explode Pivot Table. But not by doing this manually. Here is how you do it (Reading time 7 min)
Contents
The Need
Download this sample file and follow along.
There are two sheets. Data and Pivot.
Monthly performance by region is shown.
The pivot is filtered by Product…
We want to send data of each product to the respective product manager (PM). Of course, we don’t want them to see the data of the other products.
Inefficient Solution (which is what we usually do!)
Go to the filter area and select one product at a time. Copy paste the pivot table as VALUES into a new file and mail the file to the PM. Job done 
What is wrong with this? Many things…
- First of all, if there are 20 products, you have to manually filter the pivot table 20 times and then copy paste as values repeatedly.
- As you are pasting the pivot as values – the PM cannot analyze the data further. It is just a static report. This limits the value of the report.
Solution for the first problem
Don’t try to filter the pivot table manually for each filter item. Click inside the Pivot and choose the Analysis tab from PivotTable Tools. On the left side of the ribbon, open the Options dropdown and choose Show Report Filter Pages.
Now all fields in the filter area will be shown. In our case we have only one field – Product. Choose the field you need and click Ok.
Now, Excel will do the repetitive work for you and create many new sheets – one per each item in the filter field. Simple and effective. Job done very quickly.
First problem solved. But NOT the second problem. Because even if we send each sheet to each PM, it is still a pivot table. Therefore, anyone can select ALL or other products from filter area to see all the data. Of course, smart people will just double click on the grand total and get the entire raw data as well (including fields which were not included in the Pivot Table).
But we do not want to send them a “paste as value” report.
Solution : Explode Pivot Table
This requires some extra effort. We have to work one sheet at a time. Let us take Mango as the example.
Right click on the Mango sheet and choose Move or Copy.
Choose New Book from the dropdown. Now a new workbook will be created which contains just the Pivot Table filtered on Mango. So far so good.
But we cannot send this file. The file still contains ALL the data. Yes I know that the file contains Pivot Table only. But Pivot Table always makes a copy of the data and keeps it with itself. (Read this article for details: Did you know? Pivot Tables can increase the file size!)
So how to prevent it? In two steps…
Get the raw data for the Mango product
First let us get all the data for Mango product in the new file. Double click on the grand total.
Now a new sheet will be added with all the raw data (for product = mango only). This will be a table and the default name for the table will be Table1. Check it out.
The question is – has the pivot table now adjusted itself to use this filtered data? Unfortunately NOT.
Click Pivot Table Tools – Analyze (or Options tab in older version) – Change Source Data… Notice that it is still referring to the original file.
Also remember that even if this original file is not going to be sent to the PM for Mango product, that person can still choose ALL from filter and double click on grand total to get ENTIRE DATA FOR ALL PRODUCTS.
Force the Pivot Table to use Mango only data
So let us solve the problem. While you are in the Change Source dialog, notice that the active file has changed to the original file – because the data is still in the original file.
Now click inside the range shown. Press F2 key to go into edit mode. Select the file path and range and delete it. Make sure the cursor is still in the Table/Range textbox. Now, very carefully, we have to change the current file to the Mango product file. To do this, click on the View Tab in the ribbon, open the dropdown Switch Windows and choose the newly created file (it will have a name like Book3, Book4 or something like that).
Now just type Table1 in that textbox. Why? Because in a newly created file, the first table created is always called Table1. Finally, click Ok.
Go to the Pivot and refresh it. (Actually you don’t have to. But just to be on the safer side).
Now the Pivot Table has only Mango data. Open the filter dropdown to cross check.
Now you can safely send the file to the Mango PM. 
Yes, this is useful – but complex and still repetitive
But at least we are getting best of both worlds and passing on the benefit of Pivot Table based analytical capability to the PM.
But is it efficient? Absolutely not. We have to do this process for EACH PRODUCT (or in general terms – for each item in the filter area). Which is a lot of work.
So let us find a solution to this problem.
What next
This is a perfect place to write a macro to do all this repetitive but useful work for us. I will publish the macro to do all this automatically in a separate article (very soon).
Till then, try it out manually and get used to it. Many people will thank you for giving them analyzable data 
***
