Udostępnij za pośrednictwem


Lee's Excel Troubleshooting Guide

In troubleshooting various Excel issues, I have found the following solutions to work quite often. Here are my notes in troubleshooting Excel. This was intended for current version 2016, but a lot of this information is still relevant in older versions, such as 2010/2013. If you’re using something older, upgrade already.

This is part of a series of my notes and troubleshooting guides referring to Microsoft Office. They are all listed here:
Lee's Excel Troubleshooting Guide Lee's Outlook Troubleshooting Guide Lee's Word Troubleshooting Guide

Now for my disclaimer
This information is provided “as-is” and is not considered step-by-step troubleshooting. Not all of the steps in this article are going to concatenate into a single solution. Please exercise caution as some things in here can have adverse effects, and by following these steps, you are assuming that risk.

Also keep in mind that in a corporate environment, you may not be able to perform some of these steps, either by policy or lack of system permissions.

Step: Empty the User’s temp folder
I have often found that the user’s temp folder will cause a lot of crashes to simply not re-occur. You do not need admin rights for this, as this is a user folder. The %windir%\temp folder requires administrator access and is not relevant to this troubleshoot.

To do this, close all running apps, open File Explorer, navigate to %temp% and delete everything. You may not be able to delete some files in use (like live logs).

Step: Manually calculate large and linked spreadsheets
Some people have enormous bordering gigantic spreadsheets with multiple links within the spreadsheets or to external data sources. Whereas auto-calculate is a very convenient feature, it also carries with it the overhead of having to check and calculate every link as it comes.

To fix this, click Formulas in the Ribbon Bar, then Calculation Options and Manual (to the right of the menu). To manually calculate, click “Calculate Now” right next to it.

Step: Attempt to do a quick repair of Microsoft Office
A quick repair could solve some minor configuration issues. Below is a link with more information on how to do this. Keep in mind you will need administrator credentials to do this.

See Repair an Office application.

Step: Delete the contents of the Excel folder in %APPDATA%
This folder is generally pretty clean, but for sanity, you can back this folder up before you delete the folder. Don’t delete the Excel folder, just the contents.

To remove contents, close Excel and in Windows Explorer, navigate to %APPDATA%\Microsoft\Excel and delete everything inside this folder (not the folder itself). You can try opening Excel again to see if this helps.

Step: Look at security software
Now, I know the usual suspect is antivirus, antimalware, and the like. And, it’s easy to blame things that their sole purpose is to keep things from loading. Not so fast – there were some instances where this was the case. In one case, I found Microsoft’s EMET loaded on a Windows 10 system, which actually shouldn’t be there. Portions of EMET were baked into later versions of Windows 10, so there was no need to have it installed.

Before tearing out anything that looks like security software, investigate any logs and see if you can find obvious signs of something being denied access or was blocked. This would be great justification to test without it. Some modern antivirus/security software does have pretty comprehensive logging, be it to text or one of their internal mechanisms.

If you choose to go this route, keep in mind the words “I disabled antivirus” aren’t definitive words. The only way to truly test without antivirus is to uninstall it. Why? Antivirus typically carries with it not one, but two calls to Windows. The first call is the filter driver that says “should I scan this file”, and even in a disabled state, you can expect this process to trigger. The second is the actual scan driver which checks the file in question for anything suspicious.

Step: Change Printers / Update Printer Drivers
What is old is new again. For those who remember back to the early 90’s and Office 4.x, print drivers could be a problematic thing – why? Simple rendering of a document on screen (print preview) can cause issues with Excel because it uses (you guessed it) the print driver to render. The best way to avoid this is choose another printer, and even better is one of the default printers like “Microsoft Print to PDF”.

Thankfully, print drivers have become more generic and universal in nature, like when HP released their universal drivers to worldwide cheers years ago.

Worst case, you may need a newer print driver or it’s an outside chance at a display driver. It’s always best to keep drivers updated as best you can, especially in an enterprise environment.

