#Excel 2007 pivot table tool bar how to#

This will sent us on a very familiar path  On the left side, find the PivotTable and PivotChart Wizard and with the Add button add the commands to the Quick Access Toolbar.Īfterwards we click in our data and run the command from the Quick Access Toolbar. To do this, you go to File (file)/Options (options), and then Quick Access Toolbar. This command has been part of Excel for what seems an eternity, however since by default it is not on any of the ribbons, you have to add it to the Quick Access Toolbar. If you want to create a new PivotTable so that its Data Cache is separate from the other PivotTables you might have, then you must create it in a particular way, or better yet with a special command called PivotTable and PivotChart Wizard.

#Excel 2007 pivot table tool bar how to#

Afterwards I will give you two methods on how to “separate” PivotTables that have already been created… Method 1 (creating a separate PivotTable report) First way is linked to the creation of the new PivotTable report and tells us how to create a PivotTable in such a way that it already has its own Data Cache and does not share one with the existing PivotTables. In fact, In this article we will discuss three different ways how to do this. There are even more ways how sharing a Data Cache effects our analysis but both listed above are reason enough for the need of a “separation” to arise. So you’ve lost the ability to group for example a Date field by months in one PivotTable and by Quarters in another. If you used this field in another PivotTable, it reflects this grouping instantly. The grouping of records within a single field (for example, a Date field that you combine by months or quarters) now cannot be done on an individual PivotTable but immediately effects all the other PivotTables that are related to the same Data Model. Refreshing one individual PivotTable consequently refreshes all PivotTables that are based on the same data, which can be a great thing but you can easily imagine some cases where this would not be such a good thing. While this solution is obviously a great way to save space on the computer, this method also has two quite severe consequences for your PivotTables. Therefore, for each new PivotTable analysis that uses the “same” data, Excel saves hard dish space since it does not create its own Data Cache but rather uses the same one as previous PivotTables created on the same Data Model. In Excel 2003 each PivotTable had its own Data Cache but now the PivotTable that is created using the same Data Model or Data Source as another PivotTable also borrows that Pivot Tables Data Cache. The need for indexing and fast creation of analysis has forced it to work in such a manner. The PivotTable has always created something called Data Cache based on the data provided. Of course you would like to make the file size as small as possible and for this purpose even the creation or better yet the behavior of PivotTables has changed since Excel 2003. During the creation of Office 2007, 20, a great emphasis was given to the file size.