Modano Moderator Note: Strikethrough font has been used to indicate parts of this post which are no longer applicable as a result of improvements to performance and stability of Excel since this post was created.

Many of Modano’s clients are making the leap from Excel 2010 to Excel 2016 (I’m going to pretend Excel 2013 never happened, so if you're using Excel 2013 please do yourself a favour and upgrade to Excel 2016 immediately), and noticing that many things feel slower and appear to lag in Excel 2016.

One of the main reasons for this that the Excel 2016 user interface supports high-resolution displays, and uses a single document interface (SDI), meaning that each workbook opens within its own window rather than them all opening within a single window as they did in Excel 2010.

While the engineers at Microsoft obviously thought these ‘improvements’ were great ideas, I’m yet to come across a financial modeler who isn’t mourning the loss of performance that they have caused, so I thought I’d share some performance tips we’ve discovered at Modano that might help mitigate the pain.

1. Close unused workbooks

A performance biproduct of the single document interface (SDI) is that each workbook has its own window which needs to be redrawn each time Excel is repainted on your screen.

We expected Microsoft to have better optimized the management of these windows, but basic testing shows that the rendering of Excel slows with every additional open workbook. And when custom ribbon items are thrown into the mix, including the Modano ribbon tabs, the impact is even more noticeable.

So, if you’re not using workbooks, close them rather than leaving them open to drag all the others down.

2. Disable hardware graphics acceleration

Ironically, while the phrase hardware graphics acceleration might sound like a performance improvement, it’s really referring to a whole bunch of unnecessary animations happening while you use Excel that you hardly notice, but that actually slow things down.

To switch off these performance-chewing annoyances, click on File, choose Options, select the Advanced tab of the Options dialog, and tick the Disable hardware graphics acceleration check box within the Display section, as shown below:

3. Pimp up Windows

Like Office 2016, Windows 10 also contains a range of cool but unnecessary performance-chewing animations that slow things down. To turn these off, go to Control Panel, select System, and then click Advanced system settings on the left side of the window, as shown below:

Then, in the System Properties dialog, select the Advanced tab before clicking the Settings button within the Performance group. This will load the Windows Performance Options dialog, as shown below:

None of the settings in this list are necessary, and all of them slow down your computer. Personally, the only one I leave switched on is the Smooth edges of screen fonts setting, as I find that some fonts appear jagged when this option is turned off. The remaining settings had no positive impact at all for me, so I switched them all off.

Modano Moderator Note: Since this post Excel 2016 has become more stable, and Modano now recommends the use of the latest version of Excel, which on most devices should be the 64-bit version as the default Windows installation is now 64-bit.

4. Don’t use the 64-bit version of Excel 2016

When using Excel 2010, many financial modelers experienced Excel crashing after exceeding the 1GB memory limit. Since July 2016, Excel 2016 has been large address aware, which means that the 32-bit version of Excel 2016 can use 50% more memory than the 32-bit version of Excel 2010.

While this might not sound like much, from our experience building very large models for Modano clients it is more than sufficient to handle the vast majority of financial models without needing to install the 64-bit version of Excel 2016. And if it is an issue, it’s usually a model scoping and design issue rather than an Excel issue.

Very few Excel users understand that the 64-bit versions of Excel use twice as much memory as their corresponding 32-bit versions of Excel, so it’s not as simple as saying that the 64-bit versions of Excel can open files twice as large. In fact, this statement is both incorrect and misleading, and it’s more accurate to say that any 64-bit version of Excel will almost certainly be slower than its corresponding 32-bit version, because it must do twice the lifting to complete the same tasks.

Importantly, while Modano works fine on 32-bit and 64-bit versions of Excel, 64-bit versions of Excel often have compatibility issues with many add-ins, and add a layer of complexity when writing VBA macros and working with other parties, who will usually be using a 32-bit version of Excel.

So, put simply, don’t use the 64-bit version of Excel 2016 unless you absolutely need to do so. And afterwards, revert to the 32-bit version of Excel 2016 immediately.

5. Turn off auto-syncing of Office files

Like the rest of the world, Office is heading into the cloud, and it’s going to allow all sorts of exciting new ways of collaborating when using Microsoft Excel. There’s only one problem – it’s all still a bit clunky.

We’re yet to narrow down the performance hit culprits into an exhaustive list, but we’ve had feedback from clients using Dropbox, Google Drive and OneDrive, indicating that switching off auto-syncing often significantly improves the performance of Office, including Microsoft Excel.

So, if you’re using OneDrive and feel like Excel is lagging, go to the OneDrive Settings dialog, and untick the Use Office 2016 to sync Office files that I open check box, as shown below:

The strange thing about OneDrive is that it behaves very differently depending on whether you’re using it for personal use or corporate (e.g. SharePoint) use, and depending on your internet/network connection. For example, one of our users reported that auto-syncing had no impact when connected to their home internet connection, but caused Excel to lag for 3-4 seconds when connected to their work internet connection (network). We’re guessing this is a result of certain network group policies and firewalls fighting or completing with OneDrive, but there are so many variables to consider that it’s often just a case of trial-and-error.

All things aside, we can confidently say that auto-syncing to cloud storage systems is a common cause of diminished performance of Excel 2016.

Suggestions Welcome

I can’t guarantee that any of these tips will materially work for you, but at Modano we have an ongoing obsession with optimizing the performance of Excel, particularly Excel 2016 as it’s proving quite challenging.

If you’ve found other ways of optimizing Excel 2016, please share below so we can all benefit from your experience.

Onwards, M.