Excel 2016 for Windows is getting some welcome ‘under the hood’ performance improvements.
It’s great to see some work on the fundamentals of Office instead of the apparent focus on flashy new features that look great in demonstrations. Word and Outlook teams: please follow suit.
The Problems
The aim is to fix some of out of memory, high CPU or general Excel sluggishness caused, according to Microsoft by:
- Slow copying and pasting one column, in a sheet comprising lots of rows of data with many filtered.
- Slow copying and pasting many cells on a sheet with lots conditional formatting (CF) rules.
- Slow or hang when selecting cells after filtering or sorting rows from amongst lots of rows of data in a sheet.
- High CPU opening a workbook with 1 or more sheets with lots of rows filtered, containing merged cells and grouping.
- Slow when creating, deleting, editing sheet names in a workbook with lots of sheets programmatically or in the UI.
- High CPU and / or Out of memory error opening a workbook with lots of formulas (VLOOKUP, COUNTIF, etc.) with full column references (e.g. VLOOKUP over A:A) and multiple sheets.
- Slow with deleting rows in a workbook with lots of formulas with full column references (e.g. A:A).
A Lesson
Microsoft’s list does highlight one lesson that all Excel developers should keep in mind.
Avoid full column or row references like A:A or 9:9 it can slow down Excel a lot. Better to use named or more specific ranges.
The Fixes
Microsoft says they’ve changed Excel in the following ways:
- While copying and pasting, we are more intelligent in detecting the need to trigger time consuming object searches before searching for them.
- While copying and pasting with conditional formatting (CF), we have optimized our algorithm to consume time proportional to the number of CF cells involved and not any longer.
- While selecting cells in the visible sheet, after an operation, we re-render (or at least evaluate) all rows top to bottom (including filtered rows) in the visible sheet. This can be expensive depending on the number of rows and our ability to calculate for animation related rendering. Our optimization caches prior rendered calculations for animation support and reduces time during each such calculation.
- While opening a workbook and rendering lots of rows with merged cells, we are more intelligent in detecting the need to perform time-consuming merged cell rendering calculations before actually calculating.
- During sheet operations, our fix ensures expensive sheet tab dimension calculations are performed at just the right times. Time savings here augment VBA to perform bulk sheet operations faster too.
- While deleting rows that contain formulas with full column references e.g. A:A; we optimize in bulk for all rows. This saves memory for such formulas and in turn reduces overhead for subsequent delete, edit and update operations involving them.
This applies to Excel 2016 for Windows, Office 365 users only. Version 16.0.8431.2058 or later. At the moment the changes are being tested by ‘Insiders’ but will eventually be rolled out to all Office 365 customers.
Details at the MS TechCommunity.
