Excel performance: Performance and limit improvements

Article
01/24/2024

Excel M365 introduces new features that you can use to improve performance when you are working with large or complex Excel workbooks

SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS Improvements

In Office 365 version 2005 monthly channel and later, Excel's SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS as well as their singular counterparts SUMIF, AVERAGEIF, and COUNTIF are much faster than Excel 2010 aggregating string data in the spreadsheet. These functions now create an internal cached index for the range being searched in each expression. This cached index is reused in any subsequent aggregations that are pulling from the same range.

The effect is dramatic: For example calculating 1200 SUMIFS, AVERAGEIFS, and COUNTIFS formulas aggregating data from 1 million cells on a 4 core 2 GHz CPU that took 20 seconds to calculate using Excel 2010, now takes 8 seconds only, on Excel M365 2006.

RealTimeData Function (RTD)

In Excel M365 version 2002 monthly channel or later, Excel's RealTimeData (RTD) function is much faster than Excel 2010 calculating data in the spreadsheet. We removed bottlenecks in its underlying memory and data structures as well as made it thread-safe to allow it to be calculated on all available threads of Multithreaded recalculation (MTR).

For example simulating 125,000 RTD updates for stock topics like "Last Price", "Ask", "Bid" to calculate values like "Trade Volume", "Market Value", "Trade Gain/Loss" etc. in 500,0000 cells in all, took 47 seconds using Excel 2010 and only 7 seconds using Excel M365 Version 2002, on the same hardware.

Another positive effect of making RTD function thread-safe, is that Multithreaded recalculation (MTR) doesn't need to be paused to run RTD function anymore. This improves performance noticeably when running RTD along with lots of other calculations.

For example, we ran a workbook with 10,000 RTD and 10,000 VLOOKUP functions, with each VLOOKUP depending on an RTD function result. Without thread-safe RTD full recalcuation took 10.20 seconds and with thread-safe RTD it took 5.84 seconds.

VLOOKUP, HLOOKUP, MATCH improvements

In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

These lookup functions now create an internal cached index for the column range being searched. This cached index is reused in any subsequent lookups that are pulling from the same row (VLOOKUP and MATCH) or column (HLOOKUP). The effect is dramatic: lookups on 5 different columns in the same table range can be up to 4 times faster than the same lookups using Excel 2010 or Excel 2016, and the improvement is larger as more columns are looked up.

For example calculating 100 rows of these 5 VLOOKUP formulas took 37 seconds to calculate using Excel 2010 and only 12 seconds using Excel 2016.

    =VLOOKUP($A900000,$A$2:$E$1000000,1,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,2,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,3,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,4,FALSE)
    =VLOOKUP($A900000,$A$2:$E$1000000,5,FALSE)

LAA memory improvement for 32-bit Excel

Although the 64-bit version of Excel has large virtual memory limits, the 32-bit version has only 2 GBs of virtual memory. Some customers use the 32-bit version because some third-party add-ins and controls are not available in the 64-bit version.

The 32-bit versions of Excel 2013 and Excel 2016 now have Large Address Aware (LAA) enabled. This will minimize out-of-memory error messages.

LAA doubles available virtual memory from 2 GB to 4 GB on 64-bit versions of Windows, and increases available virtual memory from 2 GB to 3 GB on 32-bit versions of Windows.

For more information, see Large Address Aware Capability Change for Excel.

To download a tool that shows how much virtual memory is available and how much is being used, see Excel Memory Checking Tool.

Full column references

In earlier versions of Excel, workbooks using large numbers of full column references and multiple worksheets (for example =COUNTIF(Sheet2!A:A,Sheet3!A1)) might use large amounts of memory and CPU when opened or when rows were deleted.

Excel 2016 Build 16.0.8212.1000 reduces the memory and CPU used in these circumstances.

In a sample test on a workbook with 6 million formulas, using full column references failed with an out-of-memory message at 4 GB of virtual memory with Excel 2013 LAA and with Excel 2010, but only used 2 GB of virtual memory with Excel 2016.

Structured references

In Excel 2013 and earlier versions, editing tables where formulas in the workbook use structured references to the table was slow. This led to the perception that tables should not be used with large numbers of rows. This issue no longer occurs in Excel 2016.

For example, an editing operation that took 1.9 seconds in Excel 2013 and Excel 2010 took about 2 milliseconds in Excel 2016.

Filtering, sorting, and copy/pasting

We've made a number of improvements to the response time when filtering, sorting, and copy/pasting in large workbooks.

In Excel 2013, after filtering, sorting, or copy/pasting many rows, Excel could be slow responding or would hang. Performance was dependent on the count of all rows between the top visible row and the bottom visible row. These operations are much faster after we improved the internal calculation of vertical user interface positions in Build 16.0.8431.2058.