Step: Run Excel in Safe Mode (Disable Add-Ins)
Add-ins are also problematic at times. Instead of disabling one add-in at a time, try going into Safe Mode, which will disable them all at once. If this solves your problem, then insure you go into Excel and start disabling add-ins one by one. Hopefully in your case, the issue you’re facing is an easy repro (like crashes within 10 seconds of startup).

To load Safe Mode, either:

  • Load Excel from the “Run” box with excel /s
    Or...
  • Hold down the Ctrl key while double clicking the Excel icon.

Step: Log on as another user
There are two ways a user profile can be affected. By policy settings (GPOs) set on Excel, but also something that could be in the user profile.

If you are testing GPO settings, insure these things are true:

  • There are no computer policies applying Excel settings. This would invalidate any user setting testing, as policies applied at the computer level cover all users. If you need to for testing, you can deny the computer account “Apply Group Policy” permissions temporarily on the policy with the Excel settings.
  • The two user accounts you are testing do not have the same policies applied, more specifically ones that are specific to Excel. Like the above point, you can deny one of the users “Apply Group Policy” permissions temporarily on the user settings that hold the Excel settings.

Let’s not always blame GPOs, however. Something can be wrong in the user profile. Just to insure that it’s not the profile, log in as a different user.

Something to note: The beloved "RSOP" (rsop.msc) has been deprecated and shouldn't be used. You can get gpo info via gpresult /r (a personal favorite which just gives the basic info) or gpresult /h. Both of those methods are still supported. Remember to run this in an elevated session or you won't get system policy info.

Step: Delete the Office Keys from the Registry
If you feel that this could be settings related (and likely from a GPO), you can delete the Office portion of the registry. BE CAREFUL HERE as you may lose user settings in the process of fixing the problem (you’ve been warned).

For computer settings, navigate to HKLM\Software\Microsoft\Office.
For user settings, navigate to HKCU\Software\Microsoft\Office.

Note, if the problem applies to everyone on the system, it could be either Computer (or Computer GPO) or User (User GPO) related.

Step: Run Excel as Administrator
This step may be a bit tricky as in some more secure environments, you may not be able to elevate from a user desktop session.

This is something I’ve seen rarely fix an Office application, but I almost feel morally obligated to leave it as an option. You should only have to run it once, and in the more modern era, Excel really doesn’t require admin rights to load. It may be useful you need to access files that loading Excel normally may not be able to access. For example, someone (foolishly) linked to another spreadsheet in another location on the system that a regular user can’t get to (like another user’s profile).

Step: Disable DDE (for some older app issues)
A bit of a history lesson here, but Dynamic Data Exchange, or DDE is a very old technology that goes back to early Windows and even OS/2 for those who remember what that was.

You’ll find this setting by going to File / Options / Advanced and towards the bottom in the General section check the Ignore Other applications that use Dynamic Data Exchange (DDE) option. After this, restart Excel.

You may after the restart be able to uncheck this box again to keep the issue in remediation.

Step: Disable Hardware Graphic Acceleration
You may try to disable the Hardware Graphic Acceleration and verify the result:

You’ll find this setting by going to File / Options / Advanced and look for the Display section (not Display options for this workbook) and check the option for Disable Hardware Graphics Acceleration. Restart Excel afterwards.

Step: Disable Protected View Settings from Excel
More info on Protected View is here: https://support.office.com/en-us/article/What-is-Protected-View-d6f09ac7-e6b9-4495-8e43-2bbcdbcb6653

You’ll find this setting by going to File / Options / Trust Center / Trust Center Settings / Protected View and unchecking the options here. Again, read up on Protected View settings above for more information on the risks.

Step: Review the Microsoft KB on troubleshooting Excel crashes https://support.microsoft.com/en-us/kb/2758592

Step: Review the Microsoft KB about being out of resources or “Out of Memory” https://support.microsoft.com/en-us/kb/2779852

Step: Reinstall or Reimage
If all else fails, you can make the choice to reinstall Office (uninstall first) or just in most corporate environments, reimage the system.

More info is here: https://support.office.com/en-us/article/uninstall-office-from-a-pc-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8?ui=en-US&rs=en-US&ad=US

— Easy link to my blog: https://aka.ms/leesteve
If you like my blogs, please share it on social media and/or leave a comment.