Split data into multiple tabs from pivot table
For presentation there is often a need to split a main, data intensive, pivot table into smaller more agile data sheets. Using pizza sales data from a previous example we can see how this may be useful to split the data into multiple worksheets.
The original data above will be used to generate a simple pivot that will be summarised by the Pizza type. If you are unfamiliar how to make a pivot table then visit our pivot table tutorial.
The pivot table has the Row labels of Pizza type and the sales data has been used to, we then need to select an appropriate filter. In this example the pivot table has only three fields so the pivot has to be the final variable.
The filter options is now visible above the pivot table data as normal.
Clicking on the location filter we then see all locations in the data are now visible, this works in exactly the same manor as the data filter.
Clicking into the pivot activates the PivotTable Tools ribbon, selecting the Options tab gives the following menu options.
Click on the “Options” drop down arrow and a fly out menu appears with the following options, Options, show report filter options and generate GetPivotData.
Select the “Show Report filter Pages..” option
A pop up window appears requiring the report filter to be selected, however in this instance there is only a single option to select as I have used a very simple data set for the example.
Excel now opens a tab for every filter item in the data set so the pivot table for each location can now be manipulated further to gain greater insight.