Opening a workbook with many filtered or hidden rows, merged cells, or outlines could cause high CPU load. We introduced a fix in this area in Build 16.0.8229.1000.

After pasting a copied column of cells from a table with filtered rows where the filter resulted in a large number of separate blocks of rows, the response time was very slow. This has been improved in Build 16.0.8327.1000.

A sample test on copy/pasting 22,000 rows filtered from 44,000 rows showed a dramatic improvement:

For a table, the time went from 39 seconds in Excel 2013 and 18 seconds in Excel 2010 to 2 seconds in Excel 2016.
For a range, the time went from 30 seconds in Excel 2013 and 13 seconds in Excel 2010 to instantaneous in Excel 2016.

Copying conditional formats

In Excel 2013, copy/pasting cells containing conditional formats could be slow. This has been significantly improved in Excel 2016 Build 16.0.8229.0.

A sample test on copying 44,000 cells with a total of 386,000 conditional format rules showed a substantial improvement:

Excel 2010: 70 seconds
Excel 2013: 68 seconds
Excel 2016: 7 seconds

Adding and deleting worksheets

When adding and deleting large numbers of worksheets, a sample test on Excel 2016 Build 16.0.8431.2058 shows a 15%–20% improvement in speed compared to Excel 2013, but 5-10% slower than Excel 2010.

New functions

Excel 2016 Build 16.0.7920.1000 introduces several useful worksheet functions:

MAXIFS and MINIFS extend the COUNTIFS/SUMIFS family of functions. These functions have good performance characteristics. Use them to replace equivalent array formulas.
TEXTJOIN and CONCAT let you easily combine text strings from ranges of cells. Use them to replace equivalent VBA UDFs.

Other updates to Excel 2016 for Windows

For more details about the month-by-month improvements to Excel 2016, see What's new in Excel 2016 for Windows.

Excel 2010 performance improvements

Based on user feedback about Excel 2007, Excel 2010 introduces improvements to several features.

Feature	Improvement
Printer and page layout view	To improve performance of basic user interactions in page layout view, such as entering data, working with formulas or setting margins, Excel 2010 caches the printer settings and introduces optimized rendering calculations. Caching the printer settings reduces the number of network calls and reduces the dependency on a slow or unresponsive printer. In addition, connecting to the printer is cancelable so that the user does not have to wait for a slow or unresponsive printer.
Charts	Starting in Excel 2010, the rendering speed of charts has increased, especially with large data sets, and text-rendering performance has improved. In addition, Excel 2010 caches an image of a chart and uses the cached version when possible, to avoid unnecessary calculations and rendering.
VBA solutions	Improvements to the object model and the way it interacts with Excel increases the performance speed of many VBA solutions when run in Excel 2010 compared with Excel 2007.

Large data sets and the 64-bit version of Excel

The 64-bit version of Excel 2010 is not constrained to 2 GB of RAM like the 32-bit version applications nor up to 4 GB of RAM like the Large Address Aware 32-bit version applications. Therefore, the 64-bit version of Excel 2010 enables users to create much larger workbooks. The 64-bit version of Windows enables a larger addressable memory capacity, and Excel is designed to take advantage of that capacity. For example, users are able to fill more of the grid with data than was possible in previous versions of Excel. As more RAM is added to the computer, Excel uses that additional memory, allows larger and larger workbooks, and scales with the amount of RAM available.

In addition, because the 64-bit version of Excel enables larger data sets, both the 32-bit and 64-bit versions of Excel 2010 introduce improvements to common large data set tasks such as entering and filling down data, sorting, filtering, and copying and pasting data. Memory usage is also optimized to be more efficient in both the 32-bit and 64-bit versions of Excel.

For more information about the 64-bit version of Office 2010, see Compatibility Between the 32-bit and 64-bit Versions of Office 2010 and for choosing between 64-bit and 32-bit, see Choose between the 64-bit or 32-bit version of Office.

Shapes

Excel 2010 introduces significant improvements in the performance of graphics in Excel. At a high level, these improvements are in two areas: scalability and rendering.

The scalability improvements have a large impact in Excel scenarios because of the large number of graphics contained on worksheets. Often, this large number of shapes is created accidentally by copying and pasting data from a website, or by commonly run automation that creates shapes, but never removes them. This large number of graphics, combined with the way that graphics relate to the data grid in Excel, presents several unique performance challenges. Improvements in Excel 2010 increase the performance speed for worksheets that contain many shapes.

In addition, starting in Excel 2010, support for hardware acceleration improves rendering. Excel 2010 also introduces performance improvements to the Select method of the Shape object in the VBA object model.

