Short Version: You Want One of These 🙂

Office 365 ProPlus

Office 365 E3

Office 365 E4 andE5

Long Overdue Post!

2016 power pivot - what versions of Office Contain It?

You Want This, But What Versions Have It?

Awhile back, I posted about all of the cool improvements in Power Pivot 2016 as compared to 2013.  This followup helps you decide what version of Office 2016 to purchase.

Versions of Office 365 that DO Contain Power Pivot

Power Pivot *IS* included in the following Office 365 Plans:

Office 365 ProPlus

Office 365 E3

Office 365 E4 andE5

Good News:  It’s Also Back in Pro Retail!

For those of you who prefer non-subscription versions, good news.  Unlike the 2013 debacle, Office 2016 *does* offer Power Pivot in the Pro SKU that’s available for retail purchase:

Purchase and Download Office 2016 Pro from Amazon Here

What About Excel Standalone?

YES, Power Pivot WILL be included in the Standalone Excel SKU as well, but that is not available yet for purchase.

UPDATE: Power Pivot in Excel 2016 Standalone is now available here!

What Versions of Office 2016 do NOT Include Power Pivot?

Just to be clear, here are some Office 2016 versions that we are positive do NOT include Power Pivot:

Home and Business

Home and Student

Office 365 Home

Office 365 Personal

Office 365 Business Essentials

Office 365 Business

Office 365 Business Premium

Office 365 Enterprise E1

There also may be other versions that don’t include Power Pivot (for instance we are currently unsure about Office 365 University), so please don’t treat this as an exhaustive list, but those above are guaranteed NOT to have Power Pivot.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 71 Comments

  1. I would add one more point to this. For BOTH Office 2013, Office 2016, the “Data Model” is actually built into the Excel fabric, itself. So any edition of Office 2013/2016 can interact with a PowerPivot Excel Workbook (open it, click on slicers, change the pivot table etc.). Only when you attempt to “author” the Model – would you need the correct version of Office, which includes the Power Pivot authoring functionality (the PowerPivot window, which lets you define relationships/measures etc.).

        1. Refreshing the data requires interaction with the model, but this is not a problem, just do this:

          The Author who has full authoring privileges should update and refresh the model on a regular schedule (maybe every morning), save it on a shared drive and that way everyone without authoring functionality can see the latest version and interact with their reports.

  2. Avi, so if I author a PP solution in 2013 or 2016 and pass it along to someone who doesn’t have PP, they’ll be able to use it? However, if there are modifications that need to be made, I’ll have to be the one to make them?

    Also, my understanding is that none of this (Power Pivot, Data Models, Power Query) exists on the Mac side, and will not function on Excel Online.

    1. Thanks to Rob & Avi for this helpful and clearifyng information. Searched for this and found it here. Thanks also to DataScopic for his summarize. What about the statement of DataScopic that none of the BI-Features exists on the Mac side. Is there anybody who can clarify this. Thanks in advance.

  3. Sorry, @MS but as great as the PowerPivot functionality is, here you showed (again) that you have no clue how to deal with your customers. Is that functionality so heavy and complicated to maintain that it is removed from a lot of Excel versions? Even ones which bear the word “business” in their title? This is simply ridiculous. Strange marketing strategy to promote a “new” technologiy (and yes – for a lot of users this is still new) by making it available only to some selected few.

    Who ever needed to go through the pain of dealing with Microsoft’s customer interfaces will recognize a pattern here. Amazing product but extremely poor marketing approach!

    1. Funny Phil, but I think most of the MS engineering teams would agree with you, at least off the record.

      I look at this and say “well at least it’s better than the 2013 story.” Progress, however incremental, is appreciated.

      1. @Rob thanks … yes, compared to the previous disaster you’re of course right. As I’m an O365 subscriber a few $ more for this massive added functionality is nothing that hurts me really but it still is something that is sooo hard to understand 😉

        1. It’s easy to understand – GREED. They’re trying to force consumers to purchase the more expensive product. It’s as simple as that. Marketing’s only goal is profit. Why do you think now they’re trying to force users to “subscribe” to monthly fees or pay outrageous up-front costs for the software? And some programs like MS Access you can ONLY get by subscribing. They don’t care if the software works or doesn’t work, whether customers are happy or not. That’s the downside of a monopoly: customer service blows.

          1. Being ex-Microsoft, and having worked on Excel, I can tell you that this comment is ridiculous. Less than 10% of Excel users even use PivotTables, much less PowerPivot. I do get why they make that decision, and the answer is support. It has very little to do with who should use this feature and who shouldn’t, it’s about a very complicated feature that can require a lot of customer support and you can’t give that type of special attention to a customer who is paying you $10/mo., it’s not greed, it’s cost effectiveness.

            Greed makes a much more compelling troll comment tho.

        2. I amend my statement: you can get Access in the non-subscription Office 2016 Pro, of course the most expensive package.

  4. Hi Rob, It’s funny that way but Excel standalone is on sale in the Danish version of Microsoft Store. I haven’t actually tried to buy it so I don’t really know whether that’s a mistake or not.

    I know that Microsoft, Apple and other companies sometimes use the Scandinavian countries as “testing areas” before releasing products and ideas more broadly, it might be due to that possibility.

    http://www.microsoftstore.com/store/mseea/da_DK/pdp/Excel-2016/productID.324449500
    http://www.microsoftstore.com/store/mseea/da_DK/pdp/Excel-2016-ikke-kommerciel/productID.324450100

    ‘Køb og download nu’ means ‘Buy and download now’ as seen on the above website links

  5. I as a O365 business premium subscriber still can work in PP in 2013 but no more if I upgrade it to 2016….Very unfortunate.
    Can I have in one subscription account different user subscriptions? Like one business premium and other business enterprise E3 subscription.

  6. Helpful to know. I have been asked for an answer to this topic and it is great to have this post now as a resource!

    Regarding the 2016 edition, does this also mean that whichever version has Power Pivot has Power View also?

  7. This week I purchased a download of Excel 2016 non commercial standalone from the MS South Africa store and despite lengthy online denials from one of their agents it does contain power pivot.

  8. I can confirm that Office 365 Home Premium 2016 version does not have it even though the advertising for this states the programs are all “full” versions. Once of the reasons I went with Home Premium rather than just Home. I will have to go and check but I think the Office 365 Home Premium 2013 version had it – I know I discussed this with the Microsoft representative on the phone before signing up.

  9. What is also “interesting” is that Office ProPlus 2016 isn’t going into “standard release” until February.
    I just reimaged my old “test platform” laptop, and went to reinstall 2016 using the proplus license I have through my office – but there is no option to do so! I could have sworn I installed a copy using that license while 2016 was still in preview… grrrr. I now have 2016 models I can’t access… yes, I know there is a “First release” route to gaining access, but it will take me at least a week if not longer to try to negotiate such a thing with our IT department.

    1. Hi Eric,

      I also have a O365 ProPlus-License. Yesterday evening I tried to install the Office 2016-version. But first there was only an installation-option for the Office 2013-version. I send a support-request to MS and I got a call within 20 minutes (bad information on O365-Admin-Portal but quick support – wow).

      The following steps helped me to install Office 2016:

      Login to Office 365->Service settings->Updates->Click on First release Entire organization
      Sign out and sign in again
      Go to Office 365 settings->Software->Install Office 2016

      After doing these steps, below the installation-option for Office 2013 there appears a second installation-option for Office 2016.

      I’m a freelancer, so I’m my own admin for O365. I hope your company-admin is a helpful colleague, probably he opens the door (First release entire organizsation) for a moment, so that you can install Office 365.

      Good luck!

  10. I seem to have come across one issue and that is that it seems that Excel 2013/2016 Standalone seem to exist in a no-mans land between the sales to business and the sales to consumers sides of MS. I have spent six hours today trying to get an answer as to whether I can replace the version of XL that is part of Office 365 Home on one of my machines (the one I use, of course) with XL 2013/16 standalone. and, if I can, how. Everybody I spoke to at MS (at least four on-line chats and 5 or 6 phone calls each of which included conversations with two or three people) said they could help but no-one actually could.

    Until at last I get a straight answer – having been directed to the “Business” and “Pro” sales teams all day eventually someone points me at the consumer people (this is the UK). “No I don’t think you can replace the XL component of home with standalone. You can try it using the ‘office deployment tool’ (which is meant for ProPlus and other business versions), but it probably won’t work. However, it should be possible to have both versions of XL on the same machine.Install Office Hoe 365 and then delete the short-cuts to the 365 Home Version of Excel”. Then install Excel 365 Standalone”.What about file associations I said, when I open an XL file which version of XL will be opening it, “probably the Office 365 Version” he said, “you’ll probably just have to live with that”. “If you buy the standalone through the MS portal then if it doesn’t install you can have your money back”.

    Six, no seven hours to get a straight and intelligible answer.

    1. I have a desktop version off Office 2013 Home and Business which does not have the PP add-in. When I installed the Excel 2013 add-in it installed over the existing Excel and the PP add-in became available.

  11. Well I bought and tried Excel 2016 standalone and I’ve quickly un-installed it and got a refund. First it didn’t seem to be able to reliably update the data model of workbooks created in 2010/V2 to the new version, some worked, some didn’t. More crucially, when it does update to the current version that book is no longer editable in 2010/V2. Those people that I know of who have PP at all generally have 2010/V2.

    Aside from that rereading Rob’s assessment of Excel 2016 most of the most welcome improvements seem to reverting 2013 to how it was at 2010/V2 – so why not go just back there. The only thing I’ll really miss about 2013/16 is that it can address two screens in one instance -I like that, but can live without it when using PP (I still have XL2016 as part of my Office 365 home subscription). The two functions for comparing what’s in one list with what’s in another Rob mentions also look useful, however, Access can do that. So, all in all I’m going to keep Excel 2010 on my machine and run that with PPV2.

  12. Oh, one thing I should say is that the Excel standalone installation did just replace/install over the Excel component of Office 365 Home. Un-installing was also straightforward. That part was pretty seamless in the end although no-one at MS could tell me that it would be in advance. I can also confirm that the “Office Deployment Tool” definitely doesn’t work with Office 365 Home. So, if you’re not bothered about the backward compatibility then XL2016 standalone is a viable option for getting PP, if, like me, you don’t need or can’t justify a ProPlus or other higher level subscription which includes it. XL2016 standalone also comes with Power Query which 2010/V2 doesn’t have (I’ve not used it myself -but those who have seem enthusiastic).

  13. Thanks for this post, nice to see there is an improvement compared to 2013 (although it’s still confusing as to why it’s restricted to certain versions).

    Could you please do a similar post on which options we now have for ‘YouTube for workbooks’ as you call it? That would be highly appreciated.

  14. I have been using Excel 2010 powerpivot for sometime but noticed that if I open up sheet with cube formulae using Excel 2013 all the numbers disappear. This is a bit of an issue because I want to be able to retrospectively go back and look at Excel 2010 spreadsheets in the future even when “excel 2010” is not supported. Do you have any suggestions ? Microsoft have been hugely frustrating in the way they are managing powerpivot which is a real shame considering what an innovative product it is – I think they have to understand the kind of people who are using it …

    1. In 2013 they changed the name of the connection. Do a find and replace in formulas – replace “PowerPivot Data” with “ThisWorkbookDataModel” and all of the formulas will work again.

  15. As far as I can tell, Excel 2016 standalone does not get any of the new updates to Power Query and Power Pivot that are being made available on Office 365. Or is this just a quirk of my setup? I have Excel 2016 via HUP. When I upgrade it tells me that I am already up to date – even though I am stuck on 2.24 of Power Query…

  16. Just as an FYI I had Office 365 home with all the 2016 apps including excel and as mentioned in the article PowerPivot does not come with it, BUT then I ordered the standalone version of Excel 2016 and after the download now I was able to add in PowerPivot!

  17. I see it as false advertising when Microsoft indicates the different Office suites include the FULL version of Excel. So one easily gets the impression that the Excel for Home, being the Full version, is the SAME FULL version deployed under the OFFICE PRO PLUS
    Just need the right class action lawyer…..

    1. I’m not sure it was intentional deception. I think more a case of poor marketing. However, I’m not happy to discover that my version of Office 365 doesn’t have PP as a feature especially when I thought I was purchasing a full featured version.

    1. Pretty sure that page is wrong. I don’t understand why MS excludes Power Pivot from any version of Office that contains Access. They are both powerful data analysis tools and should be in the same versions of office for consistency.

  18. Sigh, no wonder none of the help files on support @ms are of any use to me. Yet none of the help files say the specific version, just Office 2016, or Excel 2016, which if you look at the “About” page inside the software, that’s what I have. I can’t even copy paste the version here because you can’t copy it anymore (what rationale could there be for disallowing that?). It says Excel 2016 MSO and a long number, 32-bit (I’m using a tablet with Windows 10).

    The odd thing is, there were only two versions available for purchase to me when I bought them 365, Student and Business, no enterprise or any other versions. I had no idea there were so many versions out there. I thought I was buying the whole deal by getting the business one. It’s getting to the point where a person has to be an expert on versions of software just to use basic training materials like the aka.ms/10exceltips which is prominently displayed on MS’s office training website. Thank you for explaining this.

    I did notice that there is a “power BI desktop” available for download, but it looked more like an “Access for Public Web Databases” so I didn’t look into whether I had to buy it or just download it or whatever. As much as I find it fascinating to spend a few hours massaging data from public databases for fun (who knows what cool infopopcorn I can make?), I do think it would be a waste of my time since actual BI would be held private by any company. This is once again underlining why “education” is really falling out of touch with actual business needs. If the software doesn’t even contain the real world tools… well, enough said about that. I guess I can look into the desktop thing and see if it gets me anywhere but it’s not on my priority list. Basic training in the new software is what I’m after.

  19. Office 365 University does NOT contain Power Pivot/Power Query, although the results of my queries on Amazon about it having Power Pivot had been stated as “yes”. Disappointed.

  20. Also I find it ridiculous that its not part of the Standard Office package, but is in the standalone. Currently having Get and Transform (Power Query) without Power Query is a huge pain in the ****, in trying to start working on Self serve BI for my company.

  21. No wonder that the uptake of Power Pivot is so low. I have used excel every day for most of my adult life and I only just heard of it. I would like to use it, but even finding out how to buy it is ridiculously difficult. Thank god I did not just hand over $200 for excel 2016 only to discover that Power Pivot, the only reason for the upgrade, was missing! What a disgusting way to treat your userbase!

    Frankly, it is obscene that MS do not include it in every version of excel 2016. They have made everything complicated and stressful for no good reason. Why does it harm them to bundle it with every version? If they want to charge for it then don’t include it at all and sell it seperately as a seperate product/add-in that has to be bought seperately.

    This has got to be the absolute worse way to introduce what MS is claiming to be “the best thing since sliced bread”, in the history of software sales. Is it a joke? All of these different subscription models and packagings of Office? Is it a comedy sketch?

  22. Yes, I’ve also bought Office 365 Personal to always have the lastest version of the software. For Excel I now have to use my old standalone 2013 professional version to work with the BI features. Very, very irritating….

  23. hi
    what is the sequence of the examples in the book I am little confusing what examples I have to use at the beginning of you book. to follow the exercise in the book.

    Thank you

    Carlos

  24. I only found out about PowerPivot not being in my version of Excel 2016 (Office 365 Home) today. I find this annoying. I’ve used Excel for 20 years, and this seems petty. Why not have a little faith in the users who’ve supported this application. Make it affordable to download this add-in. Have a nice day. 🙂

  25. I too recently installed Office 365 Home over top of Office 2016 so that I could be always on the latest versions. Now I find out THIS!!!.
    I don’t mind paying extra however what is really upsetting is the fact that I cannot find anywhere in the product description this difference in the versions.
    As far as I am concerned this is false advertising..

  26. Can someone help me and confirm if this part number will work with Powerpivot, I’ve spoken to 4 different Microsoft reps who all had very different answers to what supports power pivot some agreed and some didnt. I’m looking at Microsoft part# 065-08572, which is a Standalone OLP license of Excel 2016. From the info above, there’s a link to Microsoft’s site for Excel 2016 standalone retail, but i’m wondering if there is a difference with the OLP license. Thanks in advanced.

  27. Hi

    I have Office Professional Plus 2016, I think it should have powerpivot, but it doesn’t show in my add-ins. I have followed MS advice and deleted a registry entry (I only had one of the two they mentioned). I would appreciate any guidence on how to add powerpivot to excel – please?

  28. So-o-o-o. The product i could download for free and add on when I had Excel 2010 I now have to pay for since my company has switched to Office 365? Can I use Power Pivot for 2010 with Excel 2016? Because if that works ….

    1. If you buy Excel 2016 for $110 or whatever, it will overlay your current Excel with PowerPivot fetaure set, up to that point in the Excel 2016 cycle when you bought it. Any new updates will be unavailable unless you fork over another $110 or so. Better to pay the extra $5 a month and stay current. My 2c.

  29. I seriously need some guidance here.
    I am a freelancer and want to buy Office-2016 to use PowerPivot and PowerQuery. Which version should I buy? I don’t want to go for Office 365.
    Any help would be much appreciated.

    1. To create/author the data models, you’ll need an account like: Office 365 Pro Plus ($12/mo), and only a subscription service offer.

      What I’m trying to figure out is if the Office 2016 Pro Plus, non-subscription version, will also allow the Power Pivot add-in. 1 bot chat, 2 live chats, and 2 phone calls with MS and I still haven’t gotten a clear answer.

      The reason I want to know if the Office 2016 Pro Plus version of Excel will allow the add-in is because college students can purchase it for a flat fee of $49.99 through their school, as opposed to $359.99 at https://www.amazon.com/gp/product/B0153V64V4

      So, the last question I have is there any difference between the title “Office 2016 Pro Plus” as offered to faculty/students, and the same title available to the general market?

  30. By not including PowerPivot in all standard Excel versions Microsoft is driving technology backwards. As someone who has worked to develop business solutions in PowerPivot, I’m reluctant to use that particular feature in any of my new work. If I do I run the risk of having unhappy customers who are calling me to fix a problem that is actually a Microsoft Excel version problem. Future more, It could be anyone’s guess on which of the many different versions of Office may include it on the next go around. We can already see that it was striped out of the prior standard build (Excel 2010).

    Currently there are 5 different versions of the Business products, they all indicate that your getting a the standard Excel, Word, etc… but they are not all capable of PP and therefore are not the same product.

  31. I had an interesting discussion with MS Help desk. I pointed out that their promotion of O365 Personal may be deceptive, as it claims to be “fully installed”, which implies no ommissions and since O365 Pro was not included as a comparison to the promoted products a consumer may be misled in believing there is only one version. It’s being referred on, I wonder if anything will happen. I’m hoping that an equitable remedy may be offered.

  32. Same as other comments on here…Microsoft makes no effort to point out the differences. I bought Office 365 having been assured it was a full version. Microsoft confirmed that they don’t do upgrades (i.e. you want PowerPivot – it’s a business product stupid ! Business and personal Excel users are different – and there’s me thinking businesses were composed of individuals !)…oh, and as I didn’t buy Office 365 online “…we didn’t sell you anything…” I think they’re beginning to sign their death warrant as a customer service organisation, with lines like that

    1. It’s been 4 weeks now without any reply from Microsoft. I’ve made contact with the help desk and was advised that it had been referred to the Excel team… probably a nice way of saying nothing will be done about it, go away its your problem. Luckily I have a stand alone version of Excel which does have Power Pivot so I get by. I think the bear made need a poke from the Consumer Affairs Department.

      1. I didn’t need to refer it on. I made contact with Customer Service again and managed to reach the right person. They were more than happy to credit O365 personal and start a new subscription of O365 Pro Plus. I haven’t taken up the offer yet as I’m not happy with the O365 Pro Plus plan. I think its wonderful that it allows for up to 5 installs (and this will be helpful in another year) but at the moment I want a single install edition.

  33. It´s horrible what Microsoft is doing with the latest editions og excel. First off they introduced PP, which we where many who loved an incorporated in our BI solutions and so on – next the exclude the PP from most Excel editions an don´t bother to display which editions include PP! I tried to reach the support several times, so far without any luck as to get the corretct edition of Excel. First tried with 365, then after consultation with Microsoft i bought standalone excel 2016 for business and NO PP!
    So disappointed with Microsoft and wish there was an alternative to them!

Leave a Comment or Question