This is the longest running blog post series I have on my website. Why does Power Pivot Crash so often and what can I do about it? I have three previous articles that you can read in the links below
- Original post (Oct 16): Does the Power Pivot Excel Add In Crash for You?
- Follow up (Nov 16): Power Pivot Crashes Update
- Second Follow up (Jun 17): Another Update from Seattle
What Sparked My Re-interest?
Recently I was helping a client who is using Power Pivot for Excel 2013, and this re-ignited the topic for me. I stopped training students using Power Pivot more than 18 months ago. The reason I stopped was that I found Power Pivot for Excel far too unreliable to use as a tool to teach new users. The problem is that when you are new to a technology you have enough problems to deal with, without having to deal with the software crashing. I found that Power Pivot was just not reliable enough to be used to teach new users.
What has Changed (Apparently)?
When I was helping my client, I found that the Measure dialog in Excel 2016 was really bad. What do I mean by “bad”? I was typing measures in the dialog and it just wasn’t working “right”. I would type part of a function name, eg “CALCU” and press tab. The way Intellisense is supposed to work is that pressing TAB will accept the function from the list and then I can continue to write the rest of the measure. Instead pressing tab moves the focus to the next object in the dialog – eg the OK button.
Oh shoot I thought! Power Pivot is just as bad as it has been for years. What the heck!? Time to contact Howie to find out what is going on – and I am glad I did.
The Truth About What Has Changed
I am fortunate to have met some fabulous people at Microsoft. There are so many people focused on making things better that it is remarkable. Howie was charged with the unenviable task of leading a team of developers to fix the bugs in Power Pivot for Excel. In fact before Howie and the team took over the code, Power Pivot was past back and forth between quite a few teams and no one really took ownership of the problems. That changed when Howie and team took it on and they set out to fix the issues. There were literally hundreds of bugs in the code, and they have worked through the list, one at a time, until the list was all but gone.
But Why Then Are you Complaining about the UI Matt?
What I learnt while chatting to Howie is that most of the real issues have been fixed. The issue I described above is a strange nuance in the code – let me explain. Power Pivot for Excel (2016) has 2 separate editor windows for Excel. If you click on Power Pivot\Measures\New Measures you will get one UI experience. If you right click on a table and select “Add Measure/Edit Measure”, you get a different measure editing dialog box all together. This is a legacy hang over from the time when Power Pivot for Excel was only available in the premium SKUs. Thankfully Microsoft decided recently to make Power Pivot available to all Excel SKUs, and hence this second editor dialog is technically no longer required – but it still exists. The former (Power Pivot\Measures\New Measure) is a stable, robust part of the UI. The other editor (right click on a measure in the fields list) is buggy and may not be fixed.
Key Learning: Don’t right click to add or edit measures when using Power Pivot for Excel.
As a side note, the “engine” (called Vertipaq) inside Power Pivot for Excel is the same engine inside Power BI Desktop, PowerBI.com and also SQL Server Analysis Services Tabular. Actually, I say “same” but the reality is there are version differences between them all, but at the core, they are the same technology (Vertipaq). If you learn data modelling in Power BI, you are also learning data modelling in SSAS Tabular and also data modelling in Power Pivot for Excel (even though there are some version differences). Power Pivot is a much older version of Vertipaq – deliberately so. Power Pivot for Excel is part of the Office Suite and Microsoft has a strategy of maintaining as much version compatibility as possible between different versions of Office. At least in part this is why Microsoft is not currently pushing the newer Power BI updates (eg bi-directional relationships) into Power Pivot for Excel. Power BI Desktop on the other hand is a new product that is not constrained by the Office compatibility issue. If you want to use Power BI Desktop and want it to be compatible, you need to use the latest version – it is as simple as that.
It is not possible to say if and when Microsoft will bring Power Pivot for Excel up to a newer release of the technology. I wouldn’t even speculate on this one as I think it depends on so many other things that anything could happen. The only thing I would speculate on is that I think nothing will happen anytime soon.
Let me know what you think
Please let me know what you think about your version of Power Pivot. Is it robust? If not, what issues do you have? Please post your version and build number (Help, About) so it is clear which version you have. Just saying Excel 2016 is not enough as there are may different builds and versions with the same product name.