A Fabulous new Excel Add-In for Power Pivot

I’m really exited to share with you a new Excel Add-in that was authored by Bertrand d’Arbonneau and has recently been made available via SQLBI.com The Add-in called Power Pivot Utilities combines a number of existing tools into a single new tool bar in Excel as shown below (Excel 2013 and 2016 only). Everything could already be done using various bespoke existing tools and procedures, but there is huge value by bringing them all together into a simple to use UI like this. This new Add-In goes straight into my “must have tools” for Power Pivot in Excel.

image

There are currently 8 icons on the tool bar (edit: plus there are some hidden gems in the context menus). Let me explain what each item does.

1. Insert Pivot Table

This solves a problem with Excel 2013 where it is difficult to insert a new pivot table that is connected to the data model. I have previously provided VBA code to solve this, but PP Utilities adds this feature as part of the toolbar suite – nice.

2. List Table Relationships

This feature is completely new to me although I guess it could be done already using DAX Studio. It simply creates a new tab with a table that summarises the relationships in your data model. Great for simple documentation or just to check what is going on.

image

3. List Measures

This works the same as with Relationships. It creates a new tab and extracts all the Measures from your model in an easy to read table. But just wait for the killer button 6 further down the page!!

image

4. List Calculated Columns

I am sure you are getting the hang of this by now. Same as above but for Calculated Columns

image

5. Model Memory Usage

Takes the VBA code work done first by Kasper de Jonge and then Scott Senkeresty to extract the information from the underlying data model so you can see where all your space is being consumed in the data model.

image

6. Format Expressions with DAX Formatter

I love this one. It uses the DAX Formatter API to format the DAX code you extract in steps 3 and 4 above. After you do this, the DAX is so much easier to read.

image

I use the same API as I use to format DAX code posted by users in the forum at http://powerpivotforum.com.au

7. DAX Studio

Simply launches the DAX studio application. You will still need to install this application to use it – you can download it here. You can of course run the application by going to the Add-In menu, but it is nice to have everything on the one toolbar.

8. Help

Finally there is some really good quality help on each of the tools which is a really nice professional touch.

image

9. Now for those hidden gems

Pivot Table Right Click Context Menu

Edit: 30/1/16

As Dominik pointed out in the comments below, there are some hidden gems. After installing the Addin, you will have some new menu items on the right click context menu. Just select a pivot table and then right click. Note in the image below the before and after picks of what the Pivot Table context menu looks like.

after

My 2 favourite additions are the first item and the last item. The first one will format your pivot table in the old tabular layout – something I do a lot.

The second one adds data bars to the pivot, something I also do a lot.

Sheet Tab Right Click Context Menu

When you right click on the sheet tab, you will see 4 new menu options.

sheet

Slicer Right Click Context Menu

When you right click on a slicer there are more hidden gems. I really love the “Display Slicer Source” – very helpful
slicer

A Final Word

I have previously shared all of these tools and tips as bespoke solutions, but Power Pivot Utilities nicely combines them all together into a single set of value adding tools. I highly recommend it and you can download it here.

January 26, 2016 7:00 am

6 Comments

  • Thanks for the article Matt, very useful indeed.
    I read all their articles, but I don’t often go into the utlities section in SQLBI.

  • Wow! This will be great. I usually cut and paste my DAX formulas and do screen shots of relationships. This will really help the business users to check their workbooks without getting programmers involved.

  • Wow thats just the tool i was looking for to ‘Display the slicer source’. Just occasionally you get the same field name in different tables which you then use as a slicer but then you can’t remember from which table the field you are slicing on came from. This resolves that.

    Please one suggestion which i think would be a wicked tool. Please create such that when you right click a Pivot Table there is the option ‘Display slicer names that are connected to this pivot table’. Just i have been working on a big report that used a master slicer tab in excel for selecting many slicers driving many different pivot tables across the report. Managing & checking which slicers connect to which pivots was mayhem. I know you can select the slicer settings to see which Pivot tables it is linked to but it is not possible the other way round from the point of view of the pivot table.
    If this option is currently possible then please someone enlighten me where & how.
    Crazy as it may sound but the way I did it was within the pivot table to use convert to OLAP formulas where then in the formula you can see the name of the slicers

  • Bertrand d'Arbonneau

    Good suggestion. I will add this feature to the next release. So far, the code I am working on is the following:

    Sub DisplayConnectedSlicers
    Dim oSlicer As Slicer
    Dim oPT As PivotTable
    Dim strSl As String
    On Error Resume Next
    Set oPT = ActiveCell.PivotTable
    If oPT Is Nothing Then Exit Sub

    For Each oSlicer In oPT.Slicers
    strSl = strSl & “‘” & oSlicer.SlicerCache.Name & “‘ on sheet ‘” & oSlicer.Shape.Parent.Name & “‘” & vbCrLf
    Next
    If Len(strSl) > 0 Then Call MsgBox(Trim(strSl), vbOKOnly, “Connected Slicers”)
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

Bring your data to life - Let's discuss how Matt can help!