Excel PowerPivot Data Diagnose Add-In

This tool enables you to get all information needed to diagnose and detect PivotTable data problems and slicer connectivity data in you Excel Workbook.
It's a diagnostic tool for PivotTables and also to diagnose the PowerPivot Model connections with the PivotTables in different sheets.
It wil display all the elements that are connected with data, together with a list of sheets and what is on them.

From Excel 2016 onwards a PivotTable can get it's data from a range of cells, a table or even a model (PowerPivot).
Slicers can be also be connected to multiple PivotTables. PivotCharts can also configured to be getting their data from PivotTables or directly from the model.
To find out what's going on with your data in the Excel Workbook this tool can be used.

You can compare the different data source ranges on the Pivot Caches behind the Pivot Tables to see if they all point to the same amount if data/rows.
You can also find out how many Pivot Caches are in your Workbook and connect Pivot Tables to other caches.
It's also possible to set existing old PivotTables on new Data Sources with this tool.

This is an add-in without the need of installation services or Administrative permissions.
This Add-in is only tested in an Excel 2016 32bit Office version. All other versions can be tested but i cannot give you any guarantee it will work completely.
This add-In will NOT work for a (PowerPivot) Model created in 2010 and not converted to an Excel 2013 Model. This older model is inaccessible from the VBA object model library!
You can open this Add-in like a normal Excel file, or you can 'install' it by using the Add-In menu (File Menu -> Excel Options -> Add-Ins -> Excel Add-Ins -> Browse

Download

Version 0.9

Still in development

Moving - Copying - Renaming of Measures in a model is possible
This is very interesting if you want to migrate your canculations from 1 csv data source to a folder with different csv files.

Version 0.8

Initial public release

Display of Workbook Properties like:
- Named ranges: names and range addresses on Workbook level names
- Connections: quantity and properties
- PivotCaches: quantity and properties
- Slicer Caches: size, properties and quantity
- PowerPivot Model: size and table properties, relationship properties
- VBA project
Display of Worksheet properties like:
- Hidden / Protected or normal worksheets
- Tables in worksheet
- Pivot Tables in worksheet
- Pivot Charts in worksheet
- Slicers (grouped or non grouped)
- Used cells counted from A1
Display of data properties of data elements on a worksheet
- PivotTable properties (Name, Source Data/Cache)
- PivotChart properties: (Amount of Series)
- Slicer properties (Connected amount of PivotTables)
- Table properties

The workbook data diagnose tool is located in the Data tab between Sort & Filter and the other Data Tools.
It can be seen as an extra audit tool, aimed at the internal data connections around tables, the model, PivotTables and PivotCharts

The first thing you will want to do is to open a workbook to investigate.
For this you can use the open Workbook button and search for an Excel file to be examined.

After the opening of the workbook, the Workbook properties are shown AND click-able. The Worksheets in the workbook are also displayed now.

Features: clicking on one the Workbook properties will display a detailed window.

Workbook Properties:
- Workbook level Names
- PivotCaches
- SlicerCaches
- Total quantity of connections
- Linked table connections
- Query Connections
- Is there a Model in this Workbook and it's type.
- Is there a VBA Project ? (for xlsm files)
Worksheet Properties
- Name = Normal Sheet
- *Name* = Protected Sheet
- [Name] = Hidden Sheet
- Worksheet scoped Names
- Tables in the sheet
- PivotTables
- Slicers (grouped or not grouped)
- Charts
- Used row and columns

Features

Delete button: deletes the sheets
There is a filter selection available to filter out only sheets containing a PivotTable
Double-click on a sheet-name: Shows the sheet (focus)

Clicking on one of the Workbook properties numbers will reveal extra information about the subjects.

Example: Workbook Connections:

This displays a list of ALL the Workbook connections:
- External connections
- Model Connections
- Query connections
- Table (Linked table) connections

Example: Model connection details:

- Model Size (Disk size, Shrinkable Memory size, Unshrinkable Memory Size)
- Tables in the model
- Amount of records found in the table
- Origin of the table
- Columns found in the table (with their data type)
- Calculated fields (2003 non Model - PowerPivot type)
- Model relationships (Active or not)

After investigating the workbook properties we can turn to the different sheet properties.

Below the workbook properties there is a list of the sheets in the selected workbook.
For each sheet we display:
- Names (Scope of the Sheet)
- Tables
- PivotTables
- Slicers
- PivotCharts
- Used amount of rows and columns

When a sheet, or multiple sheets are selected the rightmost list will display the properties of the data objects in the selected sheets.

If you select a data object. On top of the rightmost list you ca n edit some properties of the selected data object.

For Tables:
- Remove filtering

For PivotTables
- Remove filtering on the Columns or Row Fields
- Remove filtering on the Page (Filter) Fields

For non PowerPivot (Model) connected PivotTables
- Change the assigned PivotCache
- Change the data source (Range or Table)

For Slicers
- Remove filtering