What's killing My Excel?


Across the globe, Excel crashing is an accountant or analyst's bane. I've been there and know how exhausting it can be.  Fortunately (knock on wood), I've found some tips which have virtually eliminated crashing for me.  
  • Get 64-bit Excel
  • Disable add-ins
  • Prioritize Excel
  • Check your resource monitor

Get 64-bit Excel

The #1 way to fix Excel crashing issues is to install the 64-bit version of Excel on Office 365.  

By default, IT departments across the globe install the 32-bit version of Excel on Office 365.  While that works for most, Excel's 32-bit version is not sufficient for running the more complex Excel workbooks used by finance and accounting teams.  See this Microsoft Office article about why multiple Pivot Tables, slicers and VLOOKUPs can constrain your Excel resources.   

Finance and Accounting users are frequently the largest Excel consumers, but your IT support team may not realize it when deploying PCs to users.  If your Finance and Accounting teams aren't using 64-bit Excel today, there may be some support team resistance to making the change and deploying 64-bit.  Historically, Microsoft Office recommended 32-bit installs by default; however, it did not apply to power users like Finance and Accounting teams.  Here's a link to another Microsoft Office article which might help persuade.

Incapability with existing add-ins, such as Hyperion Essbase, is the primary issue which may need to be addressed. 
  • First, I recommend seeing if there is a 64-bit version of your add-in.
  • If not, consider installing anyway and testing the impact.
  • If impacted, explore with your IT team other solutions, such as a 32-bit Excel version with the add-in installed inside a Citrix environment and the 64-bit Excel version on your desktop.
If you are experiencing install setbacks, don't give up; I assure you the 64-bit version is worth it.  While you wait for IT to validate add-ins, try one of the other solutions below. 

Disable Add-Ins

Excel Add-ins sometimes behave badly, especially when the version of Excel the add-in was built on and the current user version of Excel deviate.  This seems to be especially true in the Office 365 environment where updates can occur monthly.

 Initially, try disabling all add-ins and turning them on only when needed.  To effectuate, in Excel go to File, Options, Add-ins, Manage, Excel Add-ins, Go.  Uncheck all the add-ins.  Next, repeat these steps, but select Manage, COM Add-ins, Go.
💥BEST PRACTICE TIP
Speed up the add-in disabling process by adding a short-cut to your Excel Quick Access Toolbar.  In Excel, go to File, Options, Quick Access Toolbar, Choose commands from: All Commands, Add-ins (the first one, which has no icon & a drop down menu). 💥
When Excel crashes, keep track of which add-ins, if any, were enabled.  Once an add-in has been identified as a culprit, work with your IT team to address.  In the meantime, whenever you need to enable the suspect add-in, save backup copies often. 

If Excel crashes at the same frequency when the add-ins are disabled, then add-ins is likely not your primary culprit; read further and see if one of the other solutions helps.

Prioritize Excel

All running applications share processor time.  Tell your computer to give Excel more love by changing the priority level.  

Use Ctrl+Alt+Del to open Task Manager.  On the Details tab, right click on Excel.exe process.  Choose Set Priority.  Then, select either High or Above Normal.  

 The priority auto-resets to Normal whenever Excel is restarted making this a temporary solution at best.

While it may be tempting to choose Real Time, rumor has it that this will make your other applications jealous, your CPU's head spin, and your PC crash.  💔

Check your resource monitor

Return to Task Master.  On the bottom of the Performance tab, select "Open Resource Monitor".  This tool provides utilization visuals for the processes running on your machine.  Choose the Overview tab and keep the 4 charts open and visible while you work.  When the Resource Monitor charts are at their maximums for an extended period of time, drill down on the applicable Resource Monitor tab to try and isolate what is dragging you down.  With data in hand, partner with IT support to achieve improved performance.  
In this lesson, you learned how to eliminate Excel crashes by:
  • Installing 64-bit Excel,
  • Disabling add-ins,
  • Prioritizing Excel, and
  • Checking your resource monitor.


Prior post: Measures Inside Measures
Next post: Distinguishing datasets in Power Pivot


💬Always love hearing how others have tackled Excel performance issues.  Drop a comment below to share.
By

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)