Why Structured References are slow in Excel 2013 but fast in Excel 2016

Tables have a bad reputation for performance.

Zack (Firefytr) Barresse (who wrote the definitive guide to Excel Tables with Kevin (Zorvek) Jones) recommends a limit of around 10K rows for tables if you want to keep performance reasonable.

Prompted by a thread on the Excel-L forum I thought I should spend some time researching this.

Example Problem

Eric Lacroix kindly posted a test problem on Dropbox. I have simplified it further to make it clearer what is going on.
The workbook has a Table with 15000 rows and 2 calculated columns.

structref1Columns C and D contain COUNTIFS formulas referencing columns :

structref2XL 2013 Table Calculation Speed

A full calculation on this workbook with Excel 2013 takes 3.6 seconds on my 4.5GHZ I7 6700K.

XL 2013 Table Editing Speed

In Manual Calculation mode if you copy Column B2:B15000 (which do NOT contain any formulas) then doing a Past Special Values back onto column B takes 1.9 seconds!

There is no calculation time involved in this operation and none of the formulas in columns C and D are recalculated or re-evaluated. It is just the paste values operation that takes the time.

XL 2013 Range Calculation Speed and Editing Speed.

If you convert the table to a normal range, which converts the structured references to normal range references, then

  • Full Calculation still takes 3.6 seconds
  • But the Paste operation takes about 2 milliseconds! About 1000 times faster.

So the slowdown is :

  • Not caused by Calculation
  • Caused by Structured References

Bypassing the problem in Excel 2013

After doing some more research I discovered that the problem is caused by Excel 2013 being extremely slow to flag all the formulas containing structured references to the data in Column B for recalculation (make them dirty).
And its only slow if the formulas are not already dirty (but note that doing a recalculation automatically “cleans” all the formulas)

If you set the ForceFullCalculation property of the workbook to true then Excel does not bother to dirty formulas. The downside is that Excel then always does a full calculation of all the formulas in the workbook rather than a smart recalculation of only the dirtied formulas.

So it’s a trade-off: faster editing but slower calculation.

Excel 2016 fixes the Problem

I was surprised to find that when I tried to duplicate the problem with Excel 2016 I could not!

The Excel team have fixed the slowdown! (But don’t seem to have told anyone).

Advertisements
This entry was posted in Calculation and tagged , , , , , . Bookmark the permalink.

5 Responses to Why Structured References are slow in Excel 2013 but fast in Excel 2016

  1. sam says:

    @Charles,
    How would the calculation behave if we replace the [@Region] with A2 and retain the [Region] and [Score]

  2. fastexcel says:

    @sam,
    speed looks very similar: it still has to dirty the formulas containing structured refs.

  3. Joshua Kreuder says:

    Yep that appies to Excel 2010 too. I have a small VBA Sub to print every column in a workbook and log the Calc time of that column. On excel 2010 one of my slowest columns is always in a Table. Funny thing this column doesn’t contain any Formula. On Excel 2016 the overall calculation is about 20% Faster and Table columns dont seem to be a problem any more.

    • fastexcel says:

      Yes it feels very strange that doing a Range.Calculate on a table column that does not contain any formulas is so slow: thats because using Range.Calculate dirties any structured ref formulas that refer to the data column.

      • Joshua Kreuder says:

        It’s really kinda strange. This particular table also causes the workbook to calculate extremely slow on save. It’s about 10secs in Office 2010 for a FullRecalc. But at save it’t more than 20secs.
        Without that table the calculation is much faster.
        The Table is around 100rows and 200 Columns in size. And about 150 columns do have really simple +- Calculations in them.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s