FASTER CALCULATION TIMES
Excel’s senior program manager for Excel performance, Prash Shirolkar, said, “We are committed to making Excel 365 the new gold standard in Excel performance. Many of these features are significantly faster than those in Excel 2010.” Previous benchmarks have shown Excel 2010 was faster than Excel 2013 and Excel 2016. Shirolkar and his team are working to make modern Excel faster than Excel 2010.
Say you’re using SUMIFS to get department and region totals for 150 departments. Each of the 450 formulas are looking at data stored in cells A2:G90001. With the new update, as Excel is calculating the total for the first department, it’s efficiently building an index in memory of where each department is located. The first SUMIFS in cell J2 might take slightly longer as the indices are built, but the remaining 449 SUMIFS will calculate in a fraction of the time. With a large set of formulas, the calculation times should fall to about one-third the original calculation time.
Similar improvements are implemented for SUMIF, COUNTIF, AVERAGEIF, SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS. Note that you’ll only notice the improvement if you have multiple formulas pointing to the same database of values.
Some people use Excel to connect to the RealTimeData (RTD) function for getting real-time data from the stock market. In the past, support for RTD was only able to use a single core of your computer’s processor. The Excel team improved the underlying memory structures, removed bottlenecks, and made the process able to run on multiple cores. In an example in their demo, speed increased from 2,000 cells retrieved per minute to 23,000 cells retrieved per minute.
A NUMBER OF IMPROVEMENTS
The Excel team also found and fixed other bottlenecks in Excel:
Alt-Key Shortcuts: If you press and release the Alt key, there was a slight delay while Excel displayed the tooltips. Expert Excel users aren’t looking at the ribbon—they already know the keys to press. Excel now evaluates the shortcut key presses even before the tooltips appear.
Convert to Number: If you had a large range of text numbers and used the Convert to Number command, Excel would sometimes appear to hang as it recalculated all formulas after each number. This has been eliminated. Many people used Text to Columns (or Alt+D E F) to avoid the slowness of Convert to Number.
User-Defined Functions (UDFs): You might have created your own functions in VBA, or you might be using a VBA add-in that provides UDFs. These workbooks were slow to open. The linear scan that searched for UDFs upon the opening of a workbook has been improved.
Here’s another important way to speed your UDFs: If you have the VBA Editor open and run a macro, the title bar in VBA will update to show the word “Running.” Imagine that a UDF is operating on 1,000 cells. For each cell, Excel is changing the VBA title bar to display—and then not display—“Running.” Closing the VBA Editor when running UDFs ensures Excel doesn’t have the overhead of updating the VBA title bar.
Paste from Clipboard: If you’re pasting large sections of HTML or images from outside Excel, there were bottlenecks in getting that data into Excel. Microsoft made improvements to the underlying streaming data structure to make that happen faster.
Inserting Columns in Filtered Data: This was a problem particularly when the hidden rows contained thick borders. Calculating border thickness is a time-consuming process, and there’s no need to do it for the hidden rows in the data.
Deleting Ranges that Contain Merged Cells: Excel was iterating through the rows multiple times before performing the delete. This has been improved.
Check for Errors: In the past, using Check for Errors on 10,000 rows of data with some empty cells would take minutes or hours. Most people assumed that Excel had frozen and would kill the task. The command now builds an efficient data structure and should return the results in seconds instead of minutes.
Faster International Text Comparisons: The code to perform comparisons of text that contain international characters has been improved.
These improvements have rolled out to Microsoft 365 customers already. Customers with perpetual versions of Excel, such as Excel 2019 or Excel 2016, will have to wait for the next perpetual release in late 2021. To check that your version of Excel is updated, go to File, Account. Look for the box that says About Excel.
The words “Version 2010” mean you have the 10th release of 2010. The features described above are in version 2007 of the current channel and version 2008 of the semiannual channel. The last line of the About Excel box will tell you if you’re in the current or semiannual channel.
SF SAYS
Making common functions such as SUMIFS and VLOOKUP three times faster will improve performance for large spreadsheets.
November 2020