Feature	Improvement
Basic use	The first set of improvements made in Excel 2010 surrounds basic use scenarios. These scenarios include operations and features such as sorting, filtering, inserting or resizing rows or columns, or merging cells. When these operations occur, it may be necessary to update the position of a graphic object on the grid. In the worst-case scenario, it is necessary to make an update to every single object on the worksheet. In Excel 2010, performance of these basic scenarios improves even when there are thousands of objects on the worksheet. These improvements were not achieved with a single feature or fix, but through a dedicated focus on performance that included improving the shape lookup mechanism, testing stress files, and investigating obstructions.
Text links	A text link on a shape is created when the user specifies a formula, for example "=A1", that defines the text for a given shape. These particular shapes were prone to cause performance issues on sheets with a large number of objects and/or when changes were made to cell content. Starting in Excel 2010, the way Excel tracks and updates these shapes has improved to optimize performance for changing cell content. This work improves scenarios such as typing a new value in a cell or performing complex object model operations.
Big Grid	Starting in Excel 2007, the size of the grid expanded from 65,000 rows to over one million rows. This increase caused some performance and rendering issues when working with graphics objects in the new regions of the larger grid. Starting in Excel 2010, Excel optimizes functionality that relies on using the top left of the grid as the origin to improve the experience of working with graphics in the new regions of the grid. Rendering fidelity and performance are improved relative to Excel 2007.
Rendering: Hardware acceleration	Starting in Excel 2010, improvements were made to the graphics platform by adding support for hardware acceleration when rendering 3-D objects. While the GPU can render these objects faster than the CPU, the experience in Excel 2010 depends on the content on your worksheet. If you have a sheet full of 3-D shapes, you'll see more benefit from the hardware acceleration improvements than on a worksheet with only 2-D shapes (which don't leverage the GPU).

Calculation improvements

Starting in Excel 2007, multithreaded calculation improved calculation performance.

Starting in Excel 2010, additional performance improvements were made to further increase calculation speed. Excel 2010 can call user-defined functions asynchronously. Calling functions asynchronously improves performance by allowing several calculations to run at the same time. When you run user-defined functions on a compute cluster, calling functions asynchronously enables several computers to be used to complete the calculations. For more information, see Asynchronous User-Defined Functions.

Multi-core processing

Excel 2010 made additional investments to take advantage of multi-core processors and increase performance for routine tasks. Starting in Excel 2010, the following features use multi-core processors: saving a file, opening a file, refreshing a PivotTable (for external data sources, except OLAP and SharePoint), sorting a cell table, sorting a PivotTable, and auto-sizing a column.

For operations that involve reading and loading or writing data, such as opening a file, saving a file, or refreshing data, splitting the operation into two processes increases performance speed. The first process gets the data, and the second process loads the data into the appropriate structure in memory or writes the data to a file. In this way, as soon as the first process begins reading a portion of data, the second process can immediately start loading or writing that data, while the first process continues to read the next portion of data. Previously, the first process had to finish reading all the data in a certain section before the second process could load that section of the data into memory or write the data to a file.

PowerPivot

PowerPivot refers to a collection of applications and services that provide an end-to-end approach for creating data-driven, user-managed business intelligence solutions in Excel workbooks. PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. Leveraging familiar Excel features, users can transform large quantities of data from almost any source with amazing speed into meaningful information to get the answers they need in seconds.

PowerPivot also integrates with SharePoint. In a SharePoint farm, PowerPivot for SharePoint is the set of server-side applications, services, and features that support team collaboration on business intelligence data. SharePoint provides the platform for collaborating and sharing business intelligence across the team and larger organization. Workbook authors and owners publish and manage the business intelligence that they develop to their SharePoint sites.

For more information about PowerPivot, see PowerPivot Overview.

HPC Services for Excel 2010

With a wealth of statistical analysis functions, support for constructing complex analyses, and broad extensibility, Excel 2010 is the tool of choice for analyzing business data. As models grow larger and workbooks become more complex, the value of the information generated increases. However, more complex workbooks also require more time to calculate. For complex analyses, it is common for users to spend hours, days, or even weeks completing such complex workbooks.

One solution is to use Windows HPC Server 2008 to scale out Excel calculations across multiple nodes in a Windows high-performance computing (HPC) cluster in parallel. There are three methods for running Excel 2010 calculations in a Windows HPC Server 2008 based cluster: running Excel workbooks in a cluster, running Excel user-defined functions (UDFs) in a cluster, and using Excel as a cluster service-oriented architecture (SOA) client.

For more information about HPC Services for Excel 2010, see Accelerating Excel 2010 with Windows HPC Server 2008 R2.

Conclusion

Excel 2016 introduces performance and limitation improvements focused on increasing Excel's ability to efficiently handle large and complex workbooks. These improvements allow Excel to scale along with hardware, improving performance as the CPU and RAM capacity of computers expand.