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