This post has been republished via RSS; it originally appeared at: Excel Blog articles.
Hey Excel Insiders,
Here are some specific examples of slow, high CPU or out of memory performance issues we have learnt about from our customers:
- 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).
We have addressed each example above 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.
Please try Excel O365 16.0.8431.2058 or later update, available to Insiders, and give us feedback on whether you’re seeing noticeable performance improvements in the scenarios listed above or not via:
- File (Tab) -> Feedback… use “ExcelPERF” as a keyword in your verbatim.
- office.com/excel/community
- excel.uservoice.com
Thanks,
Prash and Randy