Excel 2016 Performance Improvements

Excel 2016 Performance Improvements

The Excel team has made a number of performance improvements to Excel 2016. These improvements were rolled out as part of the Office update cycle. The timescale that updates become available to you depends on which update option you are using:

  • Insider
  • Monthly Channel
  • Semi-annual Channel

For more details on the Office 2016 release cadence names see Slow – Fast Level Names.

This post discusses some of the features that have been introduced in Excel 2016 that you can use to improve performance with large or complex workbooks.

Large Address Aware (LAA) Memory improvement for 32-bit Excel

Although 64-bit Excel has extremely large virtual memory limits, 32-bit Excel has been limited to 2 Gigabytes (GB). And many Excel customers have found it difficult to migrate to 64-bit Excel because they use third-party addins and controls that are not available in 64-bit versions.
LAA has now been enabled for 32-bit versions of Excel 2013 and Excel 2016, and will minimize out of memory error messages.
LAA doubles available virtual memory from 2GB to 4GB when using 64-bit Windows, and increases available virtual memory from 2GB to 3GB under 32-bit Windows.

For more details see LAA 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

Previously, workbooks using large numbers of full column references and multiple worksheets, for example =COUNTIF(Sheet2!A:A,Sheet3!A1), might use large amounts of CPU and memory when opened, or rows were deleted.
An improvement in Excel 2016 build 16.0.8212.1000 substantially reduces the memory and CPU used in these circumstances.

My 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 virtual memory with Excel 2016.

Structured References

In some circumstances editing Excel Tables where formulas in the workbook use Structured References to the Table could be slow with Excel 2013 and previous versions. This led to the perception that Tables should not be used with large numbers of rows.
Excel 2016 has now fixed this problem.

My test showed an editing operation that took 1.9 seconds in Excel 2013 and Excel 2010 took about 2 milliseconds in Excel 2016.

For more details see Why Structured References are Slow in Excel 2013 but fast in Excel 2016.

Filtering, Sorting Copy/Pasting

The Excel 2016 team studied a number of large workbooks that show slow response when using Filtering, Sorting and Copy/Pasting, and a number of improvements have been made:

In Excel 2013 after Filtering or Sorting or Copy/Pasting many rows Excel could be slow responding or would hang. Performance was highly dependent on the count of all rows between the top visible row and the bottom visible row. An improvement made to the internal calculation of vertical user interface positions in build 16.0.8431.2058 has made these operations much faster.

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

In the past you could see very slow response 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.
This area has been substantially improved in build 16.0.8327.1000

My test on copy pasting 22000 rows filtered from 44000 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 virtually 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

My test on copying 44000 cells with a total of 386000 conditional format rules showed a substantial improvement:

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

Adding and deleting Worksheets

My test on Excel 2016 build 16.0.8431.2058 shows a 15-20% speed improvement compared to Excel 2013 when adding and deleting large numbers of worksheets. However Excel 2016 was 5-10% slower than Excel 2010 on this test.

New Functions

Excel 2016 build 16.0.7920.1000 introduced several very useful new worksheet functions:

MAXIFS and MINIFS extend the COUNTIFS/SUMIFS family of functions. These functions have good performance characteristics and should be used to replace equivalent array formulas.

TEXTJOIN and CONCAT let you easily combine text strings from ranges of cells. These functions can replace the slow VBA UDFs typically used in previous versions.

Other Updates to Excel 2016 for Windows

You can find more details of all the other month-by-month improvements that have been made to Excel 2016 at
What’s new in Excel 2016 for Windows.

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Excel 2016 Performance Improvements

  1. Patrick Matthews says:

    Great round-up Charles! Re: performance improvements with tables, is this on worksheet calcs only, or does it extend to VBA? I don’t have the details close at hand, but a while back (Excel 2013) I discovered that accessing table rows/columns via the ListObject object model was abominably slow, and thus fell back on Range objects. Have you encountered this in Excel 2016 at all?

  2. Patrick Matthews says:

    Side note: I was really, really happy about CONCAT() and TEXTJOIN() being added. The one thing I would have added to TEXTJOIN() would be a boolean argument on whether to concatenate the actual cell value or the value as it was displayed (this can make an impact for numeric or date entries), but one can’t have everything I guess. (The VBA UDF I keep on hand for “smart concatenating” has such an option, as well as an ignore blanks option similar to TEXTJOIN’s.)

  3. jeffrey Weir says:

    Correct me if I’m wrong, but it’s technically incorrect to say that these update apply to Excel 2016. Yes, they apply to Office 365 subscription users, who happen to have the latest version of Excel as part of that subscription. But if your version of Excel is one you paid for outright, then you get security updates only, and you won’t get new features that are added to Office 2016. See https://support.office.com/en-us/article/what-s-the-difference-between-office-365-and-office-2016-ed447ebf-6060-46f9-9e90-a239bd27eb96

    • jeffrey Weir says:

      …or does it depend on whether you have installed MSI vs C2R versions? The version I use in my workplace has no ‘build’ info whatsoever: It just says “Product Activated. Microsoft Excel 2016”. There’s no “Office Updates” section, so I presume that’s because the IT dept handle the updates centrally, meaning I won’t get these updates unless they do something their end. Damn, this stuff is confusing.

  4. Jeff Weir says:

    On the MSI vs C2R front, interestingly, my boss has a non-subscription click-to-run version of Excel 2016 Professional Plus on his pc, and he gets the update to PowerQuery (Column from example) from the May 2017 update showing up. But in my work version installed under Group Licensing, I don’t.

    I found something online that says Excel 2016 customers with volume licenses are locked to version 16.0.4266.1001 …which *might* explain why I don’t get the PQ update, but he does. https://officespdev.uservoice.com/forums/224641-feature-requests-and-feedback/suggestions/19237747-update-excel-2016-volume-license-customers-to-a-ve

    And I found another thread that seems to back this up, …with the salient bit being this comment:

    We installed Office 2016 via Volume Licensing two weeks ago for a client who has two Terminal Servers and about 60+ users total. Office 2016 is fully patched including the recent roll-outs from, and all users are experiencing this lag issue. The Office version, despite being fully patched, is still 16.0.4432.1000. Quite a few builds away from 16.0.6741.2017. I find it a little strange that we have various versions of Office within the same overall release version, and cannot patch the most crucial edition from a business perspective (Volume License) to the build that contains the highest level of fixes.

    Yikes! Looks like yet another fork:

    • Group Licence = *no* functionality updates
    • Non subscription licence = *some* functionality updates
    • Subscription licence = *all* functionality updates

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s