When you try opening an Excel 2010 PowerPivot workbook in Excel 2013 you will find that you are unable to manipulate the PivotTable. In addition, you will be prompted with an error message that asks you to upgrade the data model:
However, when you try to enable the PowerPivot add-in, you may not see it in the COM add-ins dialog box. This problem can happen because in Excel 2013, PowerPivot and the two new add-ins – PowerView and Inquire, are only available on certain SKUs (versions). These include:
- Office 2013 Professional Plus (available through Volume Licensing, MSDN and TechNet subscriptions)
- Office 365 Professional Plus (available as standalone subscription)
- Office 365 Professional Plus (available as part of the Office 365 Mid-Size Business, Office 365 Enterprise Plan 3, or Office 365 Enterprise Plan 4 offerings)
Note: Power Pivot and Power View on the standalone retail 32-bit and 64-bit SKUs were added with the October 2013 updates. If you have one of these SKU's but do not see the add-in, please apply the most recent updates and test for improvement.
If you have purchased an Office 2013 or Office 365 SKU other than those listed above, you will need to return your product to the place of purchase or stop your current subscription to change to one of the products that does support these add-ins.
There are a couple of ways to work around this issue:
Access a machine that has one of the above-listed SKUs
If you have access to a machine that has one of the above listed SKUs, you can use the PowerPivot add-in on that machine to update the PivotTable and save the file. Once the PowerPivot data model is updated, then you can open and use the PivotTables on your other machines. Keep in mind that once updated, you cannot use the PivotTable in Excel 2010
Recreate the Workbook in 2013
If you do not have access to a machine with the PowerPivot 2013 add-in, you can rebuild your PivotTable in Excel 2013. There is no way to update the model, so you will need to import your data again and recreate the workbook from scratch. The data model engine is a part of Excel 2013. To do that:
If you have tables in Excel that you want to use, do the following.
- Select a cell in one of the tables.
- On the Insert tab, click on PivotTable.
- Check Add this data to the Data Model.
- Click Ok. Your PivotTable will be created.
- In the Fields List, click the All tab. At this point you can add data from your other tables.
- You may need to create relationships between the tables. You can do that on the Data ribbon or the PivotTable Tools/Analyze ribbon.
- If you are importing data from multiple tables in a single external data source, do the following.
- Choose to use an external data source.
- Choose a connection that includes multiple data tables. When the PivotTable is created, the data will be added to the data model automatically.
- In the Fields List, click the All tab, and add data from your tables.
- If you have mixed data, do the following
- Import the data into Excel tables first.
- Create any necessary relationships.
- Follow the instructions in item 1.
For more information on Excel's built-in data model, see the following blog: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx
Power View and Inquire add-ins for Excel also require the same SKUs of Excel that Power Pivot does.
References: http://blogs.office.com/b/microsoft-excel/archive/2012/10/04/intro-to-power-view-for-excel-2013.aspx & http://office.microsoft.com/en-us/excel-help/what-you-can-do-with-spreadsheet-inquire-HA102835926.aspx.
Power Map COM add-in for Excel, also requires the same SKUs of Excel that Power Pivot does, but is not included in the stand-alone version nor in the MSI versions of Excel. This add-in is only available with an Office 365 (O365) subscription of the correct SKU.