SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

PowerPivot Compatibility across versions

There are several versions of PowerPivot available and starting with Excel 2013 there are also several versions of Excel. It is useful to look at the compatibility between the different versions of Excel and PowerPivot available now.

As a general rule when you have a PowerPivot workbook saved with a specific version of PowerPivot:

You can upgrade a workbook to a newer version of PowerPivot

You can upgrade a workbook to a newer version of Excel

You cannot open a workbook using a previous version of PowerPivot

You cannot open a workbook using a previous version of Excel, if it contains PowerPivot data

First caveat: you can open an Excel 2010 workbook containing a PowerPivot data model in Excel 2013, but once you save it in Excel 2013, you can no longer open it in Excel 2010. This is because a different file format for PowerPivot data used by Excel 2013 that cannot be understood by Excel 2010.

Second caveat: A file saved in Excel 2010 with PowerPivot 2012 (RTM or SP1) cannot be opened by an Excel 2010 running PowerPivot 2008 R2.

Each workbook has a compatibility level for PowerPivot data that corresponds to the PowerPivot version used to save the data. Here is the list of currently available compatibility levels:

1050 (2008 R2)

1100 (2012 RTM/SP1)

1103 (Excel 2013)

The following table tells you what happens when you try to open an Excel 2010 file in a certain compatibility level (columns) with a certain version of PowerPivot (rows):

1050

1100

2008 R2

Read/Modify

Not Supported

2012 RTM

Read/Upgrade++

Read/Modify

2012 SP1

Read/Upgrade++

Read/Modify/Upgrade

++ It is important to call out that it is *not* supported editing 1050 PowerPivot models in the 2012 release – you *have to* upgrade the model to 110x before you can edit/refresh the model.

A detailed list of errors you can have opening different a workbook with a different compatibility level than the current version of PowerPivot you have is available in the article Version compatibility between PowerPivot Data Models in Excel 2010 and Excel 2013. As the article mention, PowerPivot for SharePoint can open PowerPivot workbooks of previous compatibility levels, but in order to upgrade the data on the server (scheduling a data refresh) you need to upgrade the workbook to the newer format.

You have to be careful using PowerPivot in mixed environment, especially when you want to share an Excel file containing PowerPivot data. Once you refresh PowerPivot data, you need to upgrade the compatibility level if you opened the workbook with a newer version of PowerPivot, and once you do that, your colleague with a previous version of PowerPivot can no longer open the same workbook. Saving the file on SharePoint makes it possible to navigate into data, but you need a correspondent or newer version of PowerPivot on SharePoint than that used to save the file.

For these reasons, when we provide the examples for our PowerPivot Workshop and our book for Excel 2010, the sample files are saved with the older version of PowerPivot (2008 R2). Now that we are working on the Excel 2013 version of the book and of the workshop (soon to be available!), we are finally going to release samples using a newer version of Excel and PowerPivot.

Comment Notification

Comments

Bought several different PowerPivot books and trying to learn, but have not been too successful. In spite of the hype and all the books written by so many authors, PowerPivot just does not seem ready for prime time use. The idea is good, but so many issues, and so much time required to track errors down--almost not worth the trouble--and now find it difficult to trust PowerPivot.

I am in a learning mode, so not mission critical. If I were to rely upon PowerPivot to build a real application, it would be problematic.

So much promise heralded in the books, and such potential, all wasted in the poor execution. Good thing it is a free add-in. Very disappointing. No wonder the other vendors are still doing well. MS had potential of being # 1 in self-service BI with PPVT, if it worked well.

I am running Excel 2010 32-bit on Vista 64-bit with 16 GB RAM and Quad-Core CPU, so it is not that my machine is inadequate.

my name is Kuba and I hope you could help me with issues I encounter with Powerpivot. I created a report based on a simple data model. It has a significant amount of data, however I thought it is still acceptable from Powerpivot perspective. The file is around 100MB.

I use a machine with 8 cores and 16 GB of RAM. However, if I start playing with different attributes from data and move them around ROWS, COLUMNS and FILTERS, I end up with Excel eating almost 15GB of RAM. Then, I start receiving strange errors in Powerpivot about expired sessions etc. It seems as if the tool was quite unstable. What I also noticed is that Excel does not release the memory. It does not matter if the report has complex layout and filters or it is as simple as possible - Excel will occupy those 15BB of RAM as long as I reopen the file. Any ideas why it is like that? Is it a memory management bug?

