Level: Beginners
I was chatting to Avi Singh the other day and we were comparing notes about the live Power Pivot training classes we both offer in Australia (me) and the USA (Avi). One thing we both have in common is that the Microsoft Excel Power Pivot Add-In CRASHES a lot during training! When I say “crash”, I mean it stops working in some ungraceful way. Excel normally don’t stop working, just the plugin. I always warn my students that the Add-In will crash , and I always say “If Power Pivot wasn’t so great, you wouldn’t put up with it – but it seriously IS that great and hence I have learnt to live with it”. But there were a lot more Add-In crashes in my training last week than I have come to expect, and I would like to try to do something about it. Note when I say the Add-In crashes, I mean it stops working – Excel itself doesn’t crash.
Before moving on, I don’t want anyone to get me wrong here, I still love Power Pivot (really – I LOVE IT!). It is still the best thing to happen to Excel since Excel itself. But the instability of Power Pivot for Excel during the data modelling process is really bad. In this post today I explain the symptoms of the Add-In crash and the recovery process. But more importantly I am seeking feedback from the community to share their own experiences with the Add-in crashing so I have some basis for a discussion with Microsoft when I am in Redmond later this year. So please post your comments and experiences at the bottom of the page.
It is Worse in Excel 2016
I taught my Power Pivot class using Excel 2016 for the first time last week (I normally teach in Excel 2013 or Excel 2010). Frankly I was expecting that the Add-In would be a lot more stable given it is an updated version, and given Microsoft ships bug fixes via O365 on a regular basis. But in fact I found the exact opposite – The Excel 2016 O365 version of Power Pivot for Excel is the most unstable I have ever used. For the first time ever, the Add-In crashed and at the same time it actually corrupted (lost) my data model and made the entire workbook un-usable. This has never happened to me before using Excel 2010 or Excel 2013 despite experiencing literally hundreds of Add-In crashes over the years.
Edit: After a reader comment, it occurred to me that it may be safer to save and close the workbook before disabling the AddIn if you are using Excel 2016. Sometimes the file won’t even save, but this may be the lesser of 2 evils given the chance of losing the entire datamodel compared to just losing your work since the last save. So if you are using Excel 2016, I recommend closing the workbook before taking action. If you do close the workbook first AND you also lose the data model, please let me know.
Symptoms that the Add-In has Crashed
There are a few symptoms that the Power Pivot Add-In has crashed. The easiest to spot is when you get some sort of “Unhandled Exception” error message (something like this one below).
If you see an error message like this, then it is time to restart the Add-in. Read about how to recover from an Add-In crash further down the page.
But sometimes the symptoms that the Add-In has crashed are harder to spot. An example is when you write a measure directly in the Excel measure editing box (like shown below), the measure is written correctly (shown as 1 below), yet when you click “Check Formula” (shown as 2), it says there is an error in the formula (shown as 3).
This is really confusing for someone that is trying to learn to write DAX. It is hard enough getting your head around all the new formulas and new syntax, but when Excel is telling you there is an error in your formula AND THERE ISN’T, then wow! I have a few years of Power Pivot experience, and in that time I have learnt to spot these subtle clues that the Power Pivot Ad-In has crashed, but for a new user it is a horrible experience.
Remedies for When the Power Pivot Add-In Crashes
The first thing I always do when teaching a Power Pivot class is to show the students how to recover from a Power Pivot Add-In crash. This is a mandatory skill for anyone learning Power Pivot. The standard fix is to turn the Add-In off, then on again. But in my experience there are actually 3 levels of remediation depending on the seriousness of the Power Pivot Add-In Crash. In my training course yesterday, I came up with a naming convention for the 3 levels of fix.
DEFCON 3
Excel 2016 only: save and close the workbook first. This is the most mild response to the Add-In crash and it is always what I try first. You first need to open the COM Add-Ins dialog. The easiest way to do this is to turn on the Developer Ribbon (turned off by default) and use the COM Add-Ins button.
Once in the dialog, you need to de-select the Power Pivot Add-In (shown as 1), then click OK (shown as 2).
Then you need to go back into the COM Add-Ins again, reselect the Add-In and click OK. More often than not, this will successfully restart the Add-In and you can get back to work.
DEFCON 2
Excel 2016 only: save and close the workbook first. Sometimes the simple stop/restart fix doesn’t solve the problem. Then it is time to move to DEFCON 2. The process is basically the same except you must close the Excel application before restarting the Add-In.
- Disable the Power Pivot Add-In
- Shut down Excel
- Restart Excel
- Re-enable the Power Pivot Add-In.
This second approach will fix many of the more serious Add-In crashes. But sometimes (and actually quite often when using Excel 2016), even this second level response doesn’t work.
DEFCON 1
Excel 2016 only: save and close the workbook first. Time then to move to DEFCON 1 and throw everything you have at the problem. The process is still basically the same except you add in a PC reboot to completely reset everything.
-
- Disable the Power Pivot Add-In
- Shut down Excel
- Reboot your PC
- Restart Excel
- Re-enable the Power Pivot Add-In.
This last approach has always recovered the Power Pivot Add-In crash for me. Unfortunately however I experienced my first ever workbook corruption using Excel 2016. I got the Power Pivot Add-In working again with a DEFCON 1 response, but the workbook could not be saved.
Things that Causes the Add-In to Crash
Given I have observed so many Add-In crashes over the years, I have an understanding of the types of things that cause the Add-In to crash. The key things that come to mind are:
- If you make an error in a DAX formula but don’t click “Check Formula” before saving the measure. The more often you do this, the more likely the Add-In will crash. It seems the internal error handling is not that robust.
- When you create Linked Tables and add them to the data model, there are often issues that seem to be related to the data model not refreshing properly. Often you simply can’t see the new table in the Pivot Table Field List. I can normally solve this problem by shutting down Excel and restarting.
- If you try to rename a Linked Table after you have added it to the data model, this can cause the Add-In to crash.
- Unfortunately now it seems that if you are using Excel 2016, that alone is enough to be at risk.
Power BI Desktop
Edit: 22 Oct 2016
I just finished a 2 day in house training course with a client. For the first time I did the entire training in Power BI desktop. It was much more stable than Excel has been. There were a couple of application crashes but recovery was pretty good.
Please Share Your Experiences so I Can Share with Microsoft
As I mentioned at the top of the post, I want to be able to give some “feedback” to Microsoft about this problem. You can help me by sharing your experiences in the comments section below. The more real world feedback I get, the more likely I will be able to get some attention to the issue. That way I will be able to demonstrate that this is not just one grumpy Australian that is complaining about an isolated problem, but a widespread issue that needs some attention.
I will let you know how I go 🙂
Relevant Articles
This is the first post in a series of articles. You can read the others in this series from the following links.
in power pivot tab–manage only once it loads model. Second clicking manage power pivot model does not open. Have to close excel and again reopen excel.
Hello Matt,
I am using excel 2019 version and my Power pivot doesn’t work – A) (Whenever I click to “Add data model ” – Result (It closes my excel file) .I did try your strategy & steps,still I am in no way to come up from this situation
B) if I add my multiple pivot tables to simple “Add data model dialog” box when you start using inserting first pivot table – then 1) I can see “create relation option” in field list & can create relation but that doesn’t show the right data
2) but I cannot open “Diagram view” to create relation of 2 Pivot tables for simple data model which has nothing to do with Power Pivot.
Please suggest some solutions in my case.
I did get to know your article from my Guru “Mike Girvin ” Excell is fun you tube video
Warm regards,
D M
I do not recommend using “add to data model”. Microsoft built some “low involvement” approaches to using Power Pivot without the need for you to understand what is going on. To me, it is like giving someone a bicycle, giving them a push to get going and then watching them crash. If you want to use Power Pivot, I suggest you load your data using Power Query and create the relationships from the diagram view inside Power Pivot. You should try that approach and see if it crashes or not.
Hi Matt
I’m still working on Office 2010, but like you put up with the crashes because Power Pivot is a great tool when it works. With all of my workbooks I am using ODBC links to bring data in to one or more sheets, and then linking it to Power Pivot. I have learned over the years to always refresh the ODBC connection first. Then save and close the excel file, re-open the file and refresh the data in Power Pivot, save and close, then re-open and refresh the pivot tables. It seems to me that the data connections cause some kind of conflict. Have you experienced this?
No i haven’t, but I also haven’t used Excel 2010 for around 8 years. I guess you don’t have a say in your version – it is effectively 10 years old now
Thanks, Matt Allington. This post was really helpful. Keep sharing such articles.
Hi Matt:
Thanks for your clarification, actually I am using Microsoft home& student version 2016 and under the COM Add-ins there is not Microsoft power pivot for excel, only the following are available:
Microsoft power map for excel
Team foundation add-in
would you please help me as I need to use the DAX and Data model and I really need to have the Power Pivot tab in my ribbon. Many thanks, Sarah
Please read my article here https://exceleratorbi.com.au/which-versions-of-excel-come-with-power-pivot/
HI Rob if you are still collecting comments (from a grumpy BritAustralian) – Power pivot has been really stable on 2016 until this week (14/5/20) when it has all just gone horribly wrong. I am not even that advanced. COM exceptions on load (despite repairs and multiple reinstallations), lost models, unhandled exceptions. ALL coming at a critical time in our budgeting cycle. Just a disaster. The usual IT fixes – off and on again – in all the permutations you record here – have not helped other than for a short period. It’s almost like there’s a cache or trace of previous errors that doesn’t get properly cleaned away.
Rob? 🙂
.NET somehow interacted wit hand crashed Power Pivot in Office 365 and Win 10.
I am new to using power pivot. Took the training class and everything worked fine in their environment. I believe this is because we were using Excel 2010 Went to my desk and installed the add in on Excel 2016. Anytime I try to add data to the data model or ask it to recognize data I get several different errors. 1. Unable to load the tables in the power pivot window. 2. Unhandled exception has occurred in a component of your application. 3. Excel freezes and cannot be closed except through task manager. I try to do another worksheet with a clean start and get: 1 An issue with the data model is preventing excel from opening the workbook. Try restarting Excel. 2. Sorry power pivot cannot open the Data model because there was a COM exception while opening. You might be opening a workbook on a corrupt installation of excel. Our IT department has been working on it since November. We have repaired office, reinstalled office multiple times tried a new computer – no fix. If Microsoft ever responds with a fix, PLEASE let me know.
The last communication I had with Microsoft on this was Oct 2019. The advice I have been given suggests the latest versions of Power Pivot is much more stable than your experience suggests. What version of Excel 2016 are you using? There should be a build number under the Account section. If you are using O365, try to switch to the monthly channel to get a later build.
Yes, unfortunately I have experienced a number of crashes. Also, since I teach at a small university with MS Excel and Power Pivot, my students have experienced many, many crashes. I have been trying to figure out why, when and how. Your article was the first real documented discussion of the problem. So, thank you very much! My other co-worker also experienced this problem and he too has switched to Power BI instead. I have designed my classes around Power Pivot with Excel 2016, and worse, I have done many graphs and analysis with it for my college’s Assessment. I am very concerned. My last crash occurred when I had 2 different Excel files open at the same time … so, I figured that was the reason for the crash. Now, I have many back-ups just in case. Your explanations and work-around recovery will be tested by me and hopefully I can provide more information later. In the meantime, I’ll try Power BI. Thanks!
I think us, as teachers, see the problem more consistently than others. Unfortunately the problem is as bad as ever today. Initially when I first saw the problem five years ago, it was very rare that the data model became corrupt. Unfortunately now more often than not the data model completely disappears. The test is always the size of the workbook. If your workbook goes from five meg to 50 K, you can guarantee the datamodel has been lost. The only option is to incrementally save the file with a new file name, But of course that is a preventative measure. It’s too late once you realise you should’ve done it
I am having a similar problem, however my Power Pivot add-on doesn’t disappear, my data model does! The workbook was working fine when I saved it, then when I reopen all the tables are missing from the Power Pivot window and my pivot table no longer works (Excel doesn’t recognise it as a pivot table).
This is extremely disappointing considering how much time and effort has gone into getting things to this stage.
If anyone has a fix for this I’d be really interested in hearing! (I am using 2016).
Yes I agree – it is very disappointing. But you are not missing anything – it is not stable and your file is corrupt. The only way to manage it is to proactively save backup versions of your Power Pivot workbook when you are maintaining the data model. Once it is corrupt, it is gone for good.
Anyone notice an issue where antivirus causes Power Pivot to crash or give the error “An issue with the Data Model is preventing Microsoft Excel from opening this workbook” when clicking on Manage Data Model. If you disable the AV then you can use Power Pivot with no issue. The AV is Cisco AMP. Tried excluding the Office folder and user temp locations but still no luck.
I have definitely seen an issue with AV in the past. At that time, I had to get my client IT department to modify the AV settings globally in the business to fix the problem. An aggressive AV can spot the background processes as malicious activity. These processes need to be identified as safe in the config.
I suggest you uninstall the addin, reboot the PC and then reinstall. If that doesn’t work, I suggest you contact Microsoft Support
Hi Matt,
I am taking advantage of this forum to address you my particular problem with Power Pivot in excel 2010. It just can’t load, the following is the message I get:
Load behavior: A runtime error occurred during the loading of COM Add-in.
I need your help, THANKS.
power pivot add in is not showing in excel 2016 – huge problem
Last week, after a lot of preparation of some nice DAX patterns, I started my training session in Chennai boasting about PowerPivot. After a clear explanation, I just wrote a pattern to calculate “Lost Customers” which is very advanced and lengthy code and hit enter..nightmare started..Excel automatically shutdown and restarted. I could not show face to the students and I am really scared to take another session. It left me no clue why it crashed that day. After that, I thought at least hundred times I might not have boasted too much that day. But still now I could not figured it out specifically who is the culprit behind that crash.
BTW Matt, which system configuration you prefer for all your sessions..?
I’ve never been able to avoid rebooting my computer when I crash PowerPivot to the point the tab disappears from the top of Excel.
Upgrading from 32-bit to 64-bit excel helped me some. There are little “habits” I’ve picked up (things I don’t risk attempting or do it a certain way) that has helped as well. For ANY other program, this would be a deal-breaker but PowerPivot is beyond worth it.
One way I can spot that it has crashed is when you go to use what was known as Power Query, it doesn’t do anything at all.
For example if I click on Show Queries on the Data ribbon, it doesn’t show the sidebar like normal.
I have severe issues when having a workbook with a PP data model open at the same time as some other workbooks. My memory usage in task manager is never anywhere near my total memory or the memory limit for a 32bit application. Despite this my Excel application becomes unresponsive. I’m not talking the usual unresponsiveness where the screen goes white and then Excel crashes. I can save the workbook and close the workbook, but the work area goes grey. It’s almost like I’ve run a macro and forgotten to turn ScreenUpdating on at the end of it.
Hi! Have such problem with Power BI (looking problem-mate on mrexcel.com-with no result:)
while saving file with using BI, Excel makes fatal damages on the file:
attempt to open this file after save&close arise an error: “Can’t find library or property” and lead to closing Excel.
Situation very sad because data in files isn’t accessible – file isn’t openable, make connection by Get&Transform is impossible.
This situation driving me mad, because it demand making backup copies every time I intend to make ctrl-S.
Environment description: Files locates on net-storage (by MS Server), In file exist some VBA-code, not used in this session. Version of MS Excel: MSO (16.0.4498.1000) 64-bit. No third-side addons in Excel. Crash is absolutely random and not repeatable.
PS: may be there is some connection with subject:
Sometime while closing Excel, pop up a dialog with:
“You are changed personal book of VBA-macros. Save?” (this my inexact translation in English),
in spite of the fact that I haven’t made any modification on this.
It’s not a signal means that file now to be broken, but look some strange..
Thanks for the post Matt,
I had been working on a Model for the best part of two days and had the PowerPivot tab disappear. You suggestion to disable to COM Add-in and re-enable it worked a treat. Saved me days of rework.
Hi, Matt,
Thank you so much for compiling reports of PowerPivot crashes. I hadn’t seen much information regarding pp instability, and was wondering if it was just me, or my environment… while I’m sorry to see other users experiencing the same frustration I am, I’m somewhat reassured to know that I’m not alone. I had given up on trying to identify issues that caused instability, but I will start logging again here so you have more ammo for Microsoft.
Hi Matt,
I’m running Excel 2013 32bit with PP and I’ve been having some crash problems. Hoping you or someone on here can help direct me to the fix.
Every time I tried to open a workbook with a data model (or after I create a data model in a blank workbook) I would get the error “We couldn’t load the PowerPivot add-in. First try Office Repair to fix this issue. The error below should help if that doesn’t work. Object reference not set to an instance of an object.” I tried your Defcon 3,2,1, office repair, .NET Framework repair, and nothing worked. Finally, the IT department did a clean install of Microsoft Office and the error disappeared.
For a while everything was working perfectly. Then today, when I went to create a measure for the first time since the fresh install, I got a new error “Unhandled exception has occurred in a component in your application. If you click Continue, the application will ignore this error and attempt to continue. Error creating window handle.” Now the data model and PP is back to behaving the exact same way.
What are my options? PP is completely useless as it is so I have to get it fixed. I don’t want to ask IT to do another fresh install, but if I did, I’m worried that I would trigger the error again the next time I create a measure.
When you say exact same way, I assume you mean “we couldn’t load the addin…”. Do you have 64 bit or 32 bit? Do you have O365 or a stand alone install version? If it were me, I would first try 64 bit if you don’t have it, and then call Microsoft support (there is a link on my links page – follow the Office support link and ask them to tell you what the problem is.
Is power bi Desktop an option for you? It is free, you can install 64 bit next to 32 bit office etc. The only issue is everyone that needs to see the reports also need power bi desktop (or Power bi.com pro) and that may not be an option.
Hello
I work with power pivot for quite a while now. I never faced any problems with the Excel2010 and after that with Excel2013 version until we got the Pro Office 365 version. Now it regularly crashes (actually always when I create new formulas). After loosing days and days with this I “went back to basic”, mainly, using pivot to get the data in propriate table , than copy and past into a “normal” table and work with formulas there.
It is really sad that after such a long time (this is not a reason problem) they didn’t manage to solve this problem.
I will for sure keep watching the net and hope in near future they come up with a solution.
Regards
S.
Recently, I have had to cancel a project with a major client that has caused me endless grief with memory crashes/lockups while developing in Power Query and Power Pivot.
Despite multiple saves, I also experienced significant slowdown in response time. Again my wingman is Windows Task Manager which I leave open at all time to monitor when the system is going to crash. Reverting to saves every half hour or so has littered my system with time-stamped backups. Even rebuilding the project from scratch (often therapeutic for me as I can optimize the structure) has given me no better performance.
I finally had to throw in the towel as I could not develop the model without constantly “looking over my shoulder” at Task Manager to monitor the system RAM uptake.
I was upset to have to call the project and the client was upset because of the time I had expended with him to develop the methodology over the past year. Naturally I cannot bill the client, and the only solace I have is in lessons learned.
I am pleased to see the addition of new features to the Power family but saddened that some of the key issues that prevent the Power family being used in a production environment are still not addressed.
The project I began some 12 months ago started out as a Excel/VBA project, then segued into a Power Query one – with disastrous consequences – all that time and effort lost in changing to Power Query.
I am not giving up on Power Query/Bi but I cannot afford to propose any PQ/Bi solutions until the fundamental performance issues that are still being brought up by uses are addressed.
Beautiful concept but still fatally flawed in the fundamentals.
Sad indeed Fran. How about Analyze in Excel as a solution? You can author reports in Power BI Desktop (even migrate your existing model) and it is much more stable then. Then just build all your Excel reports pointing to PowerBI.com? I sort of wrote about that today https://exceleratorbi.com.au/use-power-bi-excel/
Thanks for the suggestions Matt – Unfortunately Power Query has been too much of an enticement and I am prepared to stick it out until the Ram usage issue is resolved.
…made me think of Hans Christian Anderson’s “The Emperors New Clothes”…
Why is Ram escalation still such an issue. I wish I could pin down exactly what is causing the problem, but I sit there watching the Ram uptake, through Windows Task Manager, rising and falling, rising until it finally maxes out and stalls the system.
Ah well…
Maybe this could be crowd funded…
Fran
I’m using 2016 64 bit and I’ve had every one of the issues mentioned and a few more when using the data model in Power Pivot. Good to find out I’m not alone!
This week for the first time I have been getting issues with PowerQuery, If I hit refresh my queries work no problem, however if I try to edit, the preview sometimes (randomly) takes a long time (hours) to refresh. Copying the code to a new query resolves the issue so I have resorted to updating in a new blank query then copying my code back into original. Very Very frustrating.
Have MS made any public comments about the issues?
I’ve never heard of this Power Query issue. Have you tried to contact support? Since it is reproducable they may be able to help. The link for support is on my links page in the Excel section
Thanks Matt, will try the support link and our own MS contacts. The PQ issue happens on multiple machines so its either a bug or something has corrupted inside the particular workbook.
I’m a huge fan of the Power Pivot and Power Query functionality in Excel but the issues are starting to de-rail my confidence in the product, I’ve lost several full days recently trying to recover situations leading to missed deadlines. I spent all day today going through my copious numbers of backups trying to find the most recent version which didn’t give an error on saving after updating the model, tomorrow I get to re-do all the work that was lost.
It really shouldn’t feel like Russian roulette when hitting save 🙁
Yeah, that sounds really bad. I know they are working on the Power Pivot issue (I have seen the list of bugs they have fixed). The biggest underlying issue is these problems are very difficult to replicate. If you have a Power Pivot workbook that works but has reproducible issues (that you can share) I can put it in the hands of the development team. I realise data may be sensitive however.
Haven’t, been able to extract the sensitive data without triggering the save issue, I’ll see if I can escalate through our internal channels.
I think the Power Query issue is related to the most recent version, MS seem to have added functionality to PQ but introduced bugs. I have found a PC with an older version of Office 2016 and that runs the queries without freezing, however it lacks functions (like append multiple queries at same time) so we’re faced with waiting for bug fixes or reverting to older version and re-writing everything.
My (working) version is 2.24.4065.1401
The broken version is 2.38.4491.181
Would you happen to have any information about the “Object reference not set to an instance of an object” error? I can reproduce this issue every single time.
1. Open Blank Workbook
2. Click on “Manage” for Power Pivot
3. Opens fine. Close out.
4. Retry to Open PowerPivot, and it doesn’t load, nothing happens.
5. Click it again get an error about Pivot Unable to Start.
6. Close out of the workbook and get the error prompt “Object reference not set to an instance of an object”.
I’ve tried clearing temp files, updating to latest version. The only work around I have for this is to leave PowerPivot open on a completely different workbook. I don’t run into that issue when I do that.
Hi Francisco. I am aware of the error but I have never really worried about this one as it doesn’t seem to do anything bad. Everything still works – it is just a pain.
In Prop Plus and Office 365 Current Channel I find whenver I create a new measure and click OK it then jumps me to a different file (this happened with all students this week, but not with students a week earlier who were on an older version of Excel 2016)
Also, renaming a measure which is in a Pivot Table already fails to rename that measure properly and your measure editor box then has a different name to your pivot table and field list. To avoid this you need to remove the measure from all pivot tables before renaming it. Then it works fine.
Finally, during training there are multiple occasions where some but not all students have their tables not show up in Power Pivot after loading. Closing and restarting Excel fixes the issue.
I even had a bug in the new version of Power BI Desktop this week where a few people were unable to move tiles around unless they closed and re-opened it. The files were created by importing from existing Excel models but not sure if that is the cause. Happens to just 1 or 2 students each course. Very odd
Hi Wyn. I have experienced every one of those bugs too, including Power BI Desktop. FYI, the issue with the measure rename in Excel is that there is a measure name and a customs name. After renaming (while in a pivot), you also need to go I to the measure settings (right click the measure in the values region of the fields list) and change the custom name
I have just “enjoyed” a crash of my 64bit 2016 PowerPivot for the first time! I had almost a full day of time to work on Learn To Write DAX and was making a lot of progress. Suddenly I got the #@$%@ Unhandled Error … and lost all of my work! I was glad that my wife was not around to hear/witness the lack of civility that I exhibited. My file was corrupted and would not reload; I lost the PowerPivot and PowerBI tabs. I unwittingly followed your DEFCON steps and got PowerPivot (and the PowerBI) tabs reloaded. I hope there is a FIX on the horizon for this nasty little beastie.
Yes it’s horrible. You can manage the risk by creating your measures in the Power Pivot window calculation area. I generally don’t like doing this for reasons covered in my book, but I have no doubt it is more robust to do it this way (as I believe it is the new measure dialog box in Excel that is buggy).
Since it is possible to “crash” the PP editor in Excel consistently (80%) of the time, it must be possible to trace the issue by now.
As was pointed out, its not only buggy DAX code but correct and simple code that’s crashing the system.
As I pointed out earlier, I no longer use the PP editor in Excel, and I repeatedly save backup copies of my work.
I can feel for anyone hoping to sell the product concept to management.
PowerBI id more stable but I still prefer to pre-process my data in the PQ way before presenting it further. At least I can see how the data and logic pans out this way.
Fran
I’ve been using excel 2016 64 bit with Windows 10, and YES it crashes a lot…!
I’ve been using PowerPivot for over 2 years now and it’s a great product, but its clearly become very unstable.
Microsoft needs to address this issue urgently before the products reputation is damaged
I’ve held myself and my clients back from anything more than casual dabbling in Power Pivot for many years now, hoping these issues would be stabilised. Its disappointing to see its actually getting worse. It’s a huge missed opportunity as the underlying engine has proven to be robust and scalable (ref SSAS and Power BI, even Pivot Tables on Power Pivot (if you dont touch the model) ).
Thanks Matt for having the courage to bring these issues out into the sunlight and hopefully your connections at Microsoft can pay some serious attention to it.
It seems clear at this point that Power BI is a much more stable and reliable product, backed by significant Dev & Ops teams, so I’m putting all my effort into that.
Matt
I have been following the Collie/Singh primer on DAX and find that PP consistently crashes IF I choose to edit a DAX expression from the PP Field List window (Excel side) .
If I edit the DAX expression in the PP DAX (green theme) window all is good. And the DAX expression is shown correctly in the PP (Excel side) table. So no issues.
Going forward I choose never to build or edit DAX in the Excel side until this is resolved…
Fran
Another point worth mentioning in conjunction with crashes: remember to clear out your PP cache regularly from C:\Users\%username%\AppData\Local\Temp. These temp leftovers occur when PP crashes and fails to cleanup. They’ll chew through many gigs of space if you don’t manage them.
Further, after fresh installs I ensure the following patches are applied:
https://blogs.office.com/2015/11/10/power-pivot-in-excel-2013-november-2015-customer-update/
I was contacted directly by a user with the following comments. I am posting it here so all the feedback is in one place
I’ve been trying to use excel 2016 and use the BI especially the power pivot functionality. it is impossible. It crashes all the time. If I try to test a Dax expression Excel stops working and crashes. I had the 32 bit version installed, so uninstalled and installed the 64 bit version. Same crap. One gets the Excel has stopped working message and then the message that excel is shutting down. This is this crap, I purchased the stand alone version for the BI functions. Tried your routines crashed immediately on first try to edit a dax formula. It was simple a calculate with 3 simple filters on more than one table.
I tied to apply a KB but got a message that nothin on my machine had anything for the KB to act upon.
I haven’t yet tried my excel 2013 standalone. at one time the BI functions worked. I’ve also had trouble in 2016 with trying to paste a disconnected table to the data model. Running 1607 Win10 proon HP desktop 8 gigs Processor AMD A6-8550 Radeon R5, 6 Compute Cores 2C
I’ve just started using 32 bit PP at home and at work. It crashes a lot in both places.
At home I’m using Office365 Pro Plus, build 16.0.7369.2038 running on a pretty decent new HP Envy and Windows 10. Excel crashes whenever I try to edit an existing measure via right-clicking on that measure in the Fields Dialog pane (i.e. the window in Excel on the right hand side) and selecting Edit Measure. As soon as I make a change and hit OK, Excel crashes. Whereas if I do the same thing by choosing Measures>Manage Measures from the PP ribbon in the Excel window, then I can edit the measure without Excel crashing. And I am talking about very simple tables, and a very simple measure (e.g. if I just use =COUNTA(Completions[Staff_ID]) as the initial measure, then add a simple +1 to the end of it, Excel will crash. )
At work I have experienced it crashing a *lot*, and on restart I have no PP tab in the Excel ribbon, even though the Addins Dialog shows that it is indeed installed. I’m presuming this is what you mean when you talk about DEFCON 3. Deselecting the addin, pressing ok, then going back and rechecking it again puts PP back in the ribbon. But sometimes when I reopen the file, no model shows up. So I save my work with different version numbers constantly.
This is VERY bad PR for this tool. Happy to demonstrate to anyone at MS that wants to experience my pain first hand.
Oh…and the Check Formula button seems to crash Excel when editing that measure via the Measures>Manage Measures from the PP ribbon in the Excel window.
Hi, I face exactly the same problem as Travis describes above, with the same message, and with all updates ok as well.
For example, today, Excel is crashing when I write a formula (not that complicated one, but with a time measure “lastdate”) and try to “check” it.
16RAM, Windows10 + Office 2016.
Big Snif 🙁
I am seeing this same error as Travis, Elena and Jeff. Check formula works with a new measure but every time I use Check Formula on an existing measure PP crashes. Issue just started today. Had coworker try file and she did not have issues with it, appears to be related to my computer. Yesterday IT added Visio to my computer, so wondering if there could be a connection.
Now, it’s happening to me a lot today. Every time I try to “check formula”, Excel crashes, and I get this message when it tries to restart (http://occstoragemedia.blob.core.windows.net/images/Capture.JPG). I’ve checked that I have all updates (all good – Version 16.0.7369.2038). So frustrating!
It’s definitely happened to me frequently and for various reasons. I actually just encountered this new scenario where the “check formula” button in the DAX editor window now causes a run-time error and Excel to crash (http://www.screencast.com/t/kUPeXAZH6) link here. Anyone else encountered something like this?
I can confirm everything you wrote.. just writing to show support 🙂
I’ve been keeping all un-needed addins, addons etc disabled.. no science on it, but it feels like its helping at least 🙂
I too experience multiple crashes a day. Few in Excel 2010, some in Excel 2013, but now that I’m Excel 2016 crashing is now just part of my day it seems. I echo Andre’s comment about firing up multiple instances of Excel. #1 it isolates a crash such that other instances are not affected… #2 it is more stable running isolated… #3 Nice to be able to view other spreadsheets while in PQ Editor or say read one PQ while editing another… Just hold down then left click Excel icon in task bar then right click Excel (but keep holding down). Make it a habit and life will be better.
Hi Matt, I can corroborate all your findings above, and have a long list of other crash messages I’ve received in the past. I have been using Power Pivot for most of my work over the last 2 years, and started on 2013, running 64 bit on Windows 8.1, using an HP EliteBook i7vPro (dual core, 8GB RAM). That was fine until my data models started to become more complex and I hoped that 2016 would improve things. Í also moved to Windows 10 at this point. While 2016 has better features and is more user friendly, I didn’t really notice any performance improvement. I soon felt I needed more grunt. so I got our IT guys to build me a virtual machine with 4 cores and 32 GB RAM. The performance of the virtual machine was slightly poorer than the physical one, so I abandoned that idea. At this point it was standard for my machine to crash about 6 times a day, in any number of ways, and I hoped that it was related to hardware. I successfully lobbied my boss and IT dept to invest in an HPZ640 desktop with 72 GB RAM, and since the upgrade, Power Pivot has been much more stable. It still crashes from time to time, but maybe once every two days instead of 6 times a day. The improvement in a speed of calculation sense is noticeable, but not as great as I was hoping for. The stability helps a lot though.
Still, I find the most reliable way of inducing a crash, is to try to click somewhere in Excel while a calculation process is running (often there is no notification to tell you it is busy calculating, so it just seems unresponsive for no reason). Clicking between windows repeatedly almost invariably results in a crash, just after it seems to “wake up” and display some clue that it was busy with a calculation.
I have almost had to develop a sense of intuition for when I should risk clicking on different windows after the first click didn’t do anything, and when not to, as doing so may or may not cause PP to crash.
So if you have a ton of cash to drop on a monster PC, you’ll probably experience increased stability, but I think Power Pivot still has a fair number of wrinkles that need ironing out.
I have found that starting a new instance of Excel for running multiple workbooks at the same time using the run command prompt “excel /x” has the advantage of completely isolating a crash to the particular instance of Excel it relates to. The other instance will continue to run uninterrupted. I suspect you need some grunt under the hood to take full advantage of that, however.
All that extra memory is not really needed. What you need is (in order of priority).
1. Fast CPU Clock
2. Fast (and large) L2/L3 Cache
3. Cores >=4
4. Fast RAM, >=16 is best
Virtual servers are normally useless – don’t do it.
Make sure there is no power saving features enabled in the BIOS
Sigh! I tried to catalog for a few days whenever Excel/Power Pivot threw an error for me. (see linked image below). I wasn’t even very diligent (I captured perhaps 1 of 5 errors). But you can see, it happens quite frequently. I was speaking to Matt, that while this is embarrassing to handle in a training class; it’s impossible to handle during a sales pitch! If you’re trying to sell the idea of “Modern Excel” to someone, errors like this leave you dead in the tracks.
For me, Power Query feels pretty stable, but PP/Excel interaction is always problematic.
See https://cdn.pbrd.co/images/1P68rvShd.png
Matt,
I run 2016 64bit. I also use BI desktop. My PP crashes all the time.. it is very common for me to be in BI then open Excel to only find the add in is gone. If I start getting error such as ‘Data source not found’ I’ll have to re start my machine to fix it. Until now I thought it must have so,etching to do w my Power BI being open.. now I just think it’s 2016.
I have had multiple issues with the Power Pivot add-in. Make sure your Personal Macro Workbook is not the cause of your problem.
Good point about their personal vba workbook. I blogged all about that here https://exceleratorbi.com.au/excel-keeps-crashing-check-your-vba-code/
Hi Matt,
great blog! The company I work for updated all machines with Excel 2016 64bit last August, I was the happiest chap in the world at the idea of finally being able to run some proper data model with a stronger engine but I started experiencing those crashes and I got very disappointed. At least now I know I don’t have to blame my IT guy 🙂
I’ll try your Defcon 3,2,1 suggestions and hope for the best, but when I see a big red St. Andrew’s Cross on my model it really breaks my heart.
Keep us posted on Microsoft feedback and all the best to all of us power pivot fans!
Hi Matt,
My installation of Excel 2016 (16.0.7329.1017 64-bit) now crashes whenever I click “Check DAX Formula”.
It happens for existing models and models created from scratch.
Not sure when this started exactly, but it is certainly annoying!
I will try your DEFCON fixes systematically and see how that goes, but it would be great if Microsoft can address this 🙂
Definitely have crashes with PowerPivot on Excel 2013 at work, enough so that I generally save after every change I make. I hadn’t had a workbook corruption issue for about a year until we suddenly had three in the last two months (thankfully I kept truly stupid numbers of backups, and the backups were fine).
I’ve just started playing around with 2016 at home, and it seems to be both slower (takes longer for things to “finish” when I add a new measure) and more crashy. I’m hoping that is going to be fixed soon, because my company is going to 2016 soon!
I run Excel 2013 64 bit Windows 7 and only really had problems when renaming linked tables (either from workbook or from Power Query). There was a fix in Oct 2015 which Ken Puls blogged about and been OK since.
Also, I don’t tend to use the DAX text box but rather use the calculation area in the Power Pivot window itself. I wonder if you wrote the same complex DAX measure whether it would be more stable there.
I have been working with powerpivot for almost four years in both 2010 and 2013. I now use 2013 64 bit on a virtual machine and it is way more stable than the old d32 bit in 2010. But some of the same old problems persist. Making changes to the data model can crash excel and leaving the dax windows open too long can crash it too. Sometimes the powerpivot tab just disappears! Always happens right when the boss needs a report right now. It is a serious blight on an amazing product.
My Power Pivot Excel 2016 is worse then 2013 Version.
1-) Calculating process takes time. about 7-9 sec.
2-) Sometimes data models are crashing.
3-) When I import my data model from Access or SQL, Its been imported but in order to see it, i have close excel and reopen the workbook.
Can’t say that I’ve had Excel 2016 crash on me…I have had the Power Pivot tab just disappear. I then go through those DEFCON steps (uncheck the COM Add-in, save, then re-check them). That seems to fix that problem. My bigger problem with 2016 is that it doesn’t want to set itself as the default program. If I click on an .XLSX file (in an email attachment for instance) Excel 2013 will launch and open the file. I always have to first open 2016, then FILE > OPEN and navigate to the file. Different problem than what you’re discussing here, but still frustrating. (I have tried setting the defaults through Control Panel, but alas…).
I run Excel 2013 64bit on Windows 7. I also had some crashes when I started to use the add-in. What I realized was a) Excel did not like to have two files with data models open simultaneously, specifically when the PowerPivot windows of those files were active b) In those situations Excel did crash and/or did not close correctly and an Excel process was still invisibly running in the background. That hidden process seemed to cause follow-up issues when opening other files.
Since then I take care that only one Excel process is running when I start working with a data models and I try to prevent opening another one, e.g. for looking up a measure for reuse. The issue basically went away.
Hi, I’ve had many crashes using power pivot in Excel 2016 on Windows 10. I got IT to install the 64 bit version after multiple crashes in 32 bit but it still crashes lots.
I have to clear the excel.exe using task manager and on a couple of files that I use regularly I have to remove from recent history before opening again from within excel to get the file to open correctly.
Glad it’s not just me!
I give training courses in PowerPivot. It always crashes during my courses. 2016 is mutch worse than the previous versions. And it is very frustrating…
I have a theory that the error checking is very unforgiving. If you make errors in your formulas, you will have crashes. This is why it is worse for training. What do you think?
I’d buy that, Matt. I find that if I’m working on complex DAX measures I’m more susceptible to crashes. For me just getting an error in the “Check Formula” button is enough to send it into the downward spiral.
And also when the crash powerpivot vanishes from the ribbon.
This definitely happens.
I use power pivot in excel 2013. It crashed twice in the last three months. Whenever, this happens i simply close the excel program and then i reopen it again. Then, in the left pane when i open the file again it prompts for recovery of the saved version.
I’ve worked with standalone versions of Excel 2010,13 and 16. They appear more stable comparing with O365.
Hi Matt, I am using 64Bit Office 2010 on a Win7 machine at work. PP often disappears I just assumed it was inadequate hardware. I have used every one of your DEFCON suggestions over time. The other issue you described, workbook corruption, I discovered that if I opened the workbook on someone else’s PC – with or without PP PQ installed they could open, save the WB for me and I could then open the file and continue to work. Crazy as it seems it has always worked.
Ihad always assumed it was just me!
I will definitely try the “open on someone else’s PC” trick
I use Excel 2010 & don’t run into crashes with the addin. usually my issues are with not enough memory, so in case where the model is fairly large. i’ll run that on my VM that has Excel 2010 64 bit on it.
in Excel 2010 32bit I do sometimes have issues that when I close Excel, it re-starts as if it crashed. doesn’t happen all the time so not sure if it’s the power pivot add in or some other issue?
I am using the OLD plugin with Office 2010 and it very RARELY crashes. My issue is that our standard install is 32 bit office, hence we run into memory related issues quickly.
I don’t find the addin crashes, but I often find that Excel 2016 32bit hangs while working with power query. I have to kill Excel and to restart it. I can’t use the 64bit version because it is incompatible with some other software that I use. It may be wishful thinking, but it seems to be more stable since the last update.
No crashes so far while working with Excel 2016 and Windows 10. So sorry to hear it’s causing you so much trouble, since I’ll be teaching extensively with it this winter. Had few problems teaching last year using Excel 2013 and Windows 8. I’m in a University setting (Duke U. Fuqua School of Business, Durham NC USA).
I have had great luck so far with latest versions of Office 2016 + PowerPivot. So far I’ve had very few actual crashes while working on my models.
HOWEVER – I find that very often when closing Excel with a data model it doesn’t shut down nicely – if you open task manager you will see excel.exe still running. If you then re-open Excel, the PowerPivot addin disappears from the ribbon and you can’t get it to reappear.
I found that if you use task manager to kill the excel.exe process before opening excel again, all is fine. So I created a batch file bound to a thumb button on my mouse to do this: taskkill /f /im excel.exe. I probably use it 2-3 times a day when this issue comes up.
Have you found a solution to this issue? It is rather “annoying”.
There is no solution yet however I can tell you I know the dev team is actively working on it and I have seen a large list of bugs they have already fixed. There is no clear release date so I have no idea when it will be released. When it comes, it will come to o365 well before the other versions.
I tried the Defcon 3 and it completely lost my data model. Was that a known “feature” with defcon3.
I have this issue frequently and recently for some reason it has gotten worse and almost to the point of being intolerable.
If you have Excel 2016, this is a known issue. But it is unrelated to my recovery steps (to my knowledge anyway). I guess I have never really thought about closing the workbook before disabling the plugin. Sorry you lost your datamodel.