The problem

Power Pivot (aka The Excel data model) is a columnstore database,
The impact of a single column on the data model size, can sometimes be dramatic . One column can hold half the size of the model, depending on it's sorting and number of unique values.

In general, The best practice is to pull only the columns you need, But we tend to add many columns "just in case"

Now, Lets say you have an Excel file with a  data model, some measures, Slicers , Pivots, Charts, Cube formulas , and the data model contains 6 tables with 10 columns each.
How will you find redundant columns  which are not in use anywhere and can be deleted to reduce model size ? This can massively improve performance.

The solution – Redundant Column Inspector

This  VBA   and  Excel tool scans through pivots,Slicers, formulas and more for each data model column , And finds the ones Which are not in use anywhere.
The results of the scan are displayed in a nice table, Pre-filtered for just the missing ones

The tool scan the following objects:

* Pivot Tables and Pivot Charts – the columns can be used in Rows fields, Column fields, Report filters and data field (as implicit measures)
* Slicers – If the column is used a filter through a slicer object
* Relationships – If the column is used as a one side of a relationship
* Measures – The tool search in measure formulas and check if the column is referenced  ***This one requires Excel 2016 to work ***
* Cube Formulas – If the column is used in cube formulas

The tool does NOT scan (Yet)

* The formulas of a calculated column
* A column which is hidden from client tool

These two cannot be accessed (yet) from the Power Pivot Object model through VBA

How to use ?

* Download the tool here: [wpdm_file id=3]
* Open and press 'Inspect Workbook':

* Choose the workbook to inspect
* Check out the inspection results table:

The table is filtered to include only redundant columns, Anything in this table (unless accessed from a data model calculated column) can be removed and reduce the model size.

If you remove the filter from 'Redundant' column, use can see exactly where every column is used:

Download

[wpdm_file id=3]

Please use it and reply with comments

'The use of the tool is on your own responsibility'