Pivot Diagnostic tool for Excel (1.0 Alfa) depreciated
An experimental! Excel 2013- 2016 Add-in that lists your Excel memory consumption and connections for all Pivot Tables in your Workbook. It also is able to assign a Pivot to a different PivotCache and as a result limiting the file size of your xlsx file by removing the duplicate PivotCaches in the workbook.
The tool makes also a difference between the PowerPivot Model and the older PivotCache structure.
Sorry, no download available
A follow up (Data Diagnose Add-In version is located here
- Know how your Pivot Tables are connected to the data.
- Makes a list of all Pivot Tables, slicers, PowerPivot Model and Pivot Caches embedded in your selected open Excel Workbook.
- Calculates the size (Kb) of the different Pivot Caches and PowerPivot Model (Tables).
- Can change the data source of a Pivot Table to another classic PivotCache
After installing the Add In, the existing Excel Data tab has an extra button called "Pivot Diagnose" |
After opening the workbook that contains Pivot Tables, you can activate the Pivot Diagnose and select the open workbook in the form. It will display a list of Pivot Tables with their data connection properties. Sheet: Name of the sheet where the Pivot Table resides. Pivot/Slicer: Name of the Pivot Table (also interesting for VBA developers) |
|
This example shows different classic sources Pivot Tables (Cell Range source, Table Source) Only if the Pivot Tables use the same data amount and they do not use extra features like grouping, Calculated Fields of Calculated Items two Pivot Tables with different Caches can be set on ONE (1) cache. |
After you have selected a Pivot Table below the list you can select one of the available workbook Pivot Caches. The button below will change the selected Pivot Table to the selected Pivot Cache. If a Pivot Cache has NO connected Pivot Tables anymore, it will be deleted when you save the workbook. The tool will NOT do any workbook saving. So you can still decide to keep your workbook changes or not. Pivot Tables that are connected with the PowerPivot Model are not changeable to a PivotCache and vice versa. |
|
It's also possible for classic connected Pivot Tables to change their local data-source selection. This is interesting when you discover that the Pivot Tables are using a selection of cells that is too small. Data has been entered BUT the Pivot Tables are not updated. Here you can update them all to listen to a new Cells selection. Tables are supported and Cell Ranges also. BUT: it will change the data source of ALL your Pivot Tables in your workbook. Please check all the Pivot Tables before you decide to save the workbook or use the Save As function. |
|