Kuba, the issue you describe might be related to some measure that materializes data in memory during query. If this is the case, you have some measure in the data model that could be optimized, but it's hard to make such a diagnosis without analyzing the data model in detail.

If the memory raise up also on simple measures such as SUM(table[column]), then the cause could be something else.

In Excel 2013 I observed some instability (and I hope that a Service Pack will be released soon for that) but it's the first time I heard about memory consumption so huge - unless it's caused by some measure.

I am facing problems regarding the stability of the Power pivot that it keep crshing on me & I am loyhe data. many times I have faced the message (Document not saved) & always when I close the power pivot window the whole excel sheets is crashing & the windows asking me to explor the internet to find a solution & every time no solution appears.

Which version crashes? You should open an incident with Microsoft Support for this type of issues in order to get some assistance. I have experienced some instability but more with Excel 2013 than with Excel 2010.

Badrul, this is strange. PowerPivot in Excel 2010 is more stable than in Excel 2013, at least if you use the latest build of the PowerPivot add-in (currently SP1 CU5). If you have a case that you can repro, I suggest you openin an incident with Microsoft support, they usually are able to identify and solve this type of setup issues.

We have upgraded to Office 2013, specifically for the new features and capabilities in PowerPivot 2013. I am rather disappointed though because upon refreshing data the report doesn't want to save, or give various other error reasons. I find myself having to repair my office 2013 constantly.

Hi Marco, I've been tasked with creating a powerpivot self serve solution for our organization and I wanted to know if there are any compatibility issues between 32-bit and 64-bit created files as we have multiple people on a combination of both 32-bit and 64-bit platforms

ie. if I create a powerpivot with 32-bit, is someone with 64-bit able to open, use, manipulate the data?

moving from 32 to 64 bit and from 64 to 32 bit is not an issue (you might have not enough memory available in 32 bit if the workbook saved in 64 bit is very large - where large means an Excel file larger than 250MB). The problem is PowerPivot compatibility between different version of Excel, as described in the blog post.

I have just come across your Blog in search of solution to an issue with Excel 2013 x64 and Powerpivot in fact I have had the same problem across Excel 2010 and the 2012 addin.

Do you now why the data connections, specifically one is to MSAS Cube on an SQLServer and the other is to a Teradata DB, WILL NOT save the connection passwords. The appearance is that the passwords are saved but when a Refresh is done the password is requested each time .Only while still in the Data Model will it connect and refresh.

Once loaded up to Sharepoint 2013 it is useless because it can't be refreshed.

Is it possible to schedulate in Office 365 a data refresh (through gateway configuration) for an Excel 2013 power pivot with a SQL Server 2008R2 data source? Or do I necessary have a Sql server 2012? That is: is it possibile to offer the customer an Office 365 Power BI solution without upgrading to SQL 2012?

PowerPivot=Garbage, until the instability and crashes are addressed. Can't change Measure names after-the-fact (if you change a few at once, all measures break with a yellow error icon), too many add-in crashes where the file can't be saved, and you basically have to nuke your installation of Excel-- and this is just after creating some pretty basic things and not really pushing any limits. Maybe in 2 or 3 years after MS stabilizes things, it could be more reliable, and therefore more useful. Right now, though (and this is after 2 YEARS), it still feels very beta. It's one thing for a Google web service to feel beta, but for hardcore business backend data analysis app to feel beta like this, it's pretty unacceptable, IMO..

Morgan, I wouldn't agree with the definition of garbage :-) (many companies actually use Power Pivot in production), but I agree with the stability issue that affects Excel 2013 version (Excel 2010 seems better).

My current organization use Microsoft office 2007. Since from my previous organization I m hands on working on power pivot so requested them and got a single desktop licence version of Microsoft Office standard 2010. I have been facing issue in installing the power pivot even after installing the .Net Framework4 and Visual for 2010 but excel is not opening up now. It opened only once and I cud see powerpivot icon in the excel (making me happy just for that moment). I closed the file and opened again but since then excel is just in starting... mode. I have done several uninstall/install process but nothing is helping me.

