This is our second wave of Excel Office 365 performance improvements after our first Sept ’17 one, to reinforce our commitment to fixing top impacting freezing, slow and not responding performance issues derived from Excel user feedback and usage learnings. This time around we have even more improvements than before starting with...
Lookup functions VLOOKUP, HLOOKUP, and MATCH are one of the most used functions in Excel. If you use them with exact match to find items in a table or range in Excel, and see it noticeably take time in seconds or minutes or more, chances are you'll now see very noticeable improvement in the speed at which you see results.
We made them faster by more efficiently finding the item you are looking up. We create an index on-demand when you first search of a range of cells and then reuse it in subsequent lookups from the same range, until data changes in the lookup range. For details refer to our updated Excel Performance and limit improvements whitepaper.
Also, we have improved the speed of many use cases involving operations like copy/paste, undo, conditional formatting, cell editing, cell selection, scrolling, filtering, file open, and programmability, such as:
- Copying the entire column in a sheet and selecting the clipboard is faster now because we optimized our large memory allocations with a more efficient data structure.
- Undoing pasted cells with conditional formatting was slow because a paste operation was inefficient in generating an undo record for every cell with conditional formatting and with its own changed priority. It now combines them more efficiently into one undo record with changed priorities for all in it.
- Undoing inserting a new row with a copied range with merged cells is now faster because we optimized handing of merged cells during an undo operation.
- Deleting one or more rows with merged cells is now more performant because we skip redundant calls to render the visible grid in the midst of the deletion operation.
- Viewing filter drop down for a column with lots of cells with conditional formatting is now faster because we optimized the filter drop down algorithm to be more efficient computing highlight, unique, and duplicate values.
- Scrolling in the visible sheet, after an operation wasn’t optimal, because we re-rendered (or at least evaluated) 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 introduced in our first wave of performance improvements
- Scrolling, editing of cells, cell selection, filtering operations in the grid are much faster when lots of rows are filtered / hidden because our rendering is more optimized to intelligently defer expensive rendering calls until we have calculated last row in the visible range.
- Slow transitioning to the next cell after editing a cell adjacent to a large table or range of cells with data, because after editing a cell adjacent to a large table or range of cells with data, we would generate a preview of all cells in that column to Flash Fill, which is on by default. Depending on the size of the adjacent table or range of cells and whether the cells contain additional metadata like conditional formatting, this can be a time consuming and expensive operation. We now, limit scope of Flash Fill preview to the visible area improving Excel responsiveness.
- When opening any workbook we searched all existing ribbon content to ensure every component of the ribbon renders correctly. We have now made open faster by more intelligently searching in common cases like when the ribbon item being searched for doesn’t exist. Also, during open we now skip updating MRU links synchronously and instead update them asynchronously after open. Finally, we scope the rendering of the grid to the visible grid area when opening workbooks with lots of wrapped text in locales like Japanese.
- Opening a local workbook when using a 3rd party anti-virus software is faster because we are more intelligent in avoiding searching for the 3rd party anti-virus vendor registered for scanning when a scan is determined to be redundant.
- We open simple CSV text files much faster by employing a more efficient memory allocation.
- Flashing scrollbars and slower running VBA code (than Excel 2010) even when ScreenUpdating property is set to false. We have addressed this by ensuring scrollbars will no longer update when ScreenUpdating property is set to false (just like in Excel 2010). This in turn diverts CPU cycles away from redundant rendering of the scrollbars and to running user VBA code, improving code execution speed.
- Programmatically selecting a cell, a range of cells, or a sheet using VBA is faster because we avoid redundant calls for updating a document’s upload status when running VBA macro code performing operations like selecting a cell, a range of cells, or a sheet.
- Executing Frequency function for large data sets is faster because we optimized the underlying sorting algorithm for Frequency function for large data sets.
Office 365 can help you be even more productive.1 Give it a try today!
Now we know we are not done with this second wave and there is more, that said we would really like to hear from you to ensure our fixes align closely with the freezing, or slow, or not responding issues you experience. Hence please stay connected to Excel and its community, read Excel blog posts, send us a frown, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.
Prash Shirolkar
Program Manager, Excel
1 Available in monthly channel version 1809 or later