I see this question a lot. Excel 2010 sure looks a lot like Excel 2007 – they both use the Ribbon instead of traditional menus, and they both use the new XLSX file format. So I can understand why it seems like 2007 should work.
I’m sad to say that PowerPivot is NOT supported, and simply will not function, with Excel 2007.
Stuck on Excel 2007? Not all hope is lost…
First off, you can get a trial version of Excel 2010 by clicking here. If you are just in an evaluation phase right now, that likely will help you quite a bit.
The next thing to remember is that your entire organization does NOT need to upgrade to 2010. Only the PowerPivot authors need to have 2010, and then they can publish their work to SharePoint 2010 (and servers are often upgraded long before desktops). So this *might* help.
(Also keep in mind that your entire org does NOT have to adopt SharePoint, either. A single “departmental server” running on a reasonably capable desktop computer can do the job quite nicely).
Lastly, even your PowerPivot authors can still run 2007 for most tasks, and have 2010 installed side by side for PowerPivot tasks. That is sometimes a useful concession to IT.
OK, so why IS Excel 2010 required?
Is this some nefarious MS plot? Not this time. There are good reasons why 2007 is not supported, and I worked closely on two of them while I was at Microsoft.
Reason 1 – Slicers
The PowerPivot team (of which I was a member at the time) decided that PowerPivot was not going to “shine” in customers eyes with the existing appearance of Excel. A more engaging, “Fisher Price” style of interaction with a finished report was required.
(I can confirm the truth of this from my experience on the Great Football Project in 2006 – our focus group participants BADLY needed some sort of modern-day filtering/exploration method, and Excel 2007 simply didn’t have it).
So the PowerPivot team actually “donated” a bunch of people, for about two years, to the Excel team in order to build the Slicers feature into Excel 2010 – as illustrated by Region, Territory, and DR-ST below:
Reason 2 – Embedded Data
The second reason is the bigger one, the one that made it impossible to support Excel 2007.
It was very much a requirement that PowerPivot be able to store its data inside the XLSX workbook, rather than in a separate file. Imagine, as an Excel user, if you were told you had to lug two files around everywhere just to make your pivottables work.
So, Excel 2010 had to invent a mechanism for allowing other applications to store their data inside of workbooks, and for allowing that data to be *fetched* by those applications WHILE EXCEL IS RUNNING, which um, normally locks other applications out of the file.
This was some of the most imaginative engineering I have ever witnessed, on the part of some amazing engineers like Shahar Prish and Raman Iyer. (My role was essentially High Priest… and Drawer of Pretty Diagrams).
So, today, you can take a PowerPivot workbook, rename it from .XLSX to .ZIP, and then navigate into XLCustomData and see a file named Item1.data:
Anyway, hopefully that clears up the questions – “does PowerPivot work with Excel 2007,” “what do I do now that I know it doesn’t,” and “why DOESN’T it work?”