When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010. After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office. Unfortunately they ran out of time to write in all the features into the Office 2013 version – consequently there are a number of features that are actually better in Excel 2010 plug in version than the Office 2013 version.
In 2010, it is easier to edit your measures
In Excel 2010 you can right click on a measure in the Field List Window and select “EDIT”. In 2013 you have to open the Manage Calculated Fields dialog and then find the measure you want to edit.
And 2010 is easier to insert a Power Pivot Table
In Excel 2010 there is an “insert pivot table” button on the Power Pivot tab in Excel. Just click on this button to create a new Pivot Table that is connected to the data model. No such button exists in Excel 2013 (unless you go into the Power Pivot window.
The alternative to navigating to the Power Pivot window is you have to use 8 clicks to insert a table (Shown below).
The Solution – Some VBA to Add a Pivot Table
I have been thinking of writing some VBA code to replace this missing insert pivot table connected to the data model button from Excel 2013, and I got to it last week. It is a very small amount of code that makes the process very easy. Just copy the code into your personal.xlb workbook and then add a shortcut to the code in your quick launch menu. You can then use this VBA to Insert a Power Pivot Table into any existing worksheet.
Edit: This was my original code
Sub InsertPPTable() ' 'capture the starting cursor location myAddress = ActiveCell.Address myAddress = ActiveCell.Address X = ActiveWorkbook.PivotCaches.Count ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _ ActiveWorkbook.Connections("ThisWorkbookDataModel")).CreatePivotTable TableDestination:=Range(myAddress) Cells(1, 1).Select Range(myAddress).Select End Sub
Improved code from Dominik
(see comments section for background)
Sub InsertPowerPivotTable() Dim PowerPivotCache As Excel.PivotCache Dim NewPowerPivotTable As Excel.PivotTable ‘ Create new cache Set PowerPivotCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlExternal, _ SourceData:=ActiveWorkbook.Connections(“ThisWorkbookDataModel”), _ Version:=xlPivotTableVersion15) ‘ Create PivotTable Set NewPowerPivotTable = PowerPivotCache.CreatePivotTable( _ TableDestination:=ActiveCell, _ DefaultVersion:=xlPivotTableVersion15) ‘ Settings for new PowerPivotTable With NewPowerPivotTable .RowAxisLayout xlTabularRow .HasAutoFormat = False End With ‘ Cleanup Set NewPowerPivotTable = Nothing Set PowerPivotCache = Nothing End Sub
Here is what it looks like to insert a Pivot Table linked to the data model when using my VBA code.