I’m excited to announce that faster Aggregation and RealTimeData functions are available in the latest1 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...
Microsoft 365 can help you be even more productive1. 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.
Program Manager, Excel
1 All available in Monthly channel in versions 2007 and later. Semi-annual channel preview in version 2008 and later.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.