PowerPivot for Excel 2007?

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:

Slicers - One Reason Why PowerPivot Will Not Work With Excel 2007

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:

Embedded Data - The Biggest Reason Why PowerPivot Will Not Work With Excel 2007

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?”

19 Responses to PowerPivot for Excel 2007?

  1. Boyan Penev says:

    To add to this post, if users have Excel 2003 they can benefit from PowerPivot by connecting to a published to SharePoint model directly as a SSAS instance – therefore, they can still slice/dice models with no need to open them through SharePoint at all. Recently, a client indicated that this is a very important feature as they (just like many corporations in Australia) are still using Windows XP + Excel 2003 in their SOEs.

    Interesting why this does not get more attention around the blogosphere – in my opinion it is a very interesting and useful feature of PowerPivot and it could successfully be used in marketing the product.

  2. Tom Rupsis says:

    Unfortunately, for those of us in small biz markets, the Sharepoint 2010 option isn’t all that great. Considering you have to purchase Sharepoint 2010 (not Foundation) and SQL Enterprise, it really doesn’t matter that it can run on a decently equipped workstation. :-(

    • Tom, the fixed server cost was the dealbreaker, right? Because a handful of CAL’s would be cheap.

      Just asking for my own benefit, so that I better understand the obstacles. Not suggesting that you are wrong about the price.

      • Tom Rupsis says:

        Right. SQL Server CALs and Sharepoint CALs aren’t that bad. But a SQL Enterprise server license I think is like $7500 or so and Sharepoint server is about $5k (definitely not a licensing expert so I don’t know actual prices off hand). Add on $150/CAL for SQL and $100/CAL for Sharepoint and you’re up to $20k for a 30 user system…and that assumes that they already have a 64 bit server with capacity to handle Sharepoint and SQL. It’s just a very different world when you’re dealing with small companies in small markets. They can really benefit from the tools, but not usually at a level that makes that kind of price tag feasible. Some day I’ll make it back to a bigger market… :)

      • Tom Rupsis says:

        I should have said, though, that the whole Sharepoint hurdle isn’t stopping me from finding uses for PowerPivot and demonstrating them to clients as justification for upgrading at least some of their users to 2010. :)

  3. Ken Puls says:

    Rob, with regards to your comment “Excel 2010 had to invent a mechanism for allowing other applications to store their data inside of workbooks…”

    The key point in this is “other applications”, correct? Excel can obviously read/write to the file, including all the RibbonX functionanility.

    I’m a little suprised by this, as I would have assumed that, being an Excel add-in, it would have direct access to the file just as Excel did…

    With regards to the fixed cost issue you and Tom were discussing, I just got a price from my supplier on software. SQL Enterprise (no CAL’s) is approx $11k. Sharepoint (no CAL’s) is approx $6.5k. Both those are Canadian dollars, so that’s $17.5k plus user CALs before you even get started. For both CAL’s at about $250 per user, our 30 person office would come in over $25k before the cost of hardware and install labour. I’d estimate the latter, to get it done right, would probably be about 4-5k per server. (Good techs are not cheap.) So we could easily be up to $35k without adding hardware. (Never mind training and ongoing support.)

    That’s the real obstacle to the Sharepoint install for small business. Unless you have a large pool of users to dilute the per user portion of the fixed server costs, it’s just not feasible.

    • 1) Yes, we thought that was the case, too, given that the addin runs in the Excel process. My memory has gone hazy already on the specifics, but when we tried that out, it did not work well. I seem to recall the reason being that the only way for the addin to get to the data was to have Excel load the data up front, which meant slowing down file load by a LOT, even if you weren’t planning to access the PowerPivot data. Oh, and it meant that the PowerPivot data would be duplicated in RAM – Excel would have a copy, and the addin would have a copy (only the latter being used). So RAM usage would have been 2x – unacceptable for large streams like PowerPivot data.

      We needed a mechanism that Excel itself never loaded, but that Excel could “hand off” to other code on request, and thus began quite an unexpected little odyssey. Along the way we discovered MANY little “gotchas” that needed fixing as well, things that would not have worked well “as is” either. It ended up being, as I said, one of the most impressive feats of engineering (and tenacity) that I ever witnessed at MS.

    • 2) Thanks Ken, that correlates well with Tom’s observations as well. Some components of the price DO seem to fluctuate depending on where you get the quote from, others do not. When I researched this awhile back, I came up with different numbers for SQL Enterprise. Regardless though, it does seem to always be in the same ballpark.

      • Ken Puls says:

        For sure. You can also get a “per processor” license for SQL server that allows ulimited connections are removes the need for CAL’s. The cost of that one is about $35.5k CDN. (That seems high, but that’s what I was told.) Even if you figure a CAL at $175, that’s a breakeven point of 140 users.

        I do have a per proc liense here, and don’t use CAL’s, but I know I didn’t pay near that much. It was a few years ago though, and the breakeven price for users was also way lower. If it wasn’t, I would have licensed on a per user basis…

  4. Cam McPherson. says:

    Thanks for the article.
    Powerpivot looks like a very useful piece of software, and would be great to integrate into our analytical repertoire at work. Unfortunately, we use 2007, and that’s not going to change any time soon.

    Without knowing the dependencies underlying the add-in, why could the developers not simply incorporate an upgrade into the Powerpivot install (ie: dlls / whatever else), thus making it compatible with 2003 and 2007?

    For example, @RISK does some crazy stuff for sensitivity analysis, and is compatible on all versions of MS Excel.

    As Ken Puls notes, a $35k system upgrade for a free (albeit fantastic add-in) is unacceptable.

    Why not charge a nominal sum for the add in, and make it an all-inclusive backwards-compatible bundle?

    The apparent contingencies are quite saddening from a desirous user’s point of view.

    • I understand the disappointment. The thing that surprises most people is that 95% of MS’s decisions are dictated by time and resources and not by nefarious strategy or clumsiness.

      In this case, Excel 2010 added one capability (embedded data) that was 100% technologically critical to the PowerPivot mission, and one feature (slicers) that was perception and usability critical.

      Supporting older versions of Excel would have meant a tremendous amount of additional work, to support an “all in one” file format version as well as a “two separate files” version, and then an even bigger explosion in the testing coverage required.

      I was part of those decisions when I was on the team, and I can say that supporting older versions would have been taken very seriously if it hadn’t been for the hefty work involved. That work would have come at the expense of many other features of PowerPivot v1 not making it into the release.

      Doesn’t make the lack of 2007 support any better, I know. Just trying to give you an answer to your question of “why didn’t MS do this.”

  5. Azarien says:

    I don’t really understand the Reason 2. It has been possible to embed other applications’ data via OLE mechanism since antiquity. Why wouldn’t it work for PowerPivot?

    • powerpivotpro says:

      Yes, the OLE DocFile format was quite a marvel. Still is really, but considering how long ago it was invented, it just seems even more impressive.

      I’m not sure whether the OLE doc format would have done everything we needed. Maybe, maybe not. The devil is always in the little details.

      But the 2007+ file formats are no longer OLE docfiles. They are essentially ZIP files.

      The 2007 file format WAS indeed designed to allow embedding much like OLE had supported. In the little details we discovered, though, it wasn’t designed well enough. No one had anticipated the storage of potentially 2 GB of data in the 2007 custom storage mechanisms. Nor had it been designed to be “marshalled” through various COM interfaces at runtime, delay loaded, etc. We were ALL quite surprised, in the end, how many steps were required to negotiate the handoff of the custom data stream from Excel to PowerPivot. Like you, I expected it to be a simple thing with perhaps no more than 2 steps in the code. We ended up with a 10-step process that I even had to flowchart to explain it to others. That wasn’t poor engineering, it was just a surprisingly complex problem to make everything work 100% smoothly for the user.

      So the 2007 XLSX file format had to be extended in 2010 to add the CustomData mechanism.

  6. schm0e says:

    I’m glad I found this site — it might provide the insight I need into the dark art of Higher Initiate Excel Functionality.

    Excel is a fabulous spreadsheet. As a database…it looks like an inverted pyramid. A developer’s nightmare.

    Just my $.002.

  7. schm0e says:

    If there’s one nefarious plot at MS, it’s the ongoing campaign to make the VBA developer obsolete. Me no likey.

Leave a Reply