You have taken time to create a product sales PivotTable and placed the branch field in the report filter area, product name in the row area and product sales in the values areas. However you want to analyse sales per branch by quickly generating PivotTables for each branch to be placed on separate worksheets. You are wondering whether this can be done in easy steps. Well nothing to worry about – just follow the steps below as we show you how this process can be easily automated.
Applies To: Excel 2007 and 2010
- We shall use the product sales list to illustrate our example
2. Select any cell within the data list
3. Select Insert–PivotTable
4. Select OK
5. An empty PivotTable will open on a new worksheet. Refer to the PivotTable field list and place the fields as follows
a. Branch Report Filter Area
b. Product Name Row Labels Area
c. Product Sales Values Area
6. Your PivotTable will now appear as follow:
- Note: Branch in the Report Filter
7. You now want to display the Branch details on individual worksheets
8. Go to the PivotTools Options Tab, select Show Report Filter Pages. Select OK
The individual Branch details will be displayed on separate worksheets
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
