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.

Columns C and D contain COUNTIFS formulas referencing columns :

XL 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).

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.

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.

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.