I am trying to connect to a SQL Server 2014 Express install from Power Pivot Excel 2010, but it says that the SQL Server instance doesn't exist. Shouldn't I be able to make that connection or is there probably some other issue going on?

I don't have a machine to repro - I have Excel 2010 on machines that also have SQL 2012 - so if the problem is some missing driver, I cannot repro. However, if this is the issue, you might try installing OLE DB driver from SQL Server 2012 (search for SQL Server 2012 Feature Pack)

Sometimes when opening a file it tells me that Excel was able to open the file by repairing or removing unreadable content, at which point I see that some pivot slicers have been removed. Based on a proposed solution I found, I've saved my file from binary to macro enabled workbook and back to binary, but I'm worried about whether this will be a permanent fix or if it will just recur at some point in the future.

Do you have any insight into the cause of this issue and whether the fix I have tried will actually suffice?

Our company has excel 2010 and SharePoint 2013. I want to use power view and power pivot. I am confused as what I can install. I was doing some reading and I believe I can't use power view in SharePoint 2013 cause of the Excel 2010 limitation. Can you let me know what I can install?

it is not an ideal situation. You should be able to upload a Power Pivot 2010 document to a SharePoint 2013 Library and then use Power Pivot for SharePoint - but every time your workbook will be upgraded to Excel 2013 by SharePoint, so you will be no longer able to download and edit it without Excel 2013. However, it should work in one-way only, but you have to install Power Pivot for SharePoint too (which requires SharePoint ENterprise + SQL Server 2012/14 Business Intelligence or Enterprise edition for the licenses).

Thanks for your great blog. I have created an Excel Sheet in Excel 2010 with Powerpivot add in. My customer can open the file, but get the following error message: "Initalization of the data source failed". I have tried to open the file in Excel 2013 and after upgrading the file, it works fine. Is it probable to assume that my client has an earlier version of powerpivot for 2010 (for example for server 2008 instead of 2010?. We both use 32-bit versions. It can be noted that neither of use any server connections to Sharepoint.

Additional info for my earlier post. My version is 11.0.3000.0 and his is 10.50.4000.0 (that is server 2008 ver) I guess these are not compatible and the solution is for him to install 11.0.3000.0 (that is server 2012 ver)?

I would like to thank you for writing the most honest book I have read on power-pivot, I was in entice into upgrading to office 2013, with the understanding that PowerPoint would provide access to millions of rows of data have read "powerpivot for data analyst" and "power bi with excel 2013", what I did not understand or grasp is the fine print that reveals that while you may be able to load a millions rows of data into the powerpivot, power query,myou would not be able to extract said data in any mean full way. Hence drill down is limited to 1000 rows. "Microsoft excel 2013: building data models with powerpivot" was the only book I am across with clearly outline the limitations of powerpivot, regarding drill-through options. Considering office 2010 allowed the customisation of the drill-through into raw data it seems rather odd that excel 2013 does not have this feature. I can only assume it is a marketing decision ?

As a reporting tool excel 2013 is great, but to what would you suggest as a tool that enable one to manipulate data in a meaningful way. Ie I am able to fully access the transactional data the is reflected in the report. I am not a computer expert by any means, but I have a good understanding of excel and use macro in excel to create reports that support both operational and reporting requirements.

My definition of a good report is one, that managers and expeditors can both work from. For example, we run a outstanding orders and urgency orders are flagged up and expeditors are able click through and chase those orders, hence managers and expeditors work of the same reports.

not sure about what is the question. Excel 2013 has a different architecture and for this reason you have the limitation in drillthrough. I think we'll see something changing with the next release of Excel and the upcoming Power BI - but I would like to get the same answer from Microsoft as you! :)

Thanks for the clarifications. I have a question regarding our setup. We have SharePoint 2013 with PowerPivot working fine. Then we have some collegues running Excel 2010 and some 2013. I'm aware of the ones using 2010 can't open and edit the data model. But shouldn't they be able to connect to the model with excel?

Well, some can I Believe, but for some the "Data link properties"-dialog box opens or when trying to refresh get a error saying they can't connect to the data source.

Leave a Comment

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.