If you have recently upgraded to Excel 2016 and don’t have a Power Pivot tab available to you, there are a couple things you can do. You might want to go to the Developer tab and click on the COM Add-ins icon, which will display a list of the Add-ins available. If Power Pivot is available, selecting this options will allow you to use Power Pivot. But what if Power Pivot isn’t there? Well unlike Excel 2013, there is nothing for you to go download from Microsoft. Chances are if you don’t have the option listed in the COM Add-ins window, you are going to have to part with some additional cash to get the Power Pivot tab.
Business Analytics Features are no longer included in all Versions of Excel
Power Pivot is considered a Business Analytics feature, but What-if Analysis and Forecast Sheet are not. Seems to be an interesting definition of Analytics Features. If you want Power Pivot, you are going to have to pay for it. Here’s
Extracted from https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/
a clip from Microsoft’s website intending to clear up what versions include Power Pivot. Looking at this graphic, this is no way lists all of the versions of Excel which Microsoft sells. What about Office 365 Enterprise E1? Surely you would get Power Pivot functionality with that right? No. How much more money is Power Pivot going to cost you? Well, if you have Office 365, you are paying $8 a month for the Office software, including Excel. There is no guarantee that spending more money will provide Power Pivot though. Office 365 ProPlus, which has Power Pivot, will run you $12 a month. If you have Office Small Business Premium, which runs $12.50 a month you won’t get Power Pivot. Check the version of Excel 2016 by going to File->Account then look at what is listed. If the version isn’t Office 365 Pro Plus or one of the other versions listed in the graphic, there will be no way to make Power Pivot appear.
Power BI: The tool for Desktop Data Analytics
Excel 2016 is the first version to be released after Power BI moved to it’s own application. While Excel received the visualizations of Power BI, Excel did not inherit all of the data modeling capabilities of Power BI. The difference in Power Pivot is the diagram now shows the directionality of the relationship through arrows, a far cry from Power BI’s data modeling capability. Even when it is available, Power View is turned off in Excel 2016, and the reason given for this is “The interactive visual experience provided by Power View is now available in Power BI Desktop”. This sounds like a clear drive to separate the two products. I understand the desire to separate the two products. Changing the licensing model where people don’t know if the version of Excel they have will do what they need it to do, I don’t think is a good way to get people to use Power BI.
Yours Always
Ginger Grant
Data aficionado et SQL Raconteur
Pingback: Excel Licensing Changes – Curated SQL
I’m on a very basic Office 365 license so I don’t have the Power Pivot button, but I can still interact with it. You can use “Power Query” on the Data tab dump the results to the “data model” which is really just Power Pivot. You can then create a pivot table from the data model and within the pivot table you can interact with the data model to do things like add relationships between tables and create measures. It’s not as pretty as the Power Pivot window, but it’s better than nothing.
Tim —
That’s a good work around for the licensing issue, but as you say, it’s not exactly the same thing. I initially had a copy of Small Business Premium, which I thought was busted when Power Pivot didn’t work. I figured it out, but I wish this licensing change had not been made, especially as it is not always a monetary issue.
I hope other people find your tip helpful too.
Regards,
Ginger
Can you also use DAX formulas with this approach?
Thanks.
Yes, just right-click on a table in the Pivot Table and choose “Add Measure”. You even get the ability to view the formula list which you can’t do in Power BI Desktop (at least as far as I can tell).
Thanks very much. I realized a couple of days after I asked this question that that was the case, since that particular functionality is, as you note, in the pivot tables themselves, and not particular to Power BI. So, fundamentally, for me anyway, I think that this solves my problem, which is being able to access some practice/sample files that are actually in PowerPivot. My version of Excel is one of those that does not support PowerPivot, but I’m going to try to pull the files into Pivot Tables via Power Query and if I can, then I can still work with the files as needed.
And even if I can’t, this is still a really useful piece of information – thanks for posting it, and for replying to my follow up question as well. Greatly appreciated!
Actually, it is in the PowerPivot model which sits behind-the-scenes in Excel 2016. You just interact with it through the Pivot Table because that’s the only interface that you have without the PowerPivot window. Create a Pivot Table based on the Data Model (the new name for PowerPivot), add a measure, then create a separate Pivot Table based on the Data Model and you’ll see the Measure there. You are just interacting directly with the Data Model through the Pivot Table interface.
Tim, for some reason I’m unable to reply to your latest response, so I’ll have to reply here instead. First off, thank you for the clarification – my reference to Power BI was clearly incorrect, as Power BI is not even related to this discussion, and your correct inference that I meant to say Power Pivot there still showed that I was not thinking about the overall context clearly.
That said, I have found a limitation that this fix can’t address, which is the fact that a user still can’t create calculated columns, only calculated measures, so you still don’t get the full functionality of Power Pivot. Still and all, if you don’t have a need for calculated columns, this is definitely a good workaround.
Thanks again.
Ah, good point. Ya, I don’t think you could do calculated columns with DAX, but you still could do them in Power Query. You can also define the table relationships, just not using the nice graphical method that the Power Pivot window provides.
I personally don’t have a problem with them excluding it in certain versions, but I do have a problem with the licensing being so mysterious. Excel Online has the same problem. I can tell that there are different versions, but I’m still not clear on what versions they are and what is included/excluded in each version. I just wish that there was a link on the main Office 365 pricing page to a huge exhaustive list of detailed features that are included in each version.
Well it’s 2020 and it looks like the Power Q workaround is gone 🙁