Excel performance improvements now take seconds running Aggregation functions

Video provider requires cookies to play the video. Accept to continue or watch it directly on the provider's site.

Accept

I’m excited to announce that faster Aggregation and RealTimeData functions are available in the latest¹ Microsoft 365 Monthly and Semi-annual preview channels! This is our next wave of Excel M365 performance improvements after Speedy Lookups to reinforce our commitment to making Excel in Microsoft 365 the new gold standard of Excel Performance. This time around we have even more improvements, starting with...

Aggregation functions like SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS and their singular counterparts like SUMIF, COUNTIF, AVERAGEIF are among the most used Excel functions. If you use them to aggregate 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 evaluating each expression for a range of cells . We create an index on-demand when you first search a range of cells for an expression and then reuse it in subsequent aggregations pulling from the same range, until data changes in the lookup range. For details refer to our updated Excel Performance and limit improvements whitepaper.

RealTimeData (RTD) is commonly used by customers to get real-time data from, say, the stock market. If you use them directly in your workbooks to get real-time data directly 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, because we’ve sped-up RealTimeData (RTD) function  by many times.

We achieved this speed-up in calculating real-time data by removing bottlenecks in its underlying memory and data structures as well as making it thread-safe to allow it to be calculated on all available threads of Multithreaded recalculation (MTR). An after effect of making it thread-safe is that MTR doesn't need to be paused to run RTD function anymore, which improves performance noticeably when calculating it along with lots of other calculations.

But Wait! There's more... we have improved upon multiple use cases we heard being slow, or freezing, or not responsive involving file open, paste, insert, delete, VBA, sharing Excel in Teams, and others...

1. Faster opening workbooks with many user defined functions (UDFs).  You may have noticed opening workbooks with many UDFs including those from Add-ins might be slow. We had an inefficient linear scan search to lookup every UDF found in the workbook. We have a more efficient cache and search to make this lookup much faster now.
2. Faster large paste operations using clipboard. If you saw noticeable slowness pasting large content like HTML, Images, Objects etc. into Excel, you should now see this much faster. We have made the underlying content streaming data structure more efficient and responsive.
3. Speeding up deleting ranges that include merged cells. In the case of merge cells, we found that we were inefficiently iterating through the rows in the ranges multiple times before deleting. We are now more optimal in doing this.
4. Faster inserting a column in a sheet when rows are filtered. We found that this happened especially when the sheet has thick borders and evaluating borders thickness is an expensive operation. Also, we found we did this incorrectly for hidden or filtered rows as well. We do not do this now, thus speeding up the operation.
5. Faster recalculating user defined functions (UDFs) in VBA. When VBA UDFs are calculated, they update the VBA Editor’s state (title bar, output window, etc). This is very expensive and worse, this slow performance continued even after closing the Editor window.  We’ve now fixed this where closing the VBA Editor window removes the overhead.
6. Better sharing of Excel Application Window from Teams. You may have noticed that after sharing Excel Application Window from Teams and working in it, it may start to freeze. We found Excel to leak resources causing this to happen. We do not leak these resources and have eliminated this freeze.
7. Faster operations in worksheets with large number of formulas spanning a range of cells. When performing some operations (for example “Convert to Number”) we were needlessly iterating over structures for frequently used formulas within the workbook. We no longer perform this unnecessary work.
8. Faster “Check For Errors” on large sheets with many errors. We found that when a table contains 10s of thousands of rows as well as empty cells, we generated an inefficient data structure of every error and for each iteration for an error. In the worst case, Excel would run for very long periods of time or hang, consuming high CPU and memory. We now have an efficient data structure created and used once, returning results in seconds when it would take many minutes if not hours.
9. Faster invoking ALT+ shortcut key functionality in Excel. There used to be a slight delay when using ALT+ shortcuts.  This was caused by the wait to show the Tooltip windows.  This is unnecessary for expert users.  Now Excel no longer waits for the Tooltip windows allowing expert users to type shortcuts much faster.
10. Faster international string comparisons.  String comparisons are common within Excel.  International string comparisons have been sped up by optimizing the code that performs the comparisons.

Microsoft 365 can help you be even more productive¹. Give it a try today!

Refer to the following for more Excel Performance Tips. We’re going to continue our team’s work with more performance fixes in other areas, but we would really like to hear from you to ensure our fixes align closely with the freezing, or slow, or not responding issues you experienced... and so, please stay connected to Excel Tech Community, read Excel blog posts, Send us a Smile or Frown, and send us ideas and suggestions for the next version of Excel through UserVoice. You can also follow Excel on Facebook and Twitter.

Thanx,

Prash Shirolkar

Program Manager, Excel

¹ All available in Monthly channel in versions 2007 and later. Semi-annual channel preview in version 2008 and